Want to fetch only 5 records with HQL [duplicate]

This question already has answers here: Closed 11 years ago.

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 开发者_StackOverflowrecords(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?

---

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.

---

Use Criteria.setMaxResults(..):

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

---

Directly from http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-examples

Query q = s.createFilter( collection, "" ); // the trivial filter
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();