Hibernate pagination with collection fetch join: choosing between in-memory pagination, batch fetching, and a two-step ID query

Short answer

No Hibernate hint or configuration can force a paginated collection fetch join to apply LIMIT/OFFSET at the SQL level. The duplication of parent rows per child makes that impossible. The only reliable ways to keep pagination in the database are: 1) use a two‑step ID query, or 2) drop the fetch join and rely on lazy loading with @BatchSize.

Why no hint fixes it

When you join fetch a collection, each parent row is repeated once per child. If you then set firstResult/maxResults, the database would have to count joined rows, not distinct parents, which would break the semantics of paging. Hibernate therefore logs HHH000104 and performs pagination in memory. Query hints, fetch graphs, or entity graphs cannot change this fundamental SQL limitation.

The two‑step ID query

Step 1 – paginate parent IDs only, which is safe for LIMIT/OFFSET because each row represents one parent:

List ids = em.createQuery(
    "select p.id from Parent p where ... order by p.id", Long.class)
    .setFirstResult(page * size)
    .setMaxResults(size)
    .getResultList();

Step 2 – fetch the parents and their collections using the ID list. No pagination is needed here because the page is already defined by the ID set:

List parents = em.createQuery(
    "select distinct p from Parent p left join fetch p.children " +
    "where p.id in :ids order by p.id", Parent.class)
    .setParameter("ids", ids)
    .getResultList();

Keep the same ORDER BY clause in both queries to preserve order, and make sure the ID list stays below the database’s IN‑list limit (e.g., 1000 for Oracle, 65535 for PostgreSQL). With typical page sizes this is usually not a problem.

Sizing @BatchSize

If you drop the fetch join, annotate the collection with @BatchSize(size = N). Hibernate will load up to N collections per round‑trip when they are first accessed.

• Choose N close to the page size (20–50) to reduce the number of round‑trips to roughly one per page.
• Using a very large N wastes little memory (only proxies are instantiated) but does not reduce the number of queries once the collections are accessed.
• Remember that @BatchSize does not eliminate lazy loading; accessing a collection outside an open session will still fail.

Verifying pagination

Three ways to confirm where pagination happened:

1. Log inspection: enable SQL logging and look for the HHH000104 warning. Its presence indicates in‑memory pagination. Also verify that the generated SQL contains a LIMIT/OFFSET clause when appropriate.
2. Hibernate statistics: with hibernate.generate_statistics=true, compare sessionFactory.getStatistics().getQueryExecutionCount() and getJdbcRowsFetched(). A large row count relative to the page size signals in‑memory pagination.
3. Datasource proxy: tools like datasource‑proxy or p6spy can capture the exact JDBC row count returned. If it exceeds the pageable size, pagination was performed in the JVM.

Recommendation

For production workloads where silent full‑table fetches are unacceptable, use the two‑step ID query. Keep @BatchSize as a simpler fallback for scenarios where lazy loading is acceptable and the page size is small. Always test against the exact Hibernate 6.x version you use, as the HHH000104 message text and statistics API may vary slightly.