Hibernate pagination and JOIN FETCH in-memory warning

The Trigger for In-Memory Pagination

In Hibernate 5.x and 6.x, the in-memory pagination warning (HHH000104) is triggered when you combine setFirstResult() or setMaxResults() with a JOIN FETCH on a collection association (One-to-Many or Many-to-Many).

This occurs because a SQL join on a collection produces a Cartesian product, resulting in multiple rows for a single root entity. If the database applied a LIMIT or OFFSET to this result set, it would truncate the rows of the root entities themselves, leading to incomplete data or an incorrect number of returned objects. To guarantee the correct number of root entities, Hibernate retrieves the entire result set into the JVM and applies the pagination logic in memory.

How to Restructure the Query

To ensure the database handles the limit and offset while still fetching collections efficiently, you must decouple the pagination of the root entities from the loading of their associations.

Option 1: Two-Step ID Retrieval (Recommended for Deep Paging)

This approach ensures constant-time paging by first identifying the specific primary keys needed for the current page.

1. Fetch IDs: Execute a paginated query that selects only the IDs of the root entity. Since no collection is fetched, the database can apply LIMIT and OFFSET correctly.
2. Fetch Entities: Execute a second query using JOIN FETCH to retrieve the full entities and their collections, filtering by the list of IDs obtained in step one.

Option 2: Batch Fetching (Alternative)

Instead of using JOIN FETCH in the primary query, remove the fetch join and configure Hibernate to load collections in optimized batches.

• Add @BatchSize(size = 20) to the collection field in the entity class.
• Alternatively, set the global property hibernate.default_batch_fetch_size in your configuration.

When you access the collection for the first entity on the page, Hibernate will load the collections for up to 20 root entities in a single subsequent query using an IN clause, avoiding the N+1 problem without triggering in-memory pagination.

Verification and Diagnostics

To verify if your changes are working, check the following:

• Logs: Ensure the HHH000104 warning no longer appears in the application logs.
• SQL Output: Enable hibernate.show_sql=true. The first query in the sequence should now contain a LIMIT or OFFSET clause (depending on your database dialect).

Diagnostic Detail: Are you using a DISTINCT keyword in your JPQL? While DISTINCT prevents duplicate Java objects in the final list, it does not prevent the in-memory pagination warning or the underlying SQL row duplication.