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

Try using scroll() in conjunction with this:

http://docs.jboss.org/hibernate/core/3.3/api/org/hibernate/StatelessSession.html

A command-oriented API for performing bulk operations against a database.

A stateless session does not implement a first-level cache nor interact with any second-level cache, nor does it implement transactional write-behind or automatic dirty checking, nor do operations cascade to associated instances. Collections are ignored by a stateless session. Operations performed via a stateless session bypass Hibernate's event model and interceptors. Stateless sessions are vulnerable to data aliasing effects, due to the lack of a first-level cache.

For certain kinds of transactions, a stateless session may perform slightly faster than a stateful session.

Answer 2

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