Write HQL clause using Hibernate Criteria API

Question

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

Answer 1

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.