Hibernate pagination with fetch‑join: incomplete pages after setFirstResult/setMaxResults

Answer

Hibernate does not automatically deduplicate root entities when you combine setFirstResult/setMaxResults with a join‑fetch. You must explicitly request distinct results (e.g., via DistinctResultTransformer, JPQL/HQL DISTINCT, or CriteriaBuilder distinct) to guarantee each parent appears only once per page.

Why this happens (explanation)

When a join‑fetch is present, Hibernate first builds the SQL join, then applies the LIMIT and OFFSET clauses to the resulting rows. If a parent has multiple child rows, those rows occupy multiple positions in the result set. Cutting the set with offset/limit can therefore discard some child rows, causing the parent to appear fewer times than expected or to be omitted entirely from a page.

Confirmed facts

• Hibernate’s generated SQL places LIMIT/OFFSET after the FROM … JOIN … section for all major dialects (MySQL, PostgreSQL, Oracle, SQL Server, DB2).
• Without a distinct transformer, the same parent entity can be returned multiple times in the same page when its collection size varies.
• Applying DISTINCT (or DistinctResultTransformer) collapses duplicate parent rows before the limit/offset is applied, giving the expected number of distinct parents per page.

Steps to fix the pagination issue

1. Add distinctness to the query:
   • JPQL/HQL: SELECT DISTINCT p FROM Parent p JOIN FETCH p.children WHERE …
   • Criteria API: criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY) (or criteria.distinct(true) in newer versions).
   • Native SQL: use SELECT DISTINCT … and map the result.
2. Verify the generated SQL (enable hibernate.show_sql=true) to confirm that LIMIT/OFFSET appears after the join.
3. Run a test with a parent that has a variable number of children and compare the distinct parent count with and without the distinct transformer.

When to choose alternative pagination strategies

If you need deep pagination (large offsets) or want to avoid the offset‑performance penalty, consider:

• Keyset pagination (seek method): fetch the next page using a where‑clause on the last seen key, e.g., WHERE p.id > :lastId ORDER BY p.id LIMIT :pageSize. This works safely with join‑fetch and distinct because the offset is not used.
• ScrollableResults: obtain a forward‑only cursor from Hibernate and iterate, which also avoids applying offset/limit to the joined rows.

Use these approaches when the page size is small but the offset can become large, or when you observe missing or duplicate root entities despite using distinct.