在单个表中查找最近的、半唯一行的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"]);

用英语表达，“给我Bob或Steve买的最新款Notebook或Pencil的购买记录。”

将提供：

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技巧并不是我的强项。

编辑：

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

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我希望将这些列作为结果/返回值，而我想要的是仅基于唯一列值限制返回的模型对象。

- Craig Otis

---

如果“Bob”购买了一支新铅笔，查询结果中的记录3应该被新记录“替换”，对吗？ - Leviand

@Leviand 是的，没错。如果在9月10日发生了Bob/Pencil的第8行，那么它将被返回，而不是ID为3的条目。（准确地回答查询：“给我Bob或Steve购买的最新笔记本电脑或铅笔。”） - Craig Otis

很不幸，我没有 - 而且我希望将查询保持在 Hibernate 层面。如果可能的话，使用 Criteria API，否则使用 Hibernate 查询。理想的结果实际上不是数据库级别的 ResultSet，而是具有其关系完整性的 Hibernate 实例化模型对象。（上面的示例未显示关系。） - Craig Otis

好的，我正在处理。 - Leviand

我在你的问题中发现了一个错误：结果显示为 4 Pencil 09-09-2018 Steve，但实际数据是 4 Tape 09-10-2018 Bob Supplies，你能用正确的示例进行修正吗？谢谢。 - Leviand

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)

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

请参见 Hibernate Criteria Query for multiple columns with IN clause and a subselect。

如果您的 PURCHASE_ID 保证是“按时间顺序升序”的，则可以在子查询中简单地使用 max(PURCHASE_ID)。

- ckedar

Answer 2

好的，首先我建立了一个查询，仅提取所请求的记录：

select p1.* from Purchase p1

  join (
    select 
        max(PURCHASE_DATE) as maxdate, 
        purchaser_name, 
        PRODUCT_NAME from Purchase 
    where 
        (product_name ='Notebook' or product_name = 'Pencil') 
        and purchaser_name in ('Bob','Steve')
    group by 
        purchaser_name, 
        PRODUCT_NAME) p2

  on p1.PURCHASE_DATE = p2.maxDate
  and p1.PRODUCT_NAME = p2.PRODUCT_NAME
  and p1.PURCHASER_NAME = p2.PURCHASER_NAME;

这个输出了

PURCHASE_ID PRODUCT_NAME    PURCHASE_DATE             PURCHASER_NAME    PRODUCT_CATEGORY
1           Notebook        2018-07-09 00:00:00.000   Bob               Supplies
3           Pencil          2018-06-09 00:00:00.000   Bob               Supplies
5           Pencil          2018-09-09 00:00:00.000   Steve             Supplies

现在我们可以将该查询转换为SQLQuery，并使用.setResultTransformer(Transformers.aliasToBean(Purchase.class))将其转换为bean。 请注意，我已经将会话命名为yourSession，请相应更改：

List<Purchase> list = yourSession.createSQLQuery(
          "select p1.* from Purchase p1 "
        + " join ( "
        + "     select "
        + "         max(PURCHASE_DATE) as maxdate, "
        + "         purchaser_name, "
        + "         PRODUCT_NAME from Purchase "
        + "     where "
        + "         (product_name ='Notebook' or product_name = 'Pencil') " //this must be created dinamically based on your parameters
        + "         and purchaser_name in ('Bob','Steve') " //and this too
        + "     group by "
        + "         purchaser_name, "
        + "         PRODUCT_NAME) p2 "

        + " on p1.PURCHASE_DATE = p2.maxDate "
        + " and p1.PRODUCT_NAME = p2.PRODUCT_NAME "
        + " and p1.PURCHASER_NAME = p2.PURCHASER_NAME ")
        .setResultTransformer(Transformers.aliasToBean(Purchase.class))
        .list();

现在缺少的是将你的参数像 Notebook 或 Bob 一样传递到你将包装这段代码的方法中。我会构建一个帮助方法，根据参数列表的大小编写条件。

由于我没有连接到数据库的 hibernate，所以我手写了代码，可能需要进行一些修复，但总体思路应该能起到作用。

在没有 SQLQuery 的情况下做到这一点，我认为要困难得多且更难阅读：你需要保持结果不变，并且这就是你将通过此实现的内容。

- Leviand

---

这是针对特定数据库的，对吗？也就是说，这不是HQL语句？我需要为我们支持的每个DB实现都创建一个单独的查询字符串。 - Craig Otis

是的，这是针对数据库的。 - Leviand

好的，我感谢你的回答 - 但我想找到一个使用Hibernate查询语言或Criteria API的解决方案。 - Craig Otis

