Harvesting Large Result Sets in Hibernate Without Loading the World Into Memory

You need to export 2 million Order rows with line items for an ETL job. A normal HQL query with a list() loads everything into the persistence context and you hit OOM or a long-running transaction that blocks flushes. The useful takeaway is that Hibernate does not need to load everything at once. You can harvest rows in controlled batches with fetchSize and ScrollableResults, and harvest associations with batch fetching, while keeping the session boundary tight.

The round-trip tax you are actually paying

By default many JDBC drivers fetch rows one at a time. Hibernate can ask the driver to prefetch a buffer via the fetchSize hint. That hint is passed to the driver and the database, and the maximum effective size is driver and DBMS specific.

Cursor-based pagination with setFirstResult and setMaxRows is portable for small pages, but setMaxRows is not uniformly supported and without an explicit ORDER BY the page contents are non-deterministic across executions. Pagination also re-executes the query for each page.

Stream a result set with scroll and fetchSize

For exports, data dumps, or long scans, the scroll method combined with fetchSize lets you process rows one batch at a time while the database keeps a cursor open.

Typical pattern:

// run inside a read-only transaction, short-lived session
Session session = entityManager.unwrap(Session.class);
session.setJdbcBatchSize(0); // avoid confusing batch semantics

ScrollableResults scroll = session.createQuery(\"from Order o where o.status = :status\").setParameter(\"status\", OrderStatus.COMPLETED)
    .setFetchSize(500)
    .setReadOnly(true)
    .setCacheable(false)
    .scroll(ScrollMode.FORWARD_ONLY);

try {
    while (scroll.next()) {
        Order o = (Order) scroll.get()[0];
        // process o, do not keep reference across iterations
    }
} finally {
    scroll.close();
}

Where to run: application code with a transactional service method. Required permissions: read access to the Order table and ability to hold a database cursor for the duration of the stream. Risks: the JDBC connection stays pinned to the session for the whole iteration. Forgetting scroll.close() leaks the cursor and the connection. fetchSize defaults vary by driver; omitting setFetchSize can cause row-by-row fetching and negate the benefit.

Practical check: enable Hibernate statistics and observe the number of JDBC fetches versus rows returned. On the database side you can compare fetch count against actual rows returned using database specific monitoring views. Expect fewer round trips when fetchSize is respected.

Harvest associations with batch fetching

Even with streaming, accessing a lazy collection inside the loop triggers an N+1. Hibernate can harvest associated entities in batches at session initialization.

Mapping options include the fetch sub element in XML or Fetch annotations. Example for a @OneToMany collection:

@BatchSize(size = 50)
@OneToMany(mappedBy = \"order\")
private Set<OrderLine> lines = new HashSet<>();

Or via HQL join fetch for a known graph depth:

from Order o join fetch o.lines where o.id in (:ids)

Effectiveness depends on graph depth and session flush timing. Aggressive batch fetching can inflate memory consumption in long running sessions, especially with deeply nested entity graphs. Choose subselect or instance strategies when the graph is wide.

Trade-offs and how to verify

Streaming reduces memory but holds a DB cursor and a connection for longer. Batch fetching reduces round trips but increases memory if you keep entities attached.

Verification steps you can apply without inventing metrics:

• Enable Hibernate Statistics via properties hibernate.generate_statistics=true and expose the statistics bean. Look at fetch plan counts for collection associations.
• Run the same query with setFetchSize 20 and without it, and compare JDBC fetch count against actual rows returned using database specific monitoring views.
• Always add ORDER BY when using setFirstResult/setMaxRows to keep page navigation deterministic.

Limitations: scroll is not compatible with some second-level cache modes, and not all databases support forward-only scroll with fetchSize. Long transactions increase lock risk.

Close the session promptly after the harvest, process in small chunks, and keep the persistence context clear if you must iterate for a long time. That is the practical engineering decision: harvest in batches, measure round trips, and bound memory with explicit session boundaries.