Hibernate - Java pagination - Out of memory exception

Question

I am using the following piece of code to get results from DB as result set is huge.

Is it the correct way doing? I am running in to Out of memory exception and I suspect it is because of this.

// div is set to 10,000
// i have calculated how many times i need do it using a count query
// and that value is being used in iteration variable

Query bigQ=session.createSQLQuery(bigQuery);

for(int i=0;i<iteration;i++)    
{
    bigQ.setFetchSize(div);                         
    bigQ.setMaxResults(i*div);

    List<Object[]> result=bigQ.list();

    // now i am using the result to get the values 
    for(Object[] a:result)
    {
        // rest of operations
    }
}

Note:

I have set enough Xms and -Xmx in the jboss run.conf.
I am not able use scrollable set as the postgre driver is not supporting it.
I can not use createQuery as in that case hibernate is generating lots of queries, so i am using a big join query, along with createSQLQuery.

axtavt · Accepted Answer · 2011-04-01 11:40:56Z

2

Your code looks strange - the correct way to paginate results would be this:

bigQ.setFirstResult(i * div);
bigQ.setMaxResults(div);

Also make sure that your session cache doesn't grow - if your query returns only scalar values (not entities) it shouldn't be a problem, but if you load some entities in // rest of operations, it can.

answered Apr 1, 2011 at 11:40

axtavt

243k42 gold badges518 silver badges486 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lam Over a year ago

Thank you for the reply..how do i make sure that session cache does not grow ?

lam Over a year ago

Thanks again for giving the hint of clearing the session cache. I am able to recover from the out of memory error after using session.evict()

Justin Thomas · Accepted Answer · 2011-04-01 11:36:05Z

0

Use offset and limit... http://www.petefreitag.com/item/451.cfm

answered Apr 1, 2011 at 11:36

Justin Thomas

5,8473 gold badges44 silver badges63 bronze badges

Collectives™ on Stack Overflow

Hibernate - Java pagination - Out of memory exception

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related