How do you do a limit query in JPQL or HQL

Question

In Hibernate 3  is there a way to do the equivalent of the following MySQL limit in HQL   select   from a table order by a table column desc limit 0  20    I don t want to use setMaxResults if possible  This definitely was possible in the older version of Hibernate HQL  but it seems to have disappeared

User · Answer

My observation is that even you have limit in the HQL  hibernate 3 x   it will be either causing parsing error or just ignored   if you have order by   desc asc before limit  it will be ignored  if you don t have desc asc before limit  it will cause parsing error

User · Answer

If can manage a limit in this mode  public List lt ExampleModel gt  listExampleModel         return listExampleModel null  null      public List lt ExampleModel gt  listExampleModel Integer first  Integer count        Query tmp   getSession   createQuery  from ExampleModel         if  first    null          tmp setFirstResult first       if  count    null          tmp setMaxResults count        return  List lt ExampleModel gt  tmp list        This is a really simple code to handle a limit or a list

User · Answer

Below snippet is used to perform limit query using HQL   Query query   session createQuery          query setFirstResult startPosition   query setMaxResults maxRows     You can get demo application at this link

User · Answer

The setFirstResult and setMaxResults Query methods For a JPA and Hibernate Query  the setFirstResult method is the equivalent of OFFSET  and the setMaxResults method is the equivalent of LIMIT  List lt Post gt  posts   entityManager  createQuery       quot select p  quot         quot from Post p  quot         quot order by p createdOn  quot    setFirstResult 10   setMaxResults 10   getResultList     The LimitHandler abstraction The Hibernate LimitHandler defines the database-specific pagination logic  and as illustrated by the following diagram  Hibernate supports many database-specific pagination options   Now  depending on the underlying relational database system you are using  the above JPQL query will use the proper pagination syntax  MySQL SELECT p id AS id1 0          p created on AS created 2 0          p title AS title3 0  FROM post p ORDER BY p created on LIMIT       PostgreSQL SELECT p id AS id1 0          p created on AS created 2 0          p title AS title3 0  FROM post p ORDER BY p created on LIMIT   OFFSET    SQL Server SELECT p id AS id1 0          p created on AS created on2 0          p title AS title3 0  FROM post p ORDER BY p created on OFFSET   ROWS  FETCH NEXT   ROWS ONLY  Oracle SELECT   FROM       SELECT          row     rownum rownum      FROM           SELECT              p id AS id1 0               p created on AS created on2 0               p title AS title3 0          FROM post p         ORDER BY p created on       row      WHERE rownum  lt       WHERE rownum   gt     The advantage of using setFirstResult and setMaxResults is that Hibernate can generate the database-specific pagination syntax for any supported relational databases  And  you are not limited to JPQL queries only  You can use the setFirstResult and setMaxResults method seven for native SQL queries  Native SQL queries You don t have to hardcode the database-specific pagination when using native SQL queries  Hibernate can add that to your queries  So  if you re executing this SQL query on PostgreSQL  List lt Tuple gt  posts   entityManager  createNativeQuery       quot SELECT  quot         quot    p id AS id   quot         quot    p title AS title  quot         quot from post p  quot         quot ORDER BY p created on quot   Tuple class   setFirstResult 10   setMaxResults 10   getResultList     Hibernate will transform it as follows  SELECT p id AS id         p title AS title FROM post p ORDER BY p created on LIMIT   OFFSET    Cool  right  Beyond SQL-based pagination Pagination is good when you can index the filtering and sorting criteria  If your pagination requirements imply dynamic filtering  it s a much better approach to use an inverted-index solution  like ElasticSearch

User · Answer

Criteria criteria curdSession createCriteria DTOCLASS class  addOrder Order desc  feild name                     criteria setMaxResults 3                   List lt DTOCLASS gt  users    List lt DTOCLASS gt   criteria list    for  DTOCLASS user   users                    System out println user getStart

User · Answer

SQL  SELECT   FROM table LIMIT start  maxRows   Query q   session createQuery  FROM table    q setFirstResult start   q setMaxResults maxRows

User · Answer

You can use below query  NativeQuery lt Object   gt  query   session createNativeQuery select   from employee limit    query setparameter 1 1

User · Answer

If you don t want to use setMaxResults  you can also use Query scroll instead of list  and fetch the rows you desire  Useful for paging for instance

User · Answer

String hql    select userName from AccountInfo order by points desc 5    This worked for me without using setmaxResults     Just provide the max value in the last  in this case 5  without using the keyword limit   P

User · Answer

You need to write a native query  refer this    Query value        SELECT   FROM user metric UM WHERE UM user id    userId AND UM metric id    metricId LIMIT  limit   nativeQuery   true  List lt UserMetricValue gt  findTopNByUserIdAndMetricId       Param  userId   String userId   Param  metricId   Long metricId       Param  limit   int limit

User · Answer

Query nativeQuery   true         value    select from otp u where u email   email order by u dateTime desc limit 1   public List lt otp gt  findOtp  Param  email   String email

User · Answer

You can easily use pagination for this        QueryHints    QueryHint name    org hibernate cacheable   value    true           Query  select   from a table order by a table column desc       List lt String gt  getStringValue Pageable pageable     you have to pass new PageRequest 0  1 to fetch records and from the list fetch the first record

User · Answer

This was posted on the Hibernate forum a few years back when asked about why this worked in Hibernate 2 but not in Hibernate 3      Limit was never a supported clause   in HQL  You are meant to use   setMaxResults      So if it worked in Hibernate 2  it seems that was by coincidence  rather than by design  I think this was because the Hibernate 2 HQL parser would replace the bits of the query that it recognised as HQL  and leave the rest as it was  so you could sneak in some native SQL  Hibernate 3  however  has a proper AST HQL Parser  and it s a lot less forgiving   I think Query setMaxResults   really is your only option

User · Answer

If you don t want to use setMaxResults   on the Query object then you could always revert back to using normal SQL

[java] How do you do a limit query in JPQL or HQL?

Examples related to java

Examples related to hibernate

Examples related to hql

Examples related to hibernate3