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

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.

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<Post> posts = entityManager
.createQuery(
    "select p " +
    "from Post p " +
    "order by p.createdOn ")
.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 <= ?
)
WHERE rownum_ > ?

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<Tuple> posts = entityManager
.createNativeQuery(
    "SELECT " +
    "   p.id AS id, " +
    "   p.title AS title " +
    "from post p " +
    "ORDER BY p.created_on", 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.

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.

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

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<String> getStringValue(Pageable pageable);

you have to pass new PageRequest(0, 1)to fetch records and from the list fetch the first record.

Criteria criteria=curdSession.createCriteria(DTOCLASS.class).addOrder(Order.desc("feild_name"));
                criteria.setMaxResults(3);
                List<DTOCLASS> users = (List<DTOCLASS>) criteria.list();
for (DTOCLASS user : users) {
                System.out.println(user.getStart());
            }

If can manage a limit in this mode

public List<ExampleModel> listExampleModel() {
    return listExampleModel(null, null);
}

public List<ExampleModel> 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<ExampleModel>)tmp.list();
}

This is a really simple code to handle a limit or a list.

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<UserMetricValue> findTopNByUserIdAndMetricId(
    @Param("userId") String userId, @Param("metricId") Long metricId,
    @Param("limit") int limit);

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

 // SQL: SELECT * FROM table LIMIT start, maxRows;

Query q = session.createQuery("FROM table");
q.setFirstResult(start);
q.setMaxResults(maxRows);

You can use below query

NativeQuery<Object[]> query = session.createNativeQuery(select * from employee limit ?)
query.setparameter(1,1);

@Query(nativeQuery = true,
       value = "select from otp u where u.email =:email order by u.dateTime desc limit 1")
public List<otp> findOtp(@Param("email") String email);

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)

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.