How do you do a limit query in 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.

Answer 1

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

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

Answer 2

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

Answer 3

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)

Answer 4

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

Answer 5

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.

Answer 6

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);