Hibernate Pagination: Transitioning from Offset-based to Keyset-based Retrieval

The short answer

You can integrate keyset pagination into a generic repository by parameterizing three things — the entity's sort-key attribute, the last-seen key value, and the page size — and building the WHERE key > :lastValue ORDER BY key predicate dynamically with the JPA Criteria API and metamodel. And yes, keyset pagination is more consistent than offset under frequent inserts: rows inserted before your current position never shift the pages that follow, whereas with OFFSET every insert renumbers all subsequent positions.

What is confirmed vs. assumed

Confirmed, well-established behavior: setFirstResult() generates SQL OFFSET, whose cost grows linearly with page depth because the database scans and discards preceding rows. A keyset predicate on a unique, indexed column gives roughly constant query time regardless of depth. Standard Hibernate Query and JPA Criteria APIs have no native seek abstraction; you express it manually as an extra predicate plus ORDER BY, or adopt a library such as Blaze-Persistence that adds keyset support on top of JPA.

Assumed for the code below: Hibernate 6.x (Jakarta Persistence namespace), a single unique numeric sort key, and forward-only (next-page) navigation. Verify tuple-comparison support against your actual ORM and database versions before relying on composite keys — support is version-sensitive.

A generic repository method

public <T, K extends Comparable<? super K>> List<T> nextPage(\n        Class<T> entityClass,\n        SingularAttribute<T, K> keyAttribute,\n        K lastSeenKey,          // null for the first page\n        int pageSize) {\n\n    CriteriaBuilder cb = em.getCriteriaBuilder();\n    CriteriaQuery<T> query = cb.createQuery(entityClass);\n    Root<T> root = query.from(entityClass);\n\n    if (lastSeenKey != null) {\n        query.where(cb.greaterThan(root.get(keyAttribute), lastSeenKey));\n    }\n    query.orderBy(cb.asc(root.get(keyAttribute)));\n\n    return em.createQuery(query)\n             .setMaxResults(pageSize)\n             .getResultList();\n}

Callers pass the metamodel attribute (Order_.id) and the key of the last row from the previous page. No per-entity query duplication — one method serves every entity that has a unique indexed key. The client contract changes from \"page number\" to \"last seen key,\" which fits infinite scroll or next/previous navigation.

Non-unique sort columns need a tiebreaker

If you sort by something non-unique like createdAt, a plain createdAt > :last predicate silently skips or duplicates boundary rows. Add the primary key as a tiebreaker and use row-value logic:

// Conceptually: (createdAt, id) > (:lastCreatedAt, :lastId)\n// Portable expanded form:\nquery.where(cb.or(\n    cb.greaterThan(root.get(Order_.createdAt), lastCreatedAt),\n    cb.and(cb.equal(root.get(Order_.createdAt), lastCreatedAt),\n           cb.greaterThan(root.get(Order_.id), lastId))\n));\nquery.orderBy(cb.asc(root.get(Order_.createdAt)), cb.asc(root.get(Order_.id)));

The expanded AND/OR form is more portable than tuple syntax (a,b) > (?,?), whose support varies by database and Hibernate version. Mark this for review against your specific stack.

Consistency under frequent inserts

This is where keyset wins decisively. With offset pagination, an insert before your current position shifts every subsequent row's position, so the next page repeats one row and a delete causes a skip. With keyset, the predicate is anchored to key values, not positions: rows inserted behind you are simply never revisited, and rows inserted ahead of you appear naturally when you reach them. Sequential pages show no duplicates and no gaps caused by concurrent writes.

The trade-offs to accept: no random access (you cannot jump to page 4,000), no cheap total-page count (a separate COUNT query is needed if you still display one), and previous-page navigation requires reversing both the comparison operator and the sort direction, then reversing the result list in memory.

Verification before rollout

1. Enable SQL logging (hibernate.show_sql or a datasource proxy) and confirm generated queries contain the key predicate and no OFFSET clause.
2. Compare EXPLAIN plans and latency at shallow vs. deep positions (page 2 vs. page 10,000) on production-like volume; the keyset plan should use an index range scan with flat cost.
3. Write an integration test that inserts rows mid-iteration and asserts sequential keyset pages contain no duplicates or gaps.
4. Test the non-unique-column tiebreaker path with rows sharing identical createdAt values at page boundaries.