Criteria API limit results in subquery

Question

I'm trying to write a query similar to

select * from Table a
 where a.parent_id in 
  (select b.id from Table b
   where b.state_cd = ?
   and rownum < 100)

using the Criteria API. I can achieve the query without the rownum limitation on the subquery fine using similar code to https://stackoverflow.com/a/4668015/597419 but I cannot seem to figure out how to appose a limit on the Subquery

Answer 1

In Hibernate, you can add the actual SQL restriction, but it is worth noting this will be Oracle-specific. If you switched over to PostgreSQL, this would break and you'd need LIMIT 100 instead.

DetachedCriteria criteria = DetachedCriteria.forClass(Domain.class)
   .add(Restrictions.sqlRestriction("rownum < 100"));

In the JPA API, the short answer is that you can't... In your question you proposed using the Criteria API (along with a SubQuery). However it is not until you actually call EntityManager.createQuery(criteriaQuery) that you'll get a TypedQuery where you can specify the maxResult value.

That said, you could break it into 2 queries, the first where you get the inner-select results (max 100) and then a 2nd Criteria where you take the resulting list in an in():

// inner query
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<YourClass> innerCriteriaQuery = cb.createQuery(YourClass.class);
Root<YourClass> yourClass = innerCriteriaQuery.from(YourClass.class);

innerCriteriaQuery.select(yourClass).where(
    cb.equal(yourClass.get(YourClass_.stateCode), someStateValue));

// list of 100 parent ids
List<YourClass> list = em.createQuery(innerCriteriaQuery).setMaxResults(100).getResultList();

// outer query
CriteriaQuery<YourClass> criteriaQuery = cb.createQuery(YourClass.class);
Root<YourClass> yourClass = criteriaQuery.from(YourClass.class);

criteriaQuery.select(yourClass).where(
    cb.in(yourClass.get(YourClass_.parentId)).value(list);

return em.createQuery(criteriaQuery).getResultList();