Want to fetch only 5 records with HQL

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?

score 17 · Accepted Answer · answered May 18 '11 at 08:56

17

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.

answered May 18 '11 at 08:56

Florian

489
4
13

I want to do this in hql. I don't know if it's possible or not in HQL – Ankit Katiyar Dec 13 '14 at 18:18

score 4 · Answer 2 · answered May 18 '11 at 08:50

4

Use Criteria.setMaxResults(..):

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

answered May 18 '11 at 08:50

abalogh

8,239
2
34
49

score 3 · Answer 3 · answered May 18 '11 at 08:52

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

Want to fetch only 5 records with HQL

3 Answers3