Avoiding HHH000104 Warning with Deep Pagination and Join Fetch in Hibernate

Direct Answer

Applying setFirstResult/setMaxResults before a JOIN FETCH via a subquery prevents the HHH000104 warning because Hibernate paginates the root entity IDs first and then fetches associations only for that bounded set. ScrollableResults does not eliminate the warning or the memory risk; the JDBC driver still processes the full joined result set before Hibernate trims it in memory. For deep pagination in Hibernate 6.x, the safest pattern is to avoid JOIN FETCH on the paginated query and instead load collections separately.

Confirmed Behavior vs. Practical Workarounds

The HHH000104 warning is confirmed Hibernate behavior: when a JPQL query contains a collection JOIN FETCH and pagination, Hibernate executes the SQL join without a database-level limit, loads every row into the persistence context, and only then applies the offset and limit in memory. This is why deep offsets remain expensive even with ScrollableResults.

The subquery approach is a practical workaround, not a change to Hibernate's core fetch strategy. It works because the outer query no longer paginates a collection fetch directly; the inner query limits the root IDs, and the outer query joins only those IDs.

Recommended Steps for Deep Pagination

1. Paginate root entities without collection fetch. Use a simple JPQL/Criteria query selecting only the root entity with setFirstResult and setMaxResults. This pushes LIMIT/OFFSET to the database.
2. Load collections in a second query. Collect the root IDs from step 1 and issue a separate query for the association, e.g. SELECT r, c FROM Root r JOIN r.collection c WHERE r.id IN :ids. This keeps each result set bounded by the page size.
3. Or use batch fetching. If lazy loading is acceptable, annotate the collection with @BatchSize (or use @Fetch(FetchMode.SUBSELECT)) so Hibernate issues a small number of follow-up queries instead of one large join.

If You Must Use a Subquery with Join Fetch

When the collection must be eagerly initialized in one query, restrict the root IDs first:

SELECT DISTINCT r FROM Root r
JOIN FETCH r.collection c
WHERE r.id IN (
    SELECT r2.id FROM Root r2
    ORDER BY r2.id
)

Apply setFirstResult/setMaxResults to the inner subquery (or the outer query if your provider supports it). Verify with JDBC logging that the database receives the limit before the join. Note that DISTINCT is still required because a root with N collection elements produces N joined rows.

Verification and Caveats

• Enable hibernate.show_sql and JDBC row-count logging to confirm the database—not Hibernate—is applying the limit.
• ScrollableResults streams the JDBC cursor, but if the SQL still contains the unconstrained join, the driver and Hibernate still materialize the full result set before trimming.
• If you switch to lazy loading, ensure the session remains open when the collection is accessed, or map the results to DTOs to avoid LazyInitializationException.

One detail would change the recommendation: do you need the collection fully initialized before the transaction closes, or can it be loaded in a subsequent batch? If it must be initialized eagerly, use the ID-subquery pattern; if a follow-up query is acceptable, the two-step or @BatchSize approach is simpler and more scalable.