休眠实体查询，用于在单个表中查找最新的半唯一行

java sql hibernate

2022-09-04 23:34:14

我有一个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 ...

以便返回的模型类型关系保持不变。

目前，我的查询也会返回我所有旧购买的内容：

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
     5           Pencil       09-09-2018         Steve           Supplies
     6           Pencil       09-06-2018         Steve           Supplies

对于重复购买，这会导致性能下降。

我应该使用任何特殊关键字来实现此目的吗？查询语言和SQL-fu不是我的强项。

编辑：

请注意，我目前正在使用API，并希望继续这样做。Criteria

Criteria criteria = session.createCriteria(Purchase.class);
criteria.addOrder(Order.desc("purchaseDate"));
// Product names
Criterion purchaseNameCriterion = Restrictions.or(productNames.stream().map(name -> Restrictions.eq("productName", name)).toArray(Criterion[]::new));
// Purchaser
Criterion purchaserCriterion = Restrictions.or(purchaserNames.stream().map(name -> Restrictions.eq("purchaser", name)).toArray(Criterion[]::new));
// Bundle the two together
criteria.add(Restrictions.and(purchaseNameCriterion, purchaserCriterion));

criteria.list(); // Gives the above results

如果我尝试使用不同的投影，则会出现错误：

ProjectionList projections = Projections.projectionList();
projections.add(Projections.property("productName"));
projections.add(Projections.property("purchaser"));
criteria.setProjection(Projections.distinct(projections));

结果在：

17:08:39 ERROR Order by expression "THIS_.PURCHASE_DATE" must be in the result list in this case; SQL statement:

因为，如上所述，添加投影/非重复列集似乎向Hibernate表明我希望这些列作为结果/返回值，而我想要的只是基于唯一列值来限制返回的模型对象。

答案 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。

请参阅休眠条件查询，了解具有 IN 子句和子选择的多列

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

答案 2

您可以使用 @ 创建临时变量和表。我不确定如何创建数组。

declare @product1 = 'Pencil'
declare @product2 = 'Notebook'
declare @purchaser_name1 = 'Bob'
declare @purchaser_name2= 'Steve'

这应该获得每个 cust/prod 组合的最新购买日期

select 
product_name, purchaser_name, max(purchase_date) as max_purchase_date
into @temp
from purchases with(nolock) where 
product_name in (@product1,@product2) and
purchaser_name in (@purchaser_name1,@purchaser_name2)
group by product_name, purchaser_name

如果您需要返回圆圈并获取ID，则可以重新加入购买以获取它们

select p.* from purchases p with(nolock) 
inner join @temp t 
on p.product_name = t.product_name
and p.purchaser_name = t.purchaser_name
and p.purchase_date = t.max_purchase_date

请注意表名称后面的“with（nolock）”。这可能有助于性能。