Avoiding HHH000104 Warning with Deep Pagination and Join Fetch in Hibernate

Objective

In a Hibernate‑based Java application we need to retrieve a large dataset page by page while eagerly loading associated collections via a JPQL join fetch. The goal is to keep memory usage bounded even when the offset exceeds 1,000 rows.

Current Situation

Hibernate provides setFirstResult and setMaxResults on Query or TypedQuery to express offset and limit. When a join fetch is used, the framework emits the warning HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!, indicating that the limit is applied after the join fetch and all rows are loaded into memory before trimming.

Unresolved Decision

It is unclear whether applying setFirstResult/setMaxResults before the join fetch (e.g., via a subquery) eliminates the warning and improves performance, or whether alternatives such as ScrollableResults or avoiding join fetch altogether are more appropriate. The documentation does not explicitly state the best practice for deep pagination with join fetch in Hibernate 6.x.

Questions

• Does using a subquery to apply LIMIT/OFFSET before the join fetch prevent the HHH000104 warning?
• Is ScrollableResults a viable alternative for deep pagination when a join fetch is required?
• What is the recommended approach for paginating large collections with join fetch in Hibernate 6.x?