Hibernate pagination behavior with fetch joins

When implementing pagination in a Java application using Hibernate ORM, the setFirstResult() and setMaxResults() methods are used to limit the result set and define the offset. This ensures the JVM heap is not overwhelmed by large datasets.

However, there is a known architectural conflict when these methods are combined with HQL fetch join operations. If a query fetches a collection of child entities while attempting to paginate the parent entities, the database cannot easily calculate the correct number of root objects due to the Cartesian product created by the join.

This often results in Hibernate retrieving the entire result set into memory to perform pagination manually, which negates the performance benefits of setMaxResults().

Technical Uncertainty

• What is the most efficient way to paginate root entities while still eagerly loading their associated collections?
• Does the use of a two-step retrieval process (fetching IDs first, then fetching data) consistently resolve the in-memory pagination issue across different SQL dialects?