Want to fetch only 5 records with HQL [duplicate]

Question

Possible Duplicate:
How do you do a limit query in HQL

I am new to HQL. I have following working HQL query:

from Order as o where o.account.profile.userId='abc' order by o.orderID desc

This query returns me list of orders placed by user abc . User can have 0 to 5000+ orders placed in DB. But I want to display only First 5 records(Orders). I am using sublist function of java List.

Can I directly fetch only first 5 records using HQL query? which is more efficient way to write this query?

Answer 1

You can limit the results returned by a query by calling the setFirstResult() and setMaxResults() functions on the query object before running the query. Like so:

Query query = session.createQuery("from Order as o where o.account.profile.userId='abc' order by o.orderID desc");
query.setFirstResult(0);
query.setMaxResults(5); 
List result = query.list();

It depends on your DBMS used whether this will be handled in the DBMS directly.

Answer 2

Use Criteria.setMaxResults(..):

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html