如何对 JPA 查询的子查询施加 LIMIT? [英] 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.

解决方案

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
)

这篇关于如何对 JPA 查询的子查询施加 LIMIT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！