用于在单个表中查找最近的半唯一行的 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&nbsp;where (p.PRODUCT_NAME, p1.PURCHASER_NAME, p1.PURCHASE_DATE) in&nbsp; &nbsp; (select PRODUCT_NAME, PURCHASER_NAME , max(PURCHASE_DATE)&nbsp;&nbsp; &nbsp; &nbsp;from Purchases&nbsp;&nbsp; &nbsp; &nbsp;where&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; PRODUCT_NAME in :productNames and&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; PURCHASER_NAME in :purchaserNames&nbsp;&nbsp; &nbsp; &nbsp;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 thisselect *from purchases p1wherep1.product_name in ('Notebook', 'Pencil') andp1.purchaser_name in ('Bob', 'Steve') andnot exists (&nbsp; &nbsp;select p2.purchase_id&nbsp; &nbsp;from purchases p2&nbsp; &nbsp;where&nbsp; &nbsp;p2.product_name = p1.product_name and&nbsp; &nbsp;p2.purchaser_name = p1.purchaser_name and&nbsp; &nbsp;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 clauseDetachedCriteria 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 (...) filterDetachedCriteria 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读起来更清楚一点。