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

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.

If using MySQL Connector/J even that is not enough, you need to also defeat the internal buffering done by the JDBC driver, with this:

Query query = session.createQuery(query);
query.setReadOnly(true);
// MIN_VALUE gives hint to JDBC driver to stream results
query.setFetchSize(Integer.MIN_VALUE);
ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);
// iterate over results
while (results.next()) {
    Object row = results.get();
    // process row then release reference
    // you may need to evict() as well
}
results.close();