Hibernate Pagination with setFirstResult and setMaxResults: A Practical Guide

If you need to page through a large table in Hibernate without loading everything into memory, the two JPA methods setFirstResult(int) and setMaxResults(int) are the supported way to do it. setFirstResult sets a zero-based offset (how many rows to skip), and setMaxResults caps how many rows come back. Hibernate translates them into the database's native paging syntax (LIMIT/OFFSET on PostgreSQL and MySQL, OFFSET ... FETCH or ROWNUM variants elsewhere), so the trimming happens in the database, not in your JVM.

A worked example

Assume an Order entity and a request for page 3 with a page size of 20. In a repository or service method with an injected EntityManager:

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

List<Order> orders = em.createQuery(
        "SELECT o FROM Order o ORDER BY o.id", Order.class)
    .setFirstResult(page * size)   // offset = 60
    .setMaxResults(size)           // at most 20 rows
    .getResultList();

Two details matter here. First, always include a deterministic ORDER BY. Without it, the database is free to return rows in any physical order, and page boundaries can shift between requests, producing duplicates or skipped rows across pages. Ordering by the primary key is the simplest stable choice. Second, the offset is page * size only if your page numbers are zero-based; decide the convention once and document it in your API.

If you use Spring Data JPA, you normally don't call these methods directly. A Pageable such as PageRequest.of(3, 20, Sort.by("id")) passed to a repository method is implemented on top of the same offset/limit mechanism, plus an automatic count query.

Getting the total count

Offset pagination usually needs a total row count so the UI can render "page 4 of 37". Run it as a separate query with the same filters but no pagination:

Long total = em.createQuery(
        "SELECT COUNT(o) FROM Order o", Long.class)
    .getSingleResult();

Do not try to reuse the paginated query for this. Applying setMaxResults to a query containing aggregate functions such as COUNT limits the number of aggregate rows returned, not the rows being counted, which yields confusing or incorrect results depending on the query shape. Keep the count query and the page query independent, and keep their WHERE clauses identical.

The large-offset problem

Offset pagination has a real cost that grows with the offset. When you ask for setFirstResult(100000), the database must still locate and scan past those 100,000 rows before it can return your 20. The skipped rows are read and discarded, so deep pages get slower linearly. On small or medium tables this is fine; on tables where users routinely jump to page 5,000, it becomes a measurable load.

The standard alternative is keyset ("seek") pagination: instead of skipping rows, you remember the last sort key from the previous page and filter on it:

List<Order> nextPage = em.createQuery(
        "SELECT o FROM Order o WHERE o.id > :lastId ORDER BY o.id",
        Order.class)
    .setParameter("lastId", lastSeenId)
    .setMaxResults(size)
    .getResultList();

With an index on the sort column, this runs in roughly constant time regardless of how deep into the table you are. The trade-offs: you lose the ability to jump to an arbitrary page number, and the sort key must be unique (or you must add a tiebreaker column). A common pattern is offset pagination for the first few pages of an admin UI and keyset pagination for infinite-scroll feeds or batch exports.

Common mistakes

• Missing ORDER BY. Page contents become nondeterministic. This is the most frequent real-world bug with offset pagination.
• One-based offsets. setFirstResult is zero-based. Passing 20 for the first page of size 20 silently skips the first page.
• Counting with the paginated query. As noted above, keep the count separate.
• Paginating a join fetch of a collection. With a query like SELECT o FROM Order o JOIN FETCH o.items, Hibernate cannot apply the limit in SQL without truncating the collection, so it falls back to fetching everything and paginating in memory, typically logging a warning like HHH000104: firstResult/maxResults specified with collection fetch; applying in memory. That defeats the purpose. Paginate on the root entity by ID first, then fetch collections for just those IDs in a second query.
• Assuming stable pages under concurrent writes. Rows inserted or deleted between page requests shift the window. Keyset pagination tolerates this better because the filter is anchored to a value, not a position.

Verifying the behavior

A quick unit or integration test against a known dataset goes a long way. Seed, say, 12 rows, then:

1. Run the query with setFirstResult(0).setMaxResults(5) and assert the list size is 5 and the first element is the expected entity.
2. Run with setFirstResult(10).setMaxResults(5) and assert exactly 2 rows come back — this confirms offset behavior at the tail.
3. Enable SQL logging (hibernate.show_sql=true or your logging framework's org.hibernate.SQL category) and confirm the generated statement actually contains a limit/offset clause rather than a full select.

To see the deep-offset cost yourself, run a query with setFirstResult(100000) against a realistically sized test table, note the execution time, and compare it with the equivalent keyset query. The difference is usually obvious enough to settle any design debate.

Limitations to keep in mind

The exact SQL generated varies by Hibernate dialect and database version, so behavior described here assumes a reasonably current Hibernate 5/6 with a matching dialect — verify against your own setup. Offset pagination also does not compose well with queries whose row count changes mid-navigation, and neither approach fixes a missing index on the sort column. For most CRUD-style applications, setFirstResult/setMaxResults with a stable sort and a separate count query is correct and simple; reach for keyset pagination when offsets get deep or the data is high-volume and append-heavy.