CODEWITHSUNDEEP
javasqlhibernatejpa
RNJ
RNJ

Reputation: 15552

Sublisting a result from Hibernate

I am implementing pagination on a set of result from hibernate. Currently I am doing

long count = getCountResultForNamedQuery();
List result = getResultForNamedQuery()

where the two queries are something like

select count(*) from addresses where country = 'USA';

and

select * from addresses where country = 'USA';

For the pagination I usually update getResultForQuery so that is has .setFirstResult and setMaxResult set. This will then only return the section that I want. I have to do a count so that I know how many pages to show on the pagination part of the page.

I was wondering if I could be more efficient. Could I just do

List result = getResultForQuery();
int count = result.size();
List subResult = result.subList(start, end);

Does this make sense? I am trying to only call one query if possible. The question is how clever hibernate is? When I call size will it count the object shells it returns or does it try and resolve everything and then hit the database? Which is the better pattern to use?

Apologies if the example is not clear enough. I have tried to simplify it as much as possible and may have simplified it too much!

Thanks in advance!

Upvotes: 0

Views: 1030

Answers (2)

JB Nizet
JB Nizet

Reputation: 691715

Your second solution makes sense, but could lead to a performance disaster, since it would load in memory all the addresses from USA, whereas you're only interested in their count and in a small range of these addresses. When you execute a query, the query returns a list, and this list is populated with all the results of the query. That's why setFirstResult and setMaxResults are useful.

If you don't care about absolutely exact counts, you could execute the count query only once, and execute the "result" query for each page.

Upvotes: 2

Pramod Kumar
Pramod Kumar

Reputation: 8014

You can do as follows -

Query query = getSession().createQuery("your Query").setFirstResult(start_Index).setMaxResults(Number_Of_Records_You_Want_to_Display_on_page);

For Example - 

Query query = getSession().createQuery("select * from employee").setFirstResult(101).setMaxResults(100);

Upvotes: 0

Related Questions