How to Write Simple Hibernate Criteria Subquery

Question

How can I write this SQL query as a Hibernate JPA Criteria (with Restrictions, etc) in Java ?

SELECT q.* FROM queue AS q WHERE q.executed = false AND q.queued_on = (SELECT min(queued_on) FROM queue WHERE item_id = q.item_id);

I only managed to write the first part like this:

getBaseCriteria()
    .add(Restrictions.eq("executed", false))
    // Missing Second Where Filter Here
    .addOrder(Order.asc("queuedOn"))
    .list();

Answer 1

Try to create a separate criteria instance for the subquery and simply add as another restriction as follows:

DetachedCriteria subCriteria = DetachedCriteria.forClass(Queue.class, "sub")
    .add(Restrictions.eqProperty("sub.itemId","main.itemId"))
    .setProjection(Projections.projectionList().add(Projections.min("sub.queuedOn")));

session.createCriteria(Queue.class, "main")
   .add(Subqueries.propertyEq("main.queuedOn", subCriteria ));
   .add(Restrictions.eq("main.executed", false));
   .addOrder(Order.asc("main.queuedOn"))
   .list();