Hibernate Stored Procedure invocation leads to OutOfMemory

I am using Hibernate's nam开发者_如何学Pythoned Query to execute a stored procedure returning a very large dataset ( over 2 million rows ) The DB is Oracle 11g

for instance: Query query = session.getNamedQuery(procName);

I know from the hibernate documentation, you cannot use setFirstResult/setMaxResult as stated on http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html.

All is well, on a smaller dataset like 100,000 rows. However, as soon as I test with a 1,000,000 I get the OutOfMemory error.

From the query object, I get a listIterator. I assumed that the data was only fetched once, I iterate over the listiterator ( query.list().listIterator())

I do not have a 2nd level cache configured. Will these settings help at all, when dealing with an Oracle Stored Procedure.

query.setCacheMode(org.hibernate.CacheMode.IGNORE); query.setFetchSize(1000);

query.scroll(org.hibernate.ScrollMode.FORWARD_ONLY);

Basically, how do I manage large dataset retrieval using stored procs in Hibernate.

Million thanks

It depends entirely on how you handle the data returned from the query. If you treat it as a typical query--that is, call list() on it and store the result in a Collection--then you're going to bust your memory limit with that many rows. The key is to get and process them in batches. I believe the typical way of doing so with Hibernate is getting the results as a ScrollableResults and remembering to flush() and clear() the session periodically since it acts as the first-level cache and will store all the objects being loaded. Look around for descriptions of batch processing in Hibernate, but be careful not to get confused with Hibernate's batch fetching and related concepts. They're two different animals.

Your issue is less one of Hibernate than one of memory usage. I had a similar situation where a JMS message worked fine with a small amount of data, then everything blew up with more. Since I doubt you need all 2mm records in memory at once, see if you can get the stored proc updated to take a limit and an offset so that it can return slices of data as opposed to only the whole dataset.