Hibernate JOIN FETCH Queries Trigger In‑Memory Pagination When Using setFirstResult/setMaxResults

The goal is to retrieve a large dataset from a relational database using Hibernate while applying a JOIN FETCH to eagerly load associated entities and simultaneously limiting the result set with setFirstResult() and setMaxResults() for pagination. The application must avoid loading the entire joined result into the persistence context, which would cause an OutOfMemoryError, and must work across different Hibernate Dialects (e.g., PostgreSQL, Oracle) without resorting to manual SQL.

Does Hibernate push the LIMIT/OFFSET clause into the SQL when a JOIN FETCH is present, or does it fall back to in‑memory pagination? Can ScrollableResults be used with JOIN FETCH to stream rows without materializing the full set? Should the association be fetched lazily and loaded via a separate query or DTO projection to guarantee efficient pagination?