October 26, 2004

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.


- 10 comments by 1 or more people Not publicly viewable

[Skip to the latest comment]
  1. 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

  2. Simply won't scale

    Not a problem when you have many GBs of memory and a database no where near as big

    01 Nov 2004, 09:12

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. You can also specify setFetchSize() which will retrieve that number of rows in one go.

    08 Feb 2006, 15:40

  9. 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

  10. 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.

October 2004

Mo Tu We Th Fr Sa Su
Sep |  Today  | Nov
            1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31

Tags

Search this blog

Most recent comments

  • One thing that was glossed over is that if you use Spring, there is a filter you can put in your XML… by Mathew Mannion on this entry
  • You are my hero. by Mathew Mannion on this entry
  • And may all your chickens come home to roost – in a nice fluffy organic, non–supermarket farmed kind… by Julie Moreton on this entry
  • Good luck I hope that you enjoy the new job! by on this entry
  • Good luck Kieran. :) by on this entry

Galleries

Not signed in
Sign in

Powered by BlogBuilder
© MMXIX