Hibernate Pagination with setFirstResult/setMaxResults: How It Works and Where It Breaks

The short answer

To paginate a JPQL or Criteria query in Hibernate, call setFirstResult(offset) and setMaxResults(pageSize) on the query before executing it. Hibernate translates those two numbers into the correct SQL for your database dialect — LIMIT/OFFSET on PostgreSQL and MySQL, OFFSET ... FETCH on SQL Server and Oracle 12c+, and so on. You do not write pagination SQL yourself.

Two things the API does not give you, and both bite teams in production: it does not return a total row count, and it silently stops working in SQL when your query uses a collection fetch join. The rest of this article covers the mechanism, a worked example, and the two failure modes with their fixes.

A worked example

The following runs inside a service or repository method with an injected EntityManager (JPA or Hibernate Session both expose the same methods). No special permissions are needed beyond normal database read access.

int page = 2;      // zero-based page index
int size = 20;

TypedQuery<Post> q = em.createQuery(
    "select p from Post p order by p.id", Post.class);
q.setFirstResult(page * size);   // offset: skip 40 rows
q.setMaxResults(size);           // limit: return at most 20
List<Post> posts = q.getResultList();

The order by p.id is not optional decoration. Without a deterministic ordering — ideally on a unique, indexed column — the database is free to return rows in any order, and rows can shift between pages as data changes. Users then see duplicates or missing items while paging.

To verify Hibernate is really pushing pagination into SQL, enable SQL logging (hibernate.show_sql=true, or set the logger org.hibernate.SQL to DEBUG) and confirm the generated statement contains limit ? offset ? (or your dialect's equivalent) rather than a bare select. The exact SQL is dialect- and version-sensitive, so check against your actual Hibernate version and database.

The Criteria API behaves identically:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Post> cq = cb.createQuery(Post.class);
Root<Post> root = cq.from(Post.class);
cq.select(root).orderBy(cb.asc(root.get("id")));
TypedQuery<Post> q = em.createQuery(cq);
q.setFirstResult(page * size);
q.setMaxResults(size);

Getting the total count

Pagination returns one page of rows, nothing more. If your UI needs "page 3 of 47", you need a separate count query with the same filters but without ordering or fetch joins:

TypedQuery<Long> countQ = em.createQuery(
    "select count(p) from Post p where p.published = true", Long.class);
long total = countQ.getSingleResult();
long totalPages = (total + size - 1) / size;

Keep the where clause identical to the page query or the count will disagree with the data. For very large tables where an exact count is expensive, some teams skip it and offer only "next/previous" navigation — which also pairs naturally with keyset pagination, covered below.

The collection fetch join trap

This is the most common Hibernate pagination bug. Consider:

TypedQuery<Post> q = em.createQuery(
    "select p from Post p join fetch p.comments order by p.id", Post.class);
q.setFirstResult(0);
q.setMaxResults(20);

A SQL LIMIT 20 would limit joined rows, not posts — a post with 50 comments spans 50 rows. Hibernate knows this, so it cannot paginate in SQL. Historically it falls back to fetching all matching rows into memory and applying the offset/limit there, logging:

HHH000104: firstResult/maxResults specified with collection fetch; applying in memory

On a small dev dataset this looks fine. In production it can load hundreds of thousands of rows per request. Watch your logs for that warning; treat it as a bug, not noise.

Safe alternatives, in rough order of preference:

1. Two queries. Paginate a parent-ID query first, then fetch collections for just those IDs:

   List<Long> ids = em.createQuery(
       "select p.id from Post p order by p.id", Long.class)
       .setFirstResult(page * size).setMaxResults(size)
       .getResultList();
   List<Post> posts = em.createQuery(
       "select distinct p from Post p left join fetch p.comments "
       + "where p.id in :ids order by p.id", Post.class)
       .setParameter("ids", ids)
       .getResultList();

   The second query has no pagination, so the fetch join is safe.
2. Drop the fetch join. Keep the collection lazy and tune with @BatchSize (e.g., @BatchSize(size = 20) on the collection) so Hibernate loads comments for the page's posts in a few batched queries instead of one per post.
3. Query from the child side when the use case allows it — paginating a single entity with only to-one joins paginates correctly in SQL.

Deep offsets and keyset pagination

Offset pagination has an asymptotic cost: to serve page 10,000 of size 20, the database must scan and discard 200,000 rows. Response time grows linearly with page number. If you expose deep paging on a large table, measure query time at a high offset before shipping.

The practical alternative is keyset (seek) pagination: instead of an offset, pass the last seen sort key and filter on it:

TypedQuery<Post> q = em.createQuery(
    "select p from Post p where p.id > :lastSeenId order by p.id", Post.class);
q.setParameter("lastSeenId", lastSeenId);
q.setMaxResults(size);   // no setFirstResult

This uses the index on id and costs the same for page 1 as for page 10,000. The trade-offs: it only supports forward/backward navigation (no "jump to page 37"), the sort key must be unique and indexed (add a tiebreaker column like order by p.createdAt, p.id with a matching compound predicate otherwise), and it is immune to rows shifting between requests — which offset pagination is not.

Common mistakes checklist

• Paginating without a unique ORDER BY — duplicates and gaps across pages.
• Combining setMaxResults with a collection join fetch — silent in-memory pagination; check for HHH000104.
• Reusing the page query's fetch joins or ordering in the count query — wrong counts or unnecessary work.
• Assuming the generated SQL is identical across databases — verify with SQL logging per environment.
• Offering deep offset paging on large tables without measuring it.

Verifying the result

A quick integration test (H2 or Testcontainers against your real database) settles most doubts: insert 25 known rows, request page 2 with size 10, and assert the exact expected IDs in order. Add a second test with a fetch join and assert the HHH000104 warning does not appear after refactoring to the two-query pattern. Combined with SQL logging showing limit/offset in the generated statement, that confirms pagination happens in the database, deterministically, at the cost you expect.