Hibernate Pagination: Transitioning from In-Memory Truncation to Database-Level Limits

Direct Answer

To eliminate the HHH000104 warning and achieve database-level pagination while preserving eager loading, use a two-step ID window query: first fetch only the parent entity identifiers with setFirstResult/setMaxResults, then load the full entities and their collections in a second query using the ID list. @BatchSize does not resolve HHH000104 for eager join fetches; it only optimizes lazy collection loading after the parent query completes.

Why In-Memory Pagination Happens

When a JPQL/HQL query uses JOIN FETCH on a @OneToMany collection, the join produces a Cartesian product—each parent row repeats once per child. Hibernate cannot apply LIMIT/OFFSET to the parent entities reliably because the database sees multiplied rows. It therefore logs HHH000104 and falls back to loading the entire result set into the JVM before truncating, risking OutOfMemoryError on large datasets.

Two-Step ID Window Pattern (Recommended)

1. Page the parent IDs only — run a distinct projection query:

   SELECT DISTINCT e.id FROM Entity e JOIN FETCH e.collection WHERE ... ORDER BY e.id

   Apply setFirstResult(offset) and setMaxResults(pageSize) here. The database returns exactly one row per parent.
2. Fetch full entities + collections by ID list — use the collected IDs in a second query:

   FROM Entity e JOIN FETCH e.collection WHERE e.id IN (:ids) ORDER BY e.id

   No pagination clauses needed; the IN list size equals the page size.

This keeps eager loading (single round-trip for collections) while delegating pagination to the database.

Trade-offs: ID Window Query vs. @BatchSize

Aspect	ID Window Query	@BatchSize on Lazy Collection
Eliminates HHH000104	Yes	No (only works when fetch=LAZY)
Round-trips	2 per page	1 parent query + N/ batchSize collection queries
Memory pressure	Low (page-sized results)	Low if batches are small
IN-list limits	Watch for DB-specific limits (e.g., 1000/2100 params)	N/A
Eager loading preserved	Yes (second query uses JOIN FETCH)	No — requires fetch=LAZY

Use ID window query when you need eager loading and DB-level pagination. Use @BatchSize only if you can switch the collection to fetch = FetchType.LAZY and accept batched lazy loads.

Verification Steps

1. Enable hibernate.generate_statistics=true and confirm HHH000104 disappears after the change.
2. Turn on show_sql=true (or a JDBC proxy) and verify:
   • First query contains LIMIT/OFFSET (or dialect equivalent) on the ID projection.
   • Second query uses IN (:ids) without pagination clauses.
3. Load-test with a dataset larger than heap; monitor GC and heap usage (VisualVM, JFR). The new approach should stay flat; the old path would spike.

One Diagnostic Detail Needed

What is the typical page size and maximum collection size per parent? If page size × max children exceeds your database's IN parameter limit (often 1000–2100), the second query may need a temporary table or cursor-based fetch instead of an IN list.