Setting ORDER BY and LIMIT clause on JPA Query

Question

I'm very, very new to Hibernate and JPA. I want to be able to apply ORDER BY and LIMIT clauses to a Hibernate(?) query, but am coming up empty. NOTE: I inherited this code.

Here is the current code:

public SomeCoolResponse getSomeCoolResponse(String myId) {
    String queryString = "select aThing from AWholeBunchOfThings aThing " +
    "join aThing.thisOtherThing oThing join oThing.StillAnotherThing saThing " + 
    "where saThing.subthing.id = :id";

    Query q = getEntityManager().createQuery(queryString);
    q.setParameter("id", myId);

    List<MyThings> list = q.getResultList();

    if(list.size() > 0) {
        return list.get(0);
    }
    return null;
}

Instead of getting an entire list and then just returning the first result (which is the only one we need), I'd like to be able to apply a LIMIT 0,1 clause so that the query will be faster. Also, the query needs to be sorted descending on aThing.created which is a UNIX timestamp integer.

I've tried altering queryString like this:

String queryString = "select aThing from AWholeBunchOfThings aThing " +
    "join aThing.thisOtherThing oThing join oThing.StillAnotherThing saThing " + 
    "where saThing.subthing.id = :id ORDER BY aThing.created LIMIT 0,1";

But Hibernate still returns the entire set.

I've looked at using the JPA CriteriaBuilder API, but it hurt my brain.

I'm a total n00b when it comes to this, and any help is greatly appreciated!

JPQL is not SQL; database specifics like `limit` are not supported. — Hauke Ingmar Schmidt, Dec 30 '13 at 23:41

score 7 · Accepted Answer · edited May 23 '17 at 11:54

7

I think you need

q.setMaxResults(1);

See also the accepted answer here.

How do you do a limit query in HQL?

As to the "order by" clause you may include it in the queryString.

edited May 23 '17 at 11:54

Community

1
1

answered Dec 30 '13 at 23:37

peter.petrov

38,363
16
94
159

2

Then should I be able to use `q.getSingleResult()`? – daniel0mullins Dec 30 '13 at 23:41
@daniel0mullins You mean uniqueResult()? Just call setMaxResults and your query will return at most one record. If you look at the generated SQL I think it will contain the LIMIT clause so it's exactly what you needed. – peter.petrov Dec 30 '13 at 23:42
@daniel0mullins OK, I thought we were talking org.hibernate.Query. – peter.petrov Dec 30 '13 at 23:44
like I said - total n00b – daniel0mullins Dec 30 '13 at 23:44
@daniel0mullins Then look here. http://stackoverflow.com/questions/11655870/jpa-2-criteriaquery-using-a-limit – peter.petrov Dec 30 '13 at 23:45
@daniel0mullins Hm, seems there's such a method on javax.persistence.Query http://docs.oracle.com/javaee/5/api/javax/persistence/Query.html#setMaxResults%28int%29. So I guess my original answer applies to javax.persistence.Query too. – peter.petrov Dec 30 '13 at 23:47
I think we're using Java 7(?)...regardless - your suggestion worked! Sped up my query by about 1000% down to 5s from 50s. Thanks! – daniel0mullins Dec 30 '13 at 23:57
Does the database return an entire list which is then limited by the jpql engine or does the jpql engine create a native sql query with limit clause? – Prachi Nov 28 '18 at 19:27

score 2 · Answer 2 · answered Dec 10 '16 at 08:19

2

The JPQL equivalent to LIMIT start,max is:

setFirstResult and setMaxResults:

q.setFirstResult(start); 
q.setMaxResults(limit);

answered Dec 10 '16 at 08:19

Ebru Yener

674
6
17

Setting ORDER BY and LIMIT clause on JPA Query

2 Answers2