Hibernate built in paging support
Didn't realise this until just now, but Hibernate has built in paging support.
Query query = sess.createQuery(queryStr);
query.setMaxResults(10);
query.setFirstResult(20);
return query.list();
This handy little snippet will automagically return 10 objects, starting at object 20. And of course, chances are that hibernate has actually got all the objects you want in memory anyway, so it'll probably never even touch the database. Lovely.
In case anyone wondered, this means that there'll soon be "Older entries" and "Newer entries" links at the bottom of pages listing lots of entries.
26 Oct 2004 11:35
| 
Tags: Java
| 
Comments (10)
| 
Report a problem
10 comments by 1 or more people
[Skip to the latest comment]Jason Barker
>And of course, chances are that hibernate
> has actually got all the objects you want in
> memory anyway, so it'll probably never even
> touch the database. Lovely.
Do you really think it's a good thing? What happen when the search returns a very large number of objects, I don't think it's lovely to have everything in memory. Simply won't scale.
I hope hibernate only creates the objects within a page.
01 Nov 2004, 00:56
Not a problem when you have many GBs of memory and a database no where near as big
01 Nov 2004, 09:12
alex
but most of the time, my applications DO have a big database… is there anyway to make hibernate return only the requested page instead of store the all result in the memory?
24 Dec 2004, 02:43
Charlie Roche
In fact, the ideal solution would be to use database-side instructions like limit. We're using MySQL and were forced to write a work-around because of implementation problems with the Connector/J driver. Ideally, limit a,b would perform the query, then return b rows begining at a. Unfortunately, it doesn't work. It works in the command line – but on the server side, when the query through the driver gets interpreted the limit modifier generates an SQL syntax error. I hope that they'll fix this soon, it would allow us to clean up our side of the application.
20 Jan 2005, 11:48
sean
I think what Kieran was referring to by "in memory" is that the objects have a high likelihood of being cached by Hibernate. I believe you can tune or turn off the caching so you shouldn't be concerned about huge result sets sitting in memory.
21 Jan 2005, 16:25
pixel
With oracle, if you look at the generated SQL, it only pulls back what is needed. eg.
select * from ( select * from TABLE order by COLUMN ) where rownum < 20 and rownum >= 10;
I'm guessing that it'll do the same for other databases
09 Aug 2005, 15:23
hiephm
I'm just read the Hibernate reference and about to test it. It said that:
10.4.1.5. Pagination
If you need to specify bounds upon your result set (the maximum number of rows you want to retrieve and / or the first row you want to retrieve) you should use methods of the Query interface:
Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();
Hibernate knows how to translate this limit query into the native SQL of your DBMS.
So anyone need to worry about the big database
23 Jan 2006, 02:18
You can also specify setFetchSize() which will retrieve that number of rows in one go.
08 Feb 2006, 15:40
sip
if fact, using the methods setFirstResult(20) and setMaxResults(10) hibernate will make 10 SQL queries and fetch separately each record from the DB. The method setFetchSize() is useless…
22 Feb 2006, 15:44
Joshua Davis
sip, RE: separate queries for each record…
It's likely you have an 'N+1 SELECTS' problem. Maybe you are using iterate or if you have some other issues with lazy loading. See the hibernate documentation for more information about 'N+1 SELECTS' problems. :)
22 Feb 2006, 20:52
Add a comment
You are not allowed to comment on this entry as it has restricted commenting permissions.