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();

Answer 2

There is no JPA Criteria solution for this. You could make use of a custom SQL function that runs during SQL query generation time. All JPA providers support something like that in one way or another.

If you don't want to implement that yourself or even want a proper API for constructing such queries, I can only recommend you the library I implemented called Blaze-Persistence.

Here is the documentation showcasing the limit/offset use case with subqueries: https://persistence.blazebit.com/documentation/core/manual/en_US/index.html#pagination

Your query could look like this with the query builder API:

criteriaBuilderFactory.create(entityManager, SomeEntity.class)
  .where("id").in()
    .select("subEntity.id")
    .from(SomeEntity.class, "subEntity")
    .where("subEntity.state").eq(someValue)
    .orderByAsc("subEntity.id")
    .setMaxResults(100)
  .end()

It essentially boils down to using the LIMIT SQL function that is registered by Blaze-Persistence. So when you bootstrap Blaze-Persistence with your EntityManagerFactory, you should even be able to use it like this

entityManager.createQuery(
    "select * from SomeEntity where id IN(LIMIT((" +
    "  select id " +
    "  from SomeEntity subEntity " +
    "  where subEntity.state = :someParam " +
    "  order by subEntity.id asc" +
    "),1)) "
)

or something like

criteriaQuery.where(
   cb.in(yourClass.get(YourClass_.parentId)).value(cb.function("LIMIT", subquery));

If you are using EclipseLink the calling convention of such functions looks like OPERATOR('LIMIT', ...).