使用Hibernate Criteria API编写HQL子句 [英] Write HQL clause using Hibernate Criteria API

问题描述

我想编写一个方法，返回按字段'serviceId'分组的最后添加对象列表。

以下HQL有效，但我想写这个使用Criteria API：

  FROM通知WHERE日期IN 
（SELECT MAX（date）FROM通知GROUP BY serviceId）
 ORDER BY日期ASC 
  

类似于此：

  Criteria crit = session.createCriteria（Notification.class）; 
 crit.add（Restrictions.in（date，< MAX dates>））; 
 criteria.addOrder（Order.desc（date））; 
  

预先致谢。

编辑：

现在我需要一个类似的查询，使用eclipselink API = /

基本上，我需要最后N行（最大日期），其状态是下面描述的五个状态之一，按serviceId列分组。

由于我经验不足，所以我能做到最好：

  ExpressionBuilder builder = new ExpressionBuilder（）; 
表达式exStatus1 = builder.get（status）。equal（MessageType.START.toString（））; 
表达式exStatus2 = builder.get（status）。equal（MessageType.RUNNING.toString（））; 
表达式exStatus3 = builder.get（status）。equal（MessageType.PAUSED.toString（））; 
表达式exStatus4 = builder.get（status）。equal（MessageType.END_ERROR.toString（））; 
表达式exStatus5 = builder.get（status）。equal（MessageType.END_SUCCESS.toString（））; 
 
 ReadAllQuery query = new ReadAllQuery（）; 
 query.setReferenceClass（Notification.class）; $（）（）（exStatus1）.or 
 query.setMaxRows（listSize）; 
 query.addDescendingOrdering（date）; 
  

避免重复结果行中的serviceIds的子句缺失...

解决方案

您将要使用Criteria预测API和分离的子查询：

  Criteria crit = session.createCriteria（Notification.class，main）; 
 
 DetachedCriteria notificationSubQuery = DetachedCriteria.forClass（Notification.class，sub）; 
 notificationSubQuery.setProjection（Projections.max（date））; 
 notificationSubQuery.add（Restrictions.eqProperty（sub.serviceId，main.serviceId））; 
 
 crit.add（Subqueries.propertyIn（date，notificationSubQuery））; 
 crit.addOrder（Order.desc（date））; 
  

这反映了您在HQL查询中使用的技术。

编辑：

我更新了查询以匹配您的主通知类和子查询之间的serviceId，作为这个HQL查询：

  FROM通知main WHERE date IN 
（SELECT MAX（sub.date）FROM Notification sub WHERE sub.serviceId = main.serviceId）
 ORDER BY日期ASC 
  

这可以防止案例你可以在两个不同的serviceIds之间匹配非最大日期，如下所示：

  serviceId = 1：date = 3,4 ，5 
 serviceId = 2：date = 4,5,6 
  

旧查询返回：

  serviceId：1，日期：5 
 serviceId：2，日期：5,6 
  code> 

新的查询返回：



  serviceId：1，日期：5 
 serviceId：2，日期：6 
  

L等我知道这是否适合你。

I want to write a method that returns a list of last added objects grouped by field 'serviceId'.

The following HQL works, but I want to write this using Criteria API:

FROM Notification WHERE date IN 
    (SELECT MAX(date) FROM Notification GROUP BY serviceId) 
ORDER BY date ASC

Something like this:

Criteria crit = session.createCriteria(Notification.class);
crit.add(Restrictions.in("date", <MAX dates>));
criteria.addOrder(Order.desc("date"));

Thanks in advance.

EDIT:

Now I need a similar query that works using eclipselink API =/
Basically, I need the last N rows (max date), which status is one of the five described bellow, grouped by serviceId column.
Due to my inexperience, it was the best I could:

ExpressionBuilder builder = new ExpressionBuilder();
Expression exStatus1 = builder.get("status").equal(MessageType.START.toString());
Expression exStatus2 = builder.get("status").equal(MessageType.RUNNING.toString());
Expression exStatus3 = builder.get("status").equal(MessageType.PAUSED.toString());
Expression exStatus4 = builder.get("status").equal(MessageType.END_ERROR.toString());
Expression exStatus5 = builder.get("status").equal(MessageType.END_SUCCESS.toString());

ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Notification.class);
query.setSelectionCriteria(((exStatus1).or(exStatus2).or(exStatus3).or(exStatus4).or(exStatus5)));
query.setMaxRows(listSize);
query.addDescendingOrdering("date");

The clause to avoid duplicates serviceIds in result rows is missing...

解决方案

You're going to want to use the Criteria projections API with a detached subquery:

Criteria crit = session.createCriteria(Notification.class, "main");

DetachedCriteria notificationSubQuery = DetachedCriteria.forClass(Notification.class, "sub");
notificationSubQuery.setProjection(Projections.max("date"));
notificationSubQuery.add(Restrictions.eqProperty("sub.serviceId", "main.serviceId"));

crit.add(Subqueries.propertyIn("date", notificationSubQuery));
crit.addOrder(Order.desc("date"));

This mirrors the technique you are using in your HQL query.

EDIT:

I updated the query to match serviceId between your main notification class and your sub query, essentially the same as this HQL Query:

FROM Notification main WHERE date IN 
    (SELECT MAX(sub.date) FROM Notification sub WHERE sub.serviceId = main.serviceId) 
ORDER BY date ASC

This prevents the case where you would have a non-maximum date matching between two different serviceIds like so:

serviceId = 1: date = 3,4,5
serviceId = 2: date = 4,5,6

Old query return:

serviceId: 1, date: 5
serviceId: 2, date: 5,6

New query return:

serviceId: 1, date: 5 
serviceId: 2, date: 6

Let me know if this works for you.

这篇关于使用Hibernate Criteria API编写HQL子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！