Is there a way to combine streaming data retrieval with hibernate?

Question

For the purposes of handling very large collections (and by very large I just mean "likely to throw OutOfMemory exception"), it seems problematic to use Hibernate because normally collection retrieval is done in a block, i.e. List values=session.createQuery("from X").list(), where you monolithically grab all N-million values and then process them.

What I'd prefer to do is to retrieve the values as an iterator so that I grab 1000 or so (or whatever's a reasonable page size) at a time. Apart from writing my own iteration (which seems like it's likely to be re-inventing the wheel) is there a hibernate-native way to handle this?

Answer 1

You could do

Iterator iter = session.createQuery("from X").iterate();

Answer 2

Actually session.scroll() may be better than iterate in this situation. Iterate runs one query to get all the ids and then fetches the full items one by one as you process them. Scroll uses the underlying JDBC scroll functionality which retrieves full objects but keeps a cursor open to the database. With scroll you can set the batch size to figure out the most optimal number to return at a time. If loading the N-million ids is still taking too much memory, scroll is the answer, and I suspect it will be more efficient in either case.

Neither is going to automatically close the session.