好的，明天我会尝试使用Hibernate Criteria。 - Leviand

@CraigOtis，你在使用哪些数据库？ - Leviand

Answer 3

更新

要使用Hibernate Criteria，您可以尝试子查询方法：

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

ProjectionList groupBy = Projections.projectionList();
groupBy.add(Projections.max("purchaseDate"));
groupBy.add(Projections.groupProperty("productName"));
groupBy.add(Projections.groupProperty("purchaserName"));
subQuery.setProjection(groupBy);

subQuery.add(Restrictions.in("productName", productNames));
subQuery.add(Restrictions.in("purchaserName", purchaserName));

Criteria purchase = session.createCriteria(Purchase.class, "p1");
purchase.add(Subqueries.propertiesIn(new String[] {"purchaseDate", "productName", "purchaserName"}, subQuery));
purchase.addOrder(Order.desc("purchaseDate"));

List<Purchase> p1 = purchase.list();

另一种方法是使用本地SQL：

SELECT p1.*
FROM purchase p1 LEFT JOIN purchase p2
  ON (p1.purchaser_name = p2.purchaser_name 
      AND p1.product_name = p2.product_name 
      AND p1.purchase_date < p2.purchase_date)
WHERE p2.id IS NULL 
      AND p1.product_name IN ("Notebook", "Pencil") 
      AND p1.purchaser_name IN ("Bob", "Steve")
ORDER BY p1.product_name DESC

与子查询方法相比，此SQL语句具有显着的性能优势。

然而，似乎无法将其转换为Hibernate Criteria（因为Criteria需要实体之间的路径/映射）。

- Mạnh Quyết Nguyễn

---

不幸的是，正如我所提到的，我需要使用Criteria/Hibernate查询来检索模型关系。 - Craig Otis

EntityManager.createNativeQuery 不符合您的要求吗？ - Mạnh Quyết Nguyễn

另一个缺点是它似乎是特定于数据库的。我们支持多个数据库实现（H2、MySQL等），并且将事物保持在Criteria/Hibernate级别可以避免实现特定于数据库的查询字符串。 - Craig Otis

你的查询中多个 Restrictions.or 可以被单个 Restrictions.in 替换，以提高可读性。 - Mạnh Quyết Nguyễn

Answer 4

尝试使用这段代码。

    SessionFactory sessFact = HibernateUtil.getSessionFactory();
    Session session = sessFact.openSession();
    Criteria criteria = session.createCriteria(Purchase.class);
    ProjectionList projList = Projections.projectionList();

    projList.add(Projections.groupProperty("purchaserName"));
    projList.add(Projections.groupProperty("productName"));
    projList.add(Projections.property("purchaseId"));
    projList.add(Projections.property("productName"));
    projList.add(Projections.max("purchaseDate"));
    projList.add(Projections.property("purchaserName"));

    criteria.setProjection(projList);

    List<String> productList = new ArrayList<String>() {
        {
            add("Notebook");
            add("Pencil");
        }
    };
    List<String> purchaserList = new ArrayList<String>() {
        {
            add("Bob");
            add("Steve");
        }
    };

    Disjunction prod = Restrictions.disjunction();
    prod.add(Restrictions.in("productName", productList));

    Disjunction purch = Restrictions.disjunction();
    purch.add(Restrictions.in("purchaserName", purchaserList));

    criteria.add(Restrictions.and(prod, purch));
    List resultList = criteria.list();

将 SQL 作为结果返回 (<property name="show_sql">true</property>)

select this_.PURCHASER_NAME as y0_, this_.PRODUCT_NAME as y1_, this_.PURCHASE_ID as y2_, this_.PRODUCT_NAME as y3_, max(this_.PURCHASE_DATE) as y4_, this_.PURCHASER_NAME as y5_ from purchase this_ where ((this_.PRODUCT_NAME in (?, ?)) and (this_.PURCHASER_NAME in (?, ?))) group by this_.PURCHASER_NAME, this_.PRODUCT_NAME

- Seymur Asadov

Answer 5

在我看来，诀窍在于看到“给我最新的”等同于“给出没有更新购买的行”。这可以转化为以下类型的查询：

-- 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

更易理解的阅读。

- gpeche

Answer 6

尝试以下HQL，假设您有一个自增的id字段。

FROM Purchase p WHERE p.id IN(SELECT MAX(p1.id) FROM Purchase p1 WHERE p1.productName IN('Notebook','Pencil') AND p1.purchaseName IN('Bob', 'Steve') GROUP BY p1.productName, p1.purchaseName)

- AJA

Answer 7

