Hibernate ORM Pagination Configuration with Join Fetches

Hibernate provides setFirstResult() and setMaxResults() to implement database-level pagination. While these methods typically translate to LIMIT or OFFSET clauses based on the SQL dialect, the behavior changes when JOIN FETCH is used to retrieve associated collections.

When a query fetches a OneToMany relationship, the resulting Cartesian product can conflict with the pagination logic. This often leads to Hibernate performing pagination in-memory within the JVM rather than at the database level, which poses a risk of OutOfMemoryError for large datasets.

What is the recommended configuration to ensure pagination remains at the database level when join fetching collections? Are there specific mapping strategies or alternative query patterns that prevent the transition to in-memory pagination?