How to Do a Limit Query in Jpql or Hql

How do you do a limit query in JPQL or HQL?

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.

JPQL limit query

JPQL does not provide a mechanism to limit queries. This is most often achieved by using the setMaxResults() method on the Query. If you must avoid specifying this in Java code, you could make a view in the database that contains your query and performs the limit. Then map an entity to this view as you would a table.

Example:

List<String> resultList= query.setMaxResults(100).getResultList();

How to set a limit to inner query in Hibernate?

look at How do you do a limit query in HQL?

you can't limit a query written in hql with hql. You need to make a call to setMaxResults on the Query object, which i guess will prevent you from applying a limit on a hql subquery.

This leave you with the option of

  • writting it as a sql-query or
  • trying to find another way to write your hql query so that you don't need a limit in a subquery.

Setting ORDER BY and LIMIT clause on JPA Query

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.



Related Topics



Leave a reply



Submit