首先，使用分离的条件获取productName、purchaserName和max(purchaseDate)按照productName、purchaserName进行分组。这将使我们能够使用这三个属性来识别唯一的行。但是，如果同一购买者在同一天购买了同一产品，则无法使用上述条件识别唯一的行，结果将从数据库中获取多条记录。为解决此问题，您需要在DB中为purchaseDate字段使用datetime或timestamp类型。现在，使用分离条件中的这些属性在Criteria查询中获取所需的结果。

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Purchase.class, "inner");
    detachedCriteria.add(Restrictions.in("inner.productName", new String[] { "Notebook", "Pencil" }));
    detachedCriteria.add(Restrictions.in("inner.purchaserName", new String[] { "Bob", "Steve" }));
    detachedCriteria.setProjection(Projections.projectionList().add(Projections.max("inner.purchaseDate"))
            .add(Projections.groupProperty("inner.productName"))
            .add(Projections.groupProperty("inner.purchaserName")));
    Session session = this.getEntityManager().unwrap(Session.class);
    Criteria criteria = session.createCriteria(Purchase.class, "b");
    ProjectionList projectionList = Projections.projectionList();
    projectionList.add(Projections.property("b.purchaseId"));
    projectionList.add(Projections.property("b.productName"));
    projectionList.add(Projections.property("b.purchaseDate"));
    projectionList.add(Projections.property("b.purchaserName"));
    criteria.setProjection(projectionList);
    criteria.add(Subqueries.propertiesIn(new String[] { "b.purchaseDate", "b.productName", "b.purchaserName" },
            detachedCriteria));
    criteria.list();

这个条件查询将在mysql中执行以下查询

select this_.purchase_id as y0_, this_.product_name as y1_, this_.purchase_date as y2_, this_.purchaser_name as y3_ from purchase this_ where (this_.purchase_date, this_.product_name, this_.purchaser_name) in (select max(inner_.purchase_date) as y0_, inner_.product_name as y1_, inner_.purchaser_name as y2_ from purchase inner_ where inner_.product_name in (?, ?) and inner_.purchaser_name in (?, ?) group by inner_.product_name, inner_.purchaser_name)

- Pavan

Answer 8

List<Purchase> findByProductNameInAndPurchaserNameInAndPurchaseDateBefore(List<String> productNames, List<String> purchaserNames, Date before);

我看不到购买实体，但是如果产品被映射为实体，那么这个查询必须相应地更新。

注意：我建议您阅读有关全文搜索或Hibernate搜索的内容，因为如果您将有很多类似于此的查询，那么您的项目似乎需要全文搜索支持。

- mommcilo

Answer 9

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

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)”语句，这可能有助于提高性能。

- Kevin D

---

谢谢@Kevin，我想避免降到变量声明和较低级别的脚本编写，因为我正在使用Hibernate，并希望返回具有关系完整性的模型对象。 - Craig Otis

Answer 10

你好，我可以提供一个非常简单的基于HQL的解决方案，没有太多魔法。 解决方案是以下HQL查询：

select p.id, max(p.date) from Purchase p where p.productName in('notebook','pencil') and p.purchaseName in ('ob', 'Steve') group by p.productName ,p.purchaseName

一旦您获得了记录的ID，您可以通过ID选择实际产品。

现在您可能会认为此查询将返回整个表。但它不会。大多数支持服务器端游标的现代数据库只会返回您指定的记录数量。

一旦初始化查询，下一步是告诉它要返回多少结果：

    Query query = query.setMaxResults(1)
    query.setFetchSize();
    query.scroll(ScrollMode.FORWARD_ONLY);
    // here is a hint for MySQL
    query.setMaxResults(100)

如果正确使用，此查询不会返回完整的表格！它将返回所要求的内容。

- Alexander Petrov

---

谢谢，但我不想将结果数量限制为一个常量。我想检索与我的问题描述相匹配的所有值。 - Craig Otis

我认为你在这里理解有误。这个查询并没有限制你的结果。这个查询是告诉后端除了query.setMaxResults(100)之外，它想要保持一个开放的游标，并告诉它每次要获取多少条记录。 - Alexander Petrov

如果您删除我仅为MySQL添加的最后一行query.setMaxResults（100），那么会发生以下情况：如果数据库中有10,000条记录，并且您开始迭代查询返回的集合。对于每个FetchSize数量的结果，它将继续滚动。 - Alexander Petrov

所以，如果你不想跳转到第10页，就不需要。明白吗？ - Alexander Petrov

选择 p.id，max(p.date) from Purchase p where p.productName in('笔记本电脑'，'铅笔') and p.purchaseName in ('ob'，'Steve') group by p.productName，p.purchaseName - Alexander Petrov