Server-Side Pagination in Hibernate with setFirstResult and setMaxResults

The useful answer first

If you need paged results in Hibernate, do not load the full result set and slice it in Java. Call setFirstResult(offset) and setMaxResults(pageSize) on your query. Hibernate translates these into dialect-specific SQL — LIMIT/OFFSET on PostgreSQL and MySQL, OFFSET ... FETCH FIRST on Oracle and SQL Server — so the database does the paging and only the requested rows cross the network. Pair this with a deterministic ORDER BY, or pages will overlap and skip rows.

The examples below assume Hibernate 5.x/6.x (Jakarta Persistence in 6.x; use javax.persistence imports on 5.x). Verify against your version before shipping.

How the mechanism works

Both JPA query styles support pagination:

• setFirstResult(int) — the zero-based index of the first row to return. Page 2 with a page size of 20 starts at index 20.
• setMaxResults(int) — the maximum number of rows returned, i.e. the page size.

Hibernate's dialect rewrites these calls into the target database's paging syntax at SQL generation time. Because paging happens in the database, memory use in the application stays proportional to the page size, not the table size.

Worked example: Criteria API with a stable sort

The Criteria API gives you type-safe pagination without string-concatenated JPQL. This example runs inside a service or repository method with an injected EntityManager (no special permissions needed beyond normal datasource access):

public List<Order> findPage(int page, int pageSize) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Order> cq = cb.createQuery(Order.class);
    Root<Order> root = cq.from(Order.class);

    cq.select(root)
      .orderBy(cb.asc(root.get("createdAt")),
               cb.asc(root.get("id"))); // tiebreaker for stability

    return entityManager.createQuery(cq)
        .setFirstResult(page * pageSize)
        .setMaxResults(pageSize)
        .getResultList();
}

The equivalent JPQL form is shorter and paginates identically:

List<Order> page = entityManager.createQuery(
        "SELECT o FROM Order o ORDER BY o.createdAt ASC, o.id ASC", Order.class)
    .setFirstResult(page * pageSize)
    .setMaxResults(pageSize)
    .getResultList();

Note the two-part sort. createdAt alone is not unique; two rows with the same timestamp can swap positions between queries, causing the same row to appear on two pages or vanish entirely. Appending the primary key as a tiebreaker makes the order total and stable.

Verifying that paging happens in the database

Do not assume the SQL is paged — check it. Enable SQL logging in application.properties (or equivalent):

hibernate.show_sql=true
# or, with Spring Boot:
spring.jpa.show-sql=true

Run the query with page=1, pageSize=20 and confirm two things in the log output: the generated SQL contains limit ? offset ? (PostgreSQL) or your dialect's equivalent, and the returned list contains exactly rows 21–40. If you see the full table selected with no paging clause, something (usually a collection fetch join, covered below) has forced in-memory paging.

Limits and common mistakes

Collection fetch joins break database paging

If your query uses JOIN FETCH on a @OneToMany collection, Hibernate cannot page at the SQL level — one parent row spans multiple result rows, so row-based LIMIT would cut parents in half. Hibernate falls back to fetching everything and paging in memory, logging:

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

This can load millions of rows. The standard fix is a two-step query: page the parent IDs first, then fetch the collections for just those IDs:

List<Long> ids = entityManager.createQuery(
        "SELECT o.id FROM Order o ORDER BY o.createdAt, o.id", Long.class)
    .setFirstResult(page * pageSize).setMaxResults(pageSize)
    .getResultList();

List<Order> orders = entityManager.createQuery(
        "SELECT DISTINCT o FROM Order o LEFT JOIN FETCH o.lines " +
        "WHERE o.id IN :ids ORDER BY o.createdAt, o.id", Order.class)
    .setParameter("ids", ids)
    .getResultList();

Alternatively, drop the fetch join and rely on @BatchSize or an entity graph to load the collections in a secondary query.

Deep paging degrades

OFFSET 1000000 still makes the database scan and discard a million rows. For very deep navigation, use keyset ("seek") pagination instead: filter on WHERE id > :lastSeenId ORDER BY id with setMaxResults(pageSize). This uses the index and stays fast at any depth, at the cost of not supporting arbitrary page jumps.

Other traps

• No ORDER BY: the database is free to return rows in any order per query, so page boundaries are meaningless. Always sort, and make the sort total with a unique tiebreaker.
• setMaxResults(Integer.MAX_VALUE) as "no limit": it works, but if the table grows you have an unbounded fetch with a false sense of safety. Omit the call instead.
• Negative or overflowing offsets: compute page * pageSize as a long and validate bounds before casting to int for large datasets.
• Count queries: to render "page 3 of 42", run a separate SELECT COUNT(o) with the same filters (without the fetch join). Do not derive the total from a full fetch.

Practical checklist

1. Add a total ORDER BY (business key + primary key tiebreaker).
2. Apply setFirstResult(page * pageSize) and setMaxResults(pageSize).
3. Enable SQL logging and confirm the dialect paging clause appears.
4. Watch the logs for HHH000104; if it appears, switch to the two-step ID-then-fetch pattern.
5. For deep offsets, evaluate keyset pagination.

These behaviors are long-established across Hibernate 5 and 6, but confirm the exact SQL and warning codes against your version and dialect before relying on them in production.