How to impose LIMIT on sub-query of JPA query?

Question

Is it possible to impose LIMIT on sub-query in JPA query ?

I have following query in pure SQL

select * from ipinfo 
where RangeEnd < (select RangeStart from ipinfo where RangeStart >= 1537022421 order by RangeStart asc limit 1) and (1537022421 <= RangeEnd)
ORDER BY RangeEnd desc
limit 1

Converting it directly to JPQL I'd have something like

select obj from IpInfo obj
where obj.rangeEnd < (select obj2.rangeStart from IpInfo obj2 where obj2.rangeStart >= ?1 order by obj2.rangeStart asc limit 1) and (?1 <= obj.rangeEnd)
ORDER BY obj.rangeEnd desc
limit 1

Since I can't use LIMIT in JPQL I'd have to use setMaxResults(1) on it. But what about sub-query?

Update:

I decided to go with @NamedNativeQuery for now but it's DB-specific code. If you guys can suggest pure JPA solution I'll really appreciate it.

Answer 1

I don't know how to do that with JPQL but you can probably handle it with the Criteria API, at least i'm pretty sure we can do this with Hibernate criteria subqueries so i guess it's also possible with JPA even if the JPA criteria api appears a bit confusing to me.

Check this: JPA 2.0, Criteria API, Subqueries, In Expressions

Anyway you don't even need a limit on your subquery.

Your original query: select RangeStart from ipinfo where RangeStart >= 1537022421 order by RangeStart asc limit 1 It seems you want the minimum RangeStart of your ipinfo list, which is just above a given value. The min function has been made for that.

You could simply use a subquery like this:

select min(RangeStart) from ipinfo where RangeStart >= 1537022421

Even if you need other ipinfo returned on your subquery it could be done with something like that:

select RangeEnd, anything,blabla from ipinfo where RangeStart = (
    select min(RangeStart) from ipinfo where RangeStart >= 1537022421
)

Answer 2

It is not currently possible to add a LIMIT (maxResults) or OFFSET (startIndex) via JPQL, nor is it possible to do so on a JPA subquery. There is an open JPA ticket to add this functionality:

https://github.com/eclipse-ee4j/jpa-api/issues/88

Answer 3

There is no pure JPA 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, IpInfo.class)
  .where("rangeEnd").lt()
    .from(IpInfo.class, "subInfo")
    .select("subInfo.rangeStart")
    .where("subInfo.rangeStart").geExpression("1537022421")
    .orderByAsc("subInfo.rangeStart")
    .setMaxResults(1)
  .end()
  .where("1537022421").leExpression("rangeEnd")
  .orderByDesc("rangeEnd")
  .setMaxResults(1)

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 ipinfo where RangeEnd < LIMIT((" +
    "  select RangeStart " +
    "  from ipinfo " +
    "  where RangeStart >= 1537022421 " +
    "  order by RangeStart asc" +
    "),1) " +
    "and (1537022421 <= RangeEnd)" +
    "ORDER BY RangeEnd desc"
).setMaxResults(1)

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