Hibernate pagination behavior with collection join fetches

To enforce pagination at the database level when using collection fetches, you must decouple the pagination logic from the graph fetching logic using a two-step fetch strategy.

Hibernate cannot safely apply SQL LIMIT or OFFSET when a JOIN FETCH on a collection is present. Because a single root entity can produce multiple rows in a SQL result set (one for each child record), a database-level limit would truncate the result set mid-entity, leading to incomplete object graphs. To avoid this data corruption, Hibernate retrieves the entire result set into the JVM heap and performs pagination in memory, which triggers the HHH000104 warning.

The Two-Step Fetch Implementation

To maintain strict JVM memory constraints, separate the identification of the page from the retrieval of the data:

1. Identify IDs: Execute a query to retrieve only the primary keys of the root entities. Apply setFirstResult() and setMaxResults() here. This ensures the database handles the pagination boundary.
2. Fetch Full Graphs: Execute a second query using JOIN FETCH to retrieve the entities and their collections, filtering by the IDs obtained in the first step via an IN clause.

// Step 1: Paginated ID retrieval (Database-level limit) 
List<Long> ids = entityManager.createQuery("select u.id from User u", Long.class)
    .setFirstResult(0)
    .setMaxResults(20)
    .getResultList();

// Step 2: Full graph fetch for specific IDs
List<User> users = entityManager.createQuery("select u from User u join fetch u.orders where u.id in :ids", User.class)
    .setParameter("ids", ids)
    .getResultList();

Architectural Trade-offs

Approach	Pros	Cons
Two-Step Fetch	Constant memory footprint; safe for large datasets.	Two network round-trips; IN clause size limits.
In-Memory Pagination	Single query; simpler implementation.	High risk of OutOfMemoryError; scales poorly.

Assumption: This solution assumes the page size is small enough to fit within the database's maximum IN clause limit (e.g., 1,000 elements). If your page size exceeds this, you must partition the ID list into smaller batches for the second query.

To provide a more tailored recommendation, please specify if you are using a specific Hibernate version (e.g., 5.x or 6.x) as some newer versions offer different optimization paths for window functions.