Search code examples
jpasubqueryjpql

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

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.

Solution

  • 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
)