Fetching Large Datasets With Hibernate

The Query.list() method in Hibernate returns a List which contains all the results from the query. This works fine in most cases but if the query returns a large number of rows, memory utilisation could be an issue because the List will be a huge in-memory object.

The solution is to stream the results and scroll through them using the Query.scroll() method. For best results, the following also should be done:

• Scroll with ScrollMode.FORWARD_ONLY
• Use a StatelessSession (to avoid caching)
• Set fetch size to Integer.MIN_VALUE (this one I'm not 100% sure about, but it's recommended in some blogs)

References:

• http://www.numerati.com/2012/06/26/reading-large-result-sets-with-hibernate-and-mysql/
• http://koenserneels.blogspot.sg/2013/03/bulk-fetching-with-hibernate.html
• https://docs.jboss.org/hibernate/orm/3.6/javadocs/org/hibernate/Query.html#scroll()