Hibernate pagination behavior with fetch joins

Answer

The most efficient way to paginate root entities while still eagerly loading their collections is to use a two‑step process: first fetch distinct root identifiers with the same joins (but without loading the collections), apply setFirstResult() and setMaxResults() to that identifier query, then fetch the full entities using those identifiers with an IN clause and the desired fetch join.

Confirmed facts

• When a HQL/Criteria query uses join fetch on a collection or one‑to‑many association, Hibernate produces duplicate rows for the root entity – one row per matched child.
• Applying setFirstResult()/setMaxResults() directly on such a query limits the raw row count before duplicates are collapsed, which can return fewer root entities than expected and may force Hibernate to perform pagination in memory.
• The behavior is documented for Hibernate 5.0+; earlier versions relied on the older Criteria transformer, but the logical issue remains the same.

Likely explanation

The identifier‑first query eliminates duplicates because it selects only the root’s primary key (or a distinct set of keys) while keeping the same joins. Limiting this clean set yields the correct page of root IDs. The second query then retrieves the full root entities with the desired fetch join for those IDs, so no duplicate‑row problem affects the limit.

1. Build a query that selects DISTINCT r.id (or the root entity with SELECT DISTINCT r) from the same joins you need for the fetch, but do not include the collection in the select clause.
2. Apply setFirstResult(pageSize * pageNumber) and setMaxResults(pageSize) to this query and execute it to obtain a list of IDs for the current page.
3. If the ID list is not empty, create a second query: FROM RootEntity r LEFT JOIN FETCH r.collection WHERE r.id IN (:ids). Set the ID list as a parameter and execute.
4. Return the results of the second query as the paginated page.

Dialect consistency and practical notes

This two‑step method works across all major SQL dialects supported by Hibernate (PostgreSQL, MySQL, Oracle, SQL Server, DB2) because it relies only on standard SELECT DISTINCT, LIMIT/OFFSET (or dialect equivalents), and an IN list. The main practical limitation is the maximum number of parameters allowed in an IN clause by some JDBC drivers; if a page could contain more than a few thousand IDs, consider batching the ID list or using a temporary table.

To verify the generated SQL, enable Hibernate logging (show_sql=true or logging.level.org.hibernate.SQL=DEBUG) and confirm that the first query contains a DISTINCT on the root identifier and the appropriate limit/offset, while the second query uses the IN clause with the fetch join.

Missing diagnostic detail that could affect the recommendation: What is the expected maximum number of root entities per page (or total result size)? Knowing this helps decide whether the ID list will stay within driver limits or if batching/temporary tables are required.