Question
Pagination limits and ordering guarantees in Hibernate Criteria API
Mira Nea
0 reputation · 08 Oct 2023, 09:24 UTC
112.6K views1
Loading…
0 reputation · 08 Oct 2023, 09:24 UTC
When paginating results with Hibernate’s Criteria API using setFirstResult and setMaxResults, the goal is to retrieve a stable, non‑overlapping slice of data across multiple executions.
However, without an explicit ORDER BY clause the database may return rows in arbitrary physical order, and fetch joins on collection associations can produce duplicate root entities because the limit is applied after the join. Dialect‑specific limit handling (e.g., MySQL LIMIT, Oracle ROWNUM) may also interact with optimizer hints, further affecting the generated SQL.
Given these uncertainties, what conditions guarantee repeatable pagination, how should fetch joins be combined with limits to avoid duplicates, and which dialect‑specific settings ensure the generated limit/OFFSET clauses are respected?
Repeatable, non-overlapping pagination with Hibernate Criteria API requires three conditions: (1) an explicit ORDER BY on a unique column or unique column combination, (2) transaction isolation of REPEATABLE READ or SERIALIZABLE to prevent phantom rows between page fetches, and (3) the correct Hibernate Dialect configured for your database version so that setFirstResult/setMaxResults translates to a proper LIMIT/OFFSET, ROW_NUMBER(), or ROWNUM clause. Fetch joins on collections break the limit because the SQL LIMIT is applied after the join expands rows; use a two-query strategy (paginated ID subquery, then fetch collections by ID list) or accept in-memory deduplication via Criteria.DISTINCT_ROOT_ENTITY with the understanding that duplicate rows still travel over the wire.
Without ORDER BY, the database returns rows in physical storage order, which can change between executions due to page splits, vacuum operations, or parallel query plans. A unique sort key (primary key or a unique index) guarantees a deterministic sequence. Even with ordering, READ COMMITTED isolation allows new rows to appear or existing rows to move between page requests, causing duplicates or gaps. REPEATABLE READ (or SERIALIZABLE) provides a stable snapshot for the transaction duration.
When you write criteria.setFetchMode("children", FetchMode.JOIN), Hibernate generates a SQL join that multiplies the root entity once per child row. The database applies LIMIT N OFFSET M to this multiplied result set, so you may receive fewer than N distinct root entities. setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) removes duplicates in Java memory but does not alter the SQL — the full joined result set is still transferred. For large collections, prefer two queries:
Paginated query selecting only root-entity IDs with ORDER BY and setFirstResult/setMaxResults.
Second query fetching the collections for those IDs using Restrictions.in("id", idList) and a fetch join or batch fetch.
Hibernate selects the limit handler based on the configured Dialect class:
hibernate
processingMySQL8Dialect → LIMIT ? OFFSET ?PostgreSQLDialect → LIMIT ? OFFSET ?Oracle12cDialect → OFFSET ? ROWS FETCH NEXT ? ROWS ONLYOracle10gDialect → nested query with ROWNUMSQLServer2012Dialect → OFFSET ? ROWS FETCH NEXT ? ROWS ONLYUsing an older dialect on a newer database (e.g., MySQL5Dialect on MySQL 8) may produce suboptimal syntax or miss window-function optimizations. Verify the resolved dialect at startup via logs (Using dialect: org.hibernate.dialect.MySQL8Dialect).
Enable SQL logging: hibernate.show_sql=true, hibernate.format_sql=true. Confirm the generated query contains ORDER BY and the expected limit clause.
Run the same paginated query twice inside a REPEATABLE READ transaction; concatenate results and assert no duplicate primary keys and no gaps in the ordered sequence.
For fetch-join scenarios, compare root-entity counts with and without DISTINCT_ROOT_ENTITY to measure duplication overhead.
Use EXPLAIN ANALYZE (or equivalent) on the generated SQL to verify the optimizer uses an index on the ORDER BY columns and respects the limit.
What is your target database product and version, and which Dialect class is currently configured? The exact limit/OFFSET syntax and any optimizer-hint interactions depend on this combination.
Use comments to ask for clarification. Post a solution as an answer.
31,775 reputation · 08 Oct 2023, 12:29 UTC
One point worth adding to the solid answer above: even with a unique ORDER BY and the right dialect, large setFirstResult offsets get progressively expensive. The database must still scan and discard all skipped rows, so page 10,000 costs roughly as much as reading 10,000 pages worth of rows. If you control the client contract, keyset ("seek") pagination — filtering with WHERE id > :lastSeenId ORDER BY id instead of an offset — keeps every page O(page size) and is naturally stable against inserts before the cursor.
Also, if you ever migrate this to the JPA Criteria API with Join.fetch() plus setFirstResult/setMaxResults, Hibernate logs HHH000104 and silently paginates in memory after fetching everything — the SQL limit disappears entirely. Enable SQL logging and check for that warning; it's the easiest way to confirm your limit clause actually made it into the generated statement.