Hibernate pagination behavior with collection join fetches

Pagination and Join Fetching

Hibernate provides setFirstResult() and setMaxResults() to handle database-level pagination. When these methods are used in conjunction with JOIN FETCH on a collection, the ORM must ensure that the number of root entities returned matches the requested page size, regardless of the number of child records joined.

There is a known conflict where the SQL result set contains duplicate root entities due to the join, making it impossible for the database to apply a precise LIMIT or OFFSET without risking truncated entity graphs. This often triggers the HHH000104 warning, indicating that pagination is being performed in memory after the full result set is retrieved.

Given a requirement for strict memory constraints on the JVM, it is necessary to determine the optimal configuration to avoid in-memory pagination while maintaining the efficiency of a single query fetch.

• How can the pagination boundary be enforced at the database level when using collection fetches?
• What are the architectural trade-offs between using a two-step fetch approach versus accepting in-memory pagination for small result sets?