Hibernate Pagination: Transitioning from In-Memory Truncation to Database-Level Limits

When implementing pagination using setFirstResult() and setMaxResults() in Hibernate ORM, the framework typically delegates the limit and offset logic to the database dialect. However, a specific behavior occurs when queries involve collection join fetches.

If a query fetches a collection (e.g., @OneToMany) using a join, Hibernate may be unable to determine the correct number of root entities due to the Cartesian product generated by the join. In these scenarios, Hibernate triggers the HHH000104 warning and performs pagination in-memory, loading the entire result set into the JVM before applying the limits.

For large datasets, this behavior introduces a risk of OutOfMemoryError and significant latency. To avoid this, developers often consider alternative strategies such as two-step ID-based fetching or using separate queries for collections.

• How can the transition from in-memory pagination to database-level pagination be achieved without sacrificing the efficiency of eager loading?
• What are the performance trade-offs between using a separate ID-based window query versus utilizing @BatchSize to mitigate the HHH000104 issue?