Hibernate: Walk millions of rows and don't leak memory

Question

The below code functions, but Hibernate never lets go of its grip of any object. Calling session.clear() causes exceptions regarding fetching a joined class, and calling session.evict(currentObject) before retrieving the next object also fails to free the memory. Eventually I exhaust my heap space.

Checking my heap dumps, StatefulPersistenceContext is the garbage collector's root for all references pointing to my objects.

public class CriteriaReportSource implements JRDataSource {

    private ScrollableResults sr;
    private Object currentObject;
    private Criteria c;
    private static final int scrollSize = 10;
    private int offset = 1;

    public CriteriaReportSource(Criteria c) {
        this.c = c;
        advanceScroll();
    }

    private void advanceScroll() {
//        ((Session) Main.em.getDelegate()).clear();
        this.sr = c.setFirstResult(offset)
                   .setMaxResults(scrollSize)
                   .scroll(ScrollMode.FORWARD_ONLY);
        offset += scrollSize;
    }

    public boolean next() {
        if (sr.next()) {
            currentObject = sr.get(0);
            if (sr.isLast()) {
                advanceScroll();
            }
            return true;
        }

        return false;
    }

    public Object getFieldValue(JRField jrf) throws JRException {
        Object retVal = null;
        if(currentObject == null) { return null; }
        try {
            retVal = PropertyUtils.getProperty(currentObject, jrf.getName());
        } catch (Exception ex) {
            Logger.getLogger(CriteriaReportSource.class.getName()).log(Level.SEVERE, null, ex);
        }
        return retVal;
    }
}

Answer 1

Couple of things I would suggest:

Try calling setCacheMode(CacheMode.IGNORE) on the Criteria before opening it.

In the advanceScroll() method, add if (sr != null) sr.close(); so that the previous ScrollableResults gets closed before you do the re-assignment to the new one.

One question: What is the reason for calling setMaxSize(), and then keeping track of the offset and then re-opening the scrollable results, why not just do this?

public CriteriaReportSource(Criteria c) {
    this.c = c;
    this.sr = c.setCacheMode(CacheMode.IGNORE)
               .scroll(ScrollMode.FORWARD_ONLY);
}


public boolean next() {
    if (sr.next()) {
        currentObject = sr.get(0);
        return true;
    }
    return false;
}

Answer 2

Don't use the stateful session here, it's just NOT the right tool to walk millions of rows and build a report. Use The StatelessSession interface instead.

Answer 3

I think one of my problems was that

if (sr.isLast()) {
    advanceScroll();
    //...

combined with

((Session) Main.em.getDelegate()).clear();
//Also, "Main.em.clear()" should do...

resulted in flushing the database out one run too early. That was the cause of exceptions regarding collections. Collections cannot be handled in a StatelessSession, so that's off the table. I don't know why session.evict(currentObject) fails to work when Session.clear() does work, but that's the way I'll have to handle it for now. I'll toss the answer points to whoever can figure that one out.

So, for now, there we have an answer. A manual scrolling window is required, closing the ScrollableResults doesn't help, and I need to properly run a Session.clear().