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

If you can't figure out how to limit the subquery, how about using a join? — ASA, May 12 '16 at 12:51
@Traubenfuchs There should be more than 100 results in the query. Think of the subquery being parent records and the results I want back as the children. Some parents have multiple children. I don't see how I could accomplish finding the children for the first 100 parents using a join. — Danny, May 12 '16 at 12:59
The where clause is dynamic and is typically more then just the first X rows in the table. I should've mentioned that in the question. — Danny, May 18 '16 at 13:49
It may be possible with your own custom implementation of the expression interface https://docs.oracle.com/javaee/7/api/javax/persistence/criteria/Expression.html — user3624390, Aug 04 '18 at 03:37

Dean Clark · Answer 1 · 2016-05-18T22:05:16.733

15

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

edited May 18 '16 at 22:05

answered May 18 '16 at 13:56

Dean Clark

3,770
1
11
26

This is using Hibernate's API, not the [Criteria API](http://docs.oracle.com/javaee/6/tutorial/doc/gjitv.html) – Danny May 18 '16 at 14:39
Oversight on my part... added the API specific info as well. Sadly, no way to do it with a single `Criteria`. – Dean Clark May 19 '16 at 11:11
This is helpful, but still not an ideal answer for me. There are times where this list will be upwards of 1000 items, unfortunately Oracle has a limit of only 1000 items in an `in` clause. I'm still not seeing anything in the API that lets me use `rownum` in the subquery, so I'll probably have to go this route and do multiple queries limiting each one to 1000 items. – Danny May 19 '16 at 11:59
I'm unclear on your business case here, so maybe more detail would help. Regardless, trying to limit the size if your inner-select seems like a non-option with pure JPA... So is there a specific reason you are limiting your result set? Perhaps a paging strategy would work were you can move your first result and max results (`query.setFirstResult(offset).setMaxResults(max)`) so you can ingest your results in chunks? – Dean Clark May 24 '16 at 15:59
I'm displaying a table to the user showing all of the parent records (the subquery). One of the columns in that tables is also the list of child records associated with the parent (the subquery I'm trying to limit) which the user can click on to see additional details. Depending on performance we want to limit these queries to an max amount of records to display on the roughly (~2000 parent records). This limit will be controlled by us through a property but we'll never want to display all records. – Danny May 25 '16 at 14:40
1

This Query can generate a too big IN (...), some databases has limit on IN clause. – John John Pichler Nov 30 '16 at 12:55
There is an open JPA ticket to add support for this: https://github.com/javaee/jpa-spec/issues/88 – shelley Jul 31 '18 at 21:57
Java EE has been moved to Eclipse; here is the updated open JPA ticket: https://github.com/eclipse-ee4j/jpa-api/issues/88 – shelley Dec 19 '18 at 16:55

Christian Beikov · Answer 2 · 2020-05-29T17:31:28.543

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', ...).

Criteria API limit results in subquery

2 Answers2

Linked