用于在单个表中查找最近的半唯一行的 Hibernate 实体查询

Question

我有一个带有单个表的 Hibernate 数据库，如下所示：PURCHASE_ID | PRODUCT_NAME | PURCHASE_DATE | PURCHASER_NAME | PRODUCT_CATEGORY------------------------------------------------------------------------------     1          Notebook      09-07-2018          Bob            Supplies     2          Notebook      09-06-2018          Bob            Supplies     3           Pencil       09-06-2018          Bob            Supplies     4            Tape        09-10-2018          Bob            Supplies     5           Pencil       09-09-2018         Steve           Supplies     6           Pencil       09-06-2018         Steve           Supplies     7           Pencil       09-08-2018         Allen           Supplies我想根据其他一些限制只退回最新购买的商品。例如：List<Purchase> getNewestPurchasesFor(Array<String> productNames, Array<String> purchaserNames) { ... }可以使用以下方法调用：List<Purchase> purchases = getNewestPurchasesFor(["Notebook", "Pencil"], ["Bob", "Steve"]);用英语，“给我最新购买的笔记本或铅笔，鲍勃或史蒂夫。”并将提供：PURCHASE_ID | PRODUCT_NAME | PURCHASE_DATE | PURCHASER_NAME-----------------------------------------------------------     1          Notebook      09-07-2018          Bob                 3           Pencil       09-06-2018          Bob                 5           Pencil       09-09-2018         Steve           所以，它就像基于一些一个“独特”的多列查找，或“限价”后排序合并列唯一键，但所有的例子，我发现显示使用SELECT DISTINCT(PRODUCT_NAME, PURCHASER_NAME)以获得只有那些列，而我需要使用格式：from Purchases as entity where ...以便模型类型以完整的关系返回。对于重复购买，这会导致性能下降。我应该使用任何特殊关键字来完成此操作吗？查询语言和 SQL-fu 不是我的强项。

Answer 1

首先，使用聚合查询获取产品+购买者组合的最后购买日期。

使用该查询作为匹配元组的子选择：

from Puchases p 

where (p.PRODUCT_NAME, p1.PURCHASER_NAME, p1.PURCHASE_DATE) in

    (select PRODUCT_NAME, PURCHASER_NAME , max(PURCHASE_DATE) 

     from Purchases 

     where 

        PRODUCT_NAME in :productNames and 

        PURCHASER_NAME in :purchaserNames 

     group by PRODUCT_NAME, PURCHASER_NAME)

也应该可以使用标准 API 来实现相同的功能，使用 Subqueries.propertiesIn。

如果您的 PURCHASE_ID 保证“按时间顺序递增”，那么您只需在子选择中使用 max(PURCHASE_ID) 即可。

Answer 2

在我看来，诀窍是看到“给我最新的”相当于“给没有新购买的行”。这转化为这种查询：

-- This is SQL

-- Note that if two purchases have exactly the same date, this query will

-- return both; you can fine tune the condition inside the exists clause

-- to avoid this

select *

from purchases p1

where

p1.product_name in ('Notebook', 'Pencil') and

p1.purchaser_name in ('Bob', 'Steve') and

not exists (

   select p2.purchase_id

   from purchases p2

   where

   p2.product_name = p1.product_name and

   p2.purchaser_name = p1.purchaser_name and

   p2.purchase_date > p1.purchase_date

)

order by purchase_id;

尽管这是 SQL，但转换为 HQL 应该非常简单，这对您来说可能就足够了。自从我使用 Hibernate Criteria 已经很长时间了（这些天你倾向于使用 JPA API），但它应该是类似的东西：

DetachedCriteria criteria = DetachedCriteria.forClass(Purchase.class, "p1");

// add here your filters to criteria

// criteria.add(purcharserName in (....));

// criteria.add(productName in (....));

// this appends the not exists clause

DetachedCriteria notExistsCriteria = DetachedCriteria.forClass(Purchase.class, "p2");

notExistsCriteria.add(Restrictions.eqProperty("p2.productName", "p1.productName"));

notExistsCriteria.add(Restrictions.eqProperty("p2.purchaserName", "p1.purchaserName"));

notExistsCriteria.add(Restrictions.gtProperty("p2.purchaseDate", "p1.purchaseDate"));

criteria.add(Subqueries.notExists(notExistsCriteria.setProjection(Projections.property("p1.id"))));

List<Purchase> results = // issue Criteria query

更新：

我看到 Hibernate Criteria 支持SQLALL运算符，所以如果你的数据库支持它，你也可以这样写：

DetachedCriteria criteria = DetachedCriteria.forClass(Purchase.class, "p1");

// add here your filters to criteria

// criteria.add(purcharserName in (....));

// criteria.add(productName in (....));

// this appends the p1.purchaseDate > all (...) filter

DetachedCriteria allCriteria = DetachedCriteria.forClass(Purchase.class, "p2");

allCriteria.add(Restrictions.eqProperty("p2.productName", "p1.productName"));

allCriteria.add(Restrictions.eqProperty("p2.purchaserName", "p1.purchaserName"));

criteria.add(Subqueries.propertyGeAll("p1.purchaseDate", allCriteria.setProjection(Projections.property("p2.purchaseDate"))));

List<Purchase> results = // issue Criteria query

读起来更清楚一点。