Hibernate: Avoiding reading all the records to memory at once

Question

I have a large amount of rows in the database from which I need to create an XML document. I am using hibernate 3. The basic list() method in Criteria and Query interfaces looks dangerous: I quess it pretty much has to read all the records into memory even if I only iterate over them. Or is there some lazy loading magic? If not, I seem to have two options left: using scroll() or iterate() from Query (scroll is also present in Criteria). iterate doesn't look all that great either if I want to have minimal SQL roundtrips: "The first SQL query returns identifiers only". So am I right, do I have to use scroll() for this?

Answer 1

Use the setMaxResults() method on Criteria.

Criteria crit = sess.createCriteria(Cat.class);
crit.setMaxResults(maxResults);
crit.setFirstResult(firstResultIndex);
List cats = crit.list();

http://hibernate.org/hib_docs/v3/reference/en/html/querycriteria.html

http://www.hibernate.org/hib_docs/v3/api/org/hibernate/Criteria.html

Answer 2

Also, have a look at batch fetching Section 19.1.4 and 19.1.5 should do. http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#queryhql-joins-forms

Answer 3

This is what I'm planning on doing: Create a temporary table with the object IDs of all the rows I need to export:

Insert into BatchTable (ID, Seq) Select (O.ID, Sequence.Next) 
From MyObject O Where ...

In small units of work load in the objects:

Select Min(B.Seq), Max(B.Seq) From BatchTable;

for (batch = minBatch; batch <= maxBatch; batch += size) {
 beginTransaction();
 results = query("Select O From MyObject O, BatchTable B 
                  Where O.ID = B.ID and (? <= B.Seq AND B.Seq < ?)");

 exportXML(results);
 for (MyObject O : results) {
  O.setProcessed(True);
  O.update();
 }
 commit();
}

Answer 4

If you don't need to mark the objects off as procesed, you can simply use scroll() and evict the objects from the session as you are done with them.