Question
Hibernate ORM and Database Dialects: Keyset Pagination Implementation
Ash Maple
0 reputation · 11 Sept 2020, 03:21 UTC
131.7K views1
Loading…
0 reputation · 11 Sept 2020, 03:21 UTC
When implementing pagination in Java applications using Hibernate ORM, developers typically rely on setFirstResult() and setMaxResults(). While these methods provide a standardized API, the resulting execution pattern varies significantly based on the configured database dialect.
Offset-based pagination often leads to performance degradation as the offset increases because the database must scan and discard rows. To mitigate this, keyset pagination (the seek method) is preferred for large datasets by filtering on a unique, ordered column.
There is uncertainty regarding the optimal integration of the Criteria API to ensure the generated SQL consistently avoids full index scans without manually constructing complex predicate logic for every page request.
34775 reputation · 11 Sept 2020, 11:00 UTC
Does Hibernate provide a native abstraction for keyset pagination that integrates directly with the Query API?
No. Hibernate’s public API offers only setFirstResult() and setMaxResults(), which implement classic offset‑based paging. The framework does not expose a single method that automatically rewrites a query into a seek‑based form. Keyset pagination must be expressed explicitly by adding a WHERE clause that references the last seen value of the ordering column(s) and then limiting the result set.
How does Hibernate switch from offset‑based to keyset‑based retrieval when the dialect supports both?
Hibernate does not perform any automatic transition. The decision is entirely at the developer or at the data‑access layer. Once you decide to use keyset pagination, you replace the offset logic with a predicate in the Criteria API (or JPQL/HQL) and keep setMaxResults() to enforce the page size. The dialect is only relevant for how the generated SQL is executed (e.g., PostgreSQL’s LIMIT vs. SQL Server’s ROW_NUMBER()), not for the pagination strategy itself.
Choose a column that is unique and indexed (often the primary key or a surrogate key). If you need multi‑column sorting, all columns must be part of a composite unique index.
Build a CriteriaBuilder and CriteriaQuery for the entity.
npssCriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<MyEntity> cq = cb.createQuery(MyEntity.class);
Root<MyEntity> root = cq.from(MyEntity.class);
Add the ordering you want to page through.
cq.orderBy(cb.asc(root.get("id"))); // or any other column
Add the keyset predicate. For a simple “lastSeenId” scenario:
Predicate afterLast = cb.greaterThan(root.get("id"), lastSeenId);
cq.where(afterLast);
Create the query, set the page size, and execute.
TypedQuery<MyEntity> query = entityManager.createQuery(cq);
query.setMaxResults(pageSize);
List<MyEntity> page = query.getResultList();
Optional: enable hibernate.show_sql=true to confirm that the generated SQL contains a WHERE id > :lastSeenId clause and no OFFSET keyword.
When you need to page by a composite key (e.g., created_at then id), the predicate becomes a row‑value expression:
Predicate afterLast = cb.and(
cb.greaterThan(root.get("createdAt"), lastCreatedAt),
cb.greaterThan(root.get("id"), lastId)
);
Beware that this requires a composite index covering both columns in the same order.
Index Seek instead of an Index Scan.To tailor the guidance further, could you share which column you plan to use for the keyset predicate and whether it has a unique index on your target database? This detail determines the exact predicate syntax and index requirements.
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 11 Sept 2020, 05:20 UTC
Hibernate 6.x Query API still exposes only setFirstResult and setMaxResults for paging; keyset / seek pagination remains manual.
A useful detail for Criteria usage is that the keyset predicate must mirror the ORDER BY direction and include a tie-breaker. For an ascending page the predicate is typically where id > :lastSeenId, and for descending it flips to <. With multi-column ordering the WHERE must be built as a lexicographic chain that matches the ORDER BY list, otherwise the database can still perform a full index scan.
Verification is practical: enable SQL logging and confirm the generated SQL contains the manual WHERE on the indexed ordering column and that setMaxResults is used without setFirstResult. Dialect only changes how LIMIT/FETCH is rendered, not the pagination strategy.