Geek Answers Handbook

How to impose a LIMIT on a JPA request subquery?

Is it possible to impose a LIMIT on a subquery in a JPA request?

I have the 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

By converting it directly to JPQL, I will 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 cannot use LIMIT in JPQL, I would have to use setMaxResults(1)on it. But what about a sub request?

Update:

I decided to go with it @NamedNativeQueryfor now, but this is DB specific code. If you guys can offer a clean JPA solution, I will be very grateful.

+8
source share
3 answers

, JPQL, , , API , , , Hibernate, , JPA, JPA api .

: JPA 2.0, API , ,

.

: RangeStart ipinfo, RangeStart >= 1537022421 RangeStart 1 , RangeStart ipinfo, . min.

, :

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

ipinfo, , - :

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

LIMIT (maxResults) OFFSET (startIndex) JPQL, JPA. JPA :

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

0

JPA. SQL, SQL-. JPA - .

API , Blaze-Persistence.

, / : https://persistence.blazebit.com/documentation/1.2/core/manual/en_US/index.html#pagination

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)

, LIMIT SQL, Blaze-Persistence. , Blaze-Persistence EntityManagerFactory, .

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)

EclipseLink, OPERATOR('LIMIT',...).

0

More articles:


All Articles