Hibernate Pagination: Offset Pages, Keyset Pages, and Verifying a Wrapper

Two method calls give you a page in Hibernate ORM: setFirstResult(int) for the offset and setMaxResults(int) for the page size. The useful engineering decision is not how to call them — it is when to stop using them. Offset pages drift and slow down as the offset grows; a keyset query keyed on a unique, ordered column does neither. This guide covers both, plus how to check that a pagination wrapper sitting in front of Hibernate is actually delegating to those methods.

Scope: Hibernate ORM, not OS hibernation

"Hibernate" here means the Java persistence library (Hibernate ORM, the reference implementation of Jakarta Persistence). It has nothing to do with suspending a machine to disk. If your project calls its pagination helper "Rider", that name does not identify a public API — treat it as a local wrapper and verify it, as described below.

Prerequisites

• A Hibernate ORM project. The examples use Hibernate 6.x with Jakarta Persistence (jakarta.persistence). Hibernate 5.x exposes the same two methods under javax.persistence, so only imports change.
• Read access to the table you are paging. Pagination itself needs no write permission.
• An entity with a unique, indexed, monotonically increasing column — a generated id is the usual choice. Keyset pagination depends on this.
• For the checks: a way to see generated SQL, such as the org.hibernate.SQL logger at DEBUG in a development profile. Do not leave SQL logging on in production; it is noisy and can write parameter values into logs.

Offset pagination with setFirstResult and setMaxResults

// Hibernate 6.x, jakarta.persistence
List<Ticket> page = session
    .createQuery("from Ticket t order by t.id asc", Ticket.class)
    .setFirstResult(offset)   // rows to skip
    .setMaxResults(10)        // rows to return
    .getResultList();

Hibernate translates this into dialect-specific SQL. Depending on the dialect you will see something shaped like offset ? rows fetch first ? rows only or limit ? offset ?. Read the actual statement from your SQL log rather than assuming a form.

An order by clause is not optional. Without a deterministic sort, the database is free to return rows in any order, and page 2 may repeat or skip rows from page 1 even when nothing is being written concurrently.

Two failure modes matter in production:

• Drift. Offset pagination counts rows at query time. If rows are inserted or deleted between requests, the same offset points at different rows, so a client walking pages can see duplicates or miss records.
• Cost. A large offset usually forces the database to produce and discard the skipped rows. Deep pages get progressively slower.

Keyset pagination when offsets drift or get slow

Keyset (cursor) pagination replaces the offset with a predicate on the last row you saw. The client sends back the sort key of the final row, not a page number.

// first page
List<Ticket> first = session
    .createQuery("from Ticket t where t.id > :lastId order by t.id asc", Ticket.class)
    .setParameter("lastId", 0L)
    .setMaxResults(10)
    .getResultList();

// next page: lastId is the id of the last row returned above
List<Ticket> next = session
    .createQuery("from Ticket t where t.id > :lastId order by t.id asc", Ticket.class)
    .setParameter("lastId", lastId)
    .setMaxResults(10)
    .getResultList();

Because the predicate is on a value rather than a row count, inserts elsewhere in the table do not shift the window. The database can also seek straight to id > :lastId using the index instead of scanning past the skipped rows.

If you need to sort by a non-unique column such as createdAt, add a unique tie-breaker, otherwise rows sharing a timestamp can be skipped. A portable form is:

where t.createdAt > :ts
   or (t.createdAt = :ts and t.id > :id)
order by t.createdAt asc, t.id asc

Row-value comparisons such as (createdAt, id) > (:ts, :id) are supported by some databases and not others. The expanded form above works broadly but may use the index less efficiently. Check the execution plan on your database before choosing.

Expected checks

1. Snapshot check. Run the query with setFirstResult(0) and setMaxResults(10), then compare the returned rows, in order, against a known snapshot of the table. This confirms the ordering is what you think it is.
2. Offset check. Run the same query with setFirstResult(100) and confirm the result is exactly the next ten rows — rows 101–110 of the ordered set, with no repeats from earlier pages.
3. SQL check. With the SQL logger on, confirm the emitted statement contains the offset/fetch (or limit/offset) clause and the order by you intended. This is also the fastest way to catch a wrapper that builds its own SQL.
4. Keyset check. Take the last id of page 1, request page 2 with where id > :lastId, and confirm the first row of page 2 is the immediate successor in the ordered set. Then insert a row with a lower id from another session and repeat: the keyset page should be unchanged, while an offset page may not be.

Do not treat a passing row count as proof. Ten rows returned is not the same as the correct ten rows; compare identifiers.

If a wrapper named "Rider" sits in front

If your codebase has a pagination helper called Rider (or any other local name), the name alone tells you nothing about behavior. Check three things in the wrapper's source:

• Does it call setFirstResult and setMaxResults, or assemble SQL itself? A wrapper that builds its own limit/offset strings can diverge from ORM behavior, especially across dialects.
• Does it enforce an order by? A wrapper that accepts a page number without a deterministic sort inherits the drift problem.
• Does it translate a cursor into a predicate, or silently convert it back into an offset? A cursor API backed by offsets gives you the cost of offsets without the stability of keyset.

Verify by logging SQL through the wrapper and comparing both the statement and the page contents against a direct Hibernate query. Treat any wrapper behavior you cannot confirm from source or logs as unverified.

Limitations and recovery

Keyset pagination cannot jump to an arbitrary page number, because there is no cheap way to compute "row 500" without counting. If your UI needs numbered pages, keep offset pagination and accept drift, or maintain a separate count. Keyset also requires a stable sort key; tables without a unique ordered column need one added first.

Pagination queries are read-only, so there is nothing to roll back. If you add an index to support the keyset predicate or ordering, that is a schema change: record the exact CREATE INDEX statement, keep the matching DROP INDEX as the rollback, and confirm the index is actually used via your database's execution plan rather than assuming it.

Marked for review: some Hibernate 6.x releases added built-in key-based pagination helpers. If you want to use those instead of the explicit predicate shown above, confirm the API and behavior against the documentation for the exact Hibernate version in your build. The manual form here works on any version that supports setMaxResults.