Question
Hibernate ORM Pagination Configuration with Join Fetches
Neel Atlas
0 reputation · 26 Nov 2024, 09:31 UTC
63.3K views
Loading…
0 reputation · 26 Nov 2024, 09:31 UTC
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?
31275 reputation · 26 Nov 2024, 14:38 UTC
To keep pagination at the database level when you use JOIN FETCH for a OneToMany association, you must eliminate duplicate root rows before Hibernate applies the LIMIT/OFFSET clause. The recommended steps are:
hibernate
npss
javasetFirstResult and setMaxResults) on the same query object after the distinct transformer is set.setFirstResult() / setMaxResults() to database‑level LIMIT/OFFSET (or dialect‑specific equivalents).JOIN FETCH is present, the SQL join produces a Cartesian product: one row per parent‑child pair.OutOfMemoryError).setResultTransformer(DistinctRootEntityResultTransformer) (Hibernate 5) or query.distinct() on a JPA CriteriaQuery (Hibernate 6) collapses the duplicate rows before the limit/offset is applied, preserving database‑level pagination.If the fetched collection is large per root entity, the distinct query still transfers all child rows, which may affect bandwidth and memory. In that case consider:
@BatchSize(size = 20) (or tune via hibernate.default_batch_fetch_size) so Hibernate loads collections in separate SELECTs after the root page is fetched.@Fetch(FetchMode.SUBSELECT) loads the whole collection for the page in a second query.order by o.id) and pass the last seen value as a cursor; this avoids OFFSET costs and works well with distinct.hibernate.show_sql=true and hibernate.format_sql=true.JOIN, DISTINCT (or DISTINCT‑equivalent), and LIMIT/OFFSET.setMaxResults and that consecutive pages do not overlap.If you know the typical size of the OneToMany collection per root entity, please share it; that will determine whether batch/subselect fetching is advisable alongside the distinct transformer.
Use comments to ask for clarification. Post a solution as an answer.
3,970 reputation · 26 Nov 2024, 19:31 UTC
One addition worth making: rather than relying on the distinct transformer, Hibernate 6 lets you turn the silent in-memory fallback into a hard failure by setting hibernate.query.fail_on_pagination_over_collection_fetch=true. Enabling this in a test profile surfaces every offending query during development instead of discovering the OutOfMemoryError in production.
The more robust pattern is a two-step fetch: run the paginated query selecting only parent IDs (no collection fetch), then a second query with JOIN FETCH ... WHERE parent.id IN (:ids). Pagination stays fully in the database, and no Cartesian product ever reaches the limit clause. Make sure both queries share identical filters and a deterministic sort (e.g., primary key), or the fetched parents can drift from the intended page.
Verify with SQL logging that LIMIT/OFFSET appears and the "applying in memory" warning is gone. Note the setting name and defaults differ between Hibernate 5 and 6, so confirm against your version.