Hibernate Offset-Limit Pagination: How setFirstResult and setMaxResults Work and When to Avoid Them

The Short Answer

Use setFirstResult(offset) and setMaxResults(pageSize) on a JPA or Hibernate Query to fetch a single page of results. Hibernate translates these calls into dialect-specific LIMIT/OFFSET (or equivalent) clauses. The query must include an ORDER BY; without it, the database can return rows in physical storage order, causing skipped or duplicated rows across pages. For deep pagination (large offsets), prefer keyset pagination instead.

Worked Example: Basic Page Request

The following snippet requests the third page of ten employees, ordered by primary key:

// JPA EntityManager (Hibernate 6+ uses org.hibernate.query.Query under the hood)
List<Employee> page3 = entityManager.createQuery(
    "select e from Employee e order by e.id", Employee.class)
    .setFirstResult(20)   // skip first 20 rows (pages 1 and 2)
    .setMaxResults(10)    // take the next 10 rows
    .getResultList();

With a PostgreSQL dialect this produces SQL similar to:

select e.id, e.name, e.dept_id
from employee e
order by e.id
limit 10 offset 20;

On SQL Server the same call yields OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; on Oracle 12c+ it becomes OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY as well. The dialect handles the translation automatically.

Counting Total Pages Requires a Separate Query
Hibernate does not compute the total row count. You must run a second query that mirrors the WHERE and JOIN clauses but omits ORDER BY and any projection that would change cardinality:
Long total = entityManager.createQuery(
    "select count(e) from Employee e where e.active = true", Long.class)
    .getSingleResult();
Then totalPages = (total + pageSize - 1) / pageSize. Keep the count query in sync with the data query; a missing predicate in the count query is a common source of off-by-one pagination bugs.

Why Large Offsets Get Slow
Offset pagination forces the database to scan and discard offset rows before returning the requested page. With an index on the ordering column the scan is an index range scan, but the engine still visits every skipped entry. At offset 1,000,000 the database does roughly one million index lookups before emitting page 100,001.
Keyset pagination (also called seek pagination) avoids this by remembering the last seen ordering value and adding a WHERE predicate:
// Page 1
List<Employee> page1 = em.createQuery(
    "select e from Employee e where e.active = true order by e.id", Employee.class)
    .setMaxResults(10)
    .getResultList();

// Page 2 — use the last id from page1
Long lastId = page1.get(page1.size() - 1).getId();
List<Employee> page2 = em.createQuery(
    "select e from Employee e where e.active = true and e.id > :lastId order by e.id", Employee.class)
    .setParameter("lastId", lastId)
    .setMaxResults(10)
    .getResultList();
The database seeks directly to lastId via the index, making deep pages as fast as the first page. The trade-off: you lose random-access page numbers (no "jump to page 47") and the ordering column must be unique and immutable (or you must add a tie-breaker).

Fetch Joins and setMaxResults — A Version-Sensitive Trap
When a query uses JOIN FETCH to eagerly load a collection, Hibernate 5 often executed a single query with a Cartesian product and then applied LIMIT in memory, returning fewer root entities than requested. Hibernate 6 changed this behavior: depending on the dialect it may split the fetch into multiple queries or apply a dialect-specific window function. The generated SQL can differ dramatically between PostgreSQL, MySQL, and Oracle.
Example that triggers the issue:
// Risky: fetch join with pagination
List<Department> depts = em.createQuery(
    "select distinct d from Department d join fetch d.employees order by d.id", Department.class)
    .setFirstResult(0)
    .setMaxResults(5)
    .getResultList();
Safe pattern: paginate the root entity first, then initialize collections in a second query or via batch fetching:
// Step 1: paginate roots only
List<Department> roots = em.createQuery(
    "select d from Department d order by d.id", Department.class)
    .setFirstResult(0)
    .setMaxResults(5)
    .getResultList();

// Step 2: fetch collections for the selected roots
List<Long> ids = roots.stream().map(Department::getId).toList();
em.createQuery(
    "select d from Department d join fetch d.employees where d.id in :ids", Department.class)
    .setParameter("ids", ids)
    .getResultList(); // returns the same Department instances with employees initialized
Always enable SQL logging (logging.level.org.hibernate.SQL=DEBUG) and inspect the generated statements when you combine fetch joins with pagination.

Collections Are Not Limited by setMaxResults
setMaxResults limits the number of root entity rows returned. If you fetch a collection without a join fetch, each root entity will still load its entire collection when accessed. If you do use a join fetch, the Cartesian product multiplies rows before the limit is applied, so you may get fewer root entities than pageSize. The rule: paginate on the root entity, then load collections separately.

Common Mistakes Checklist

Missing ORDER BY — results are non-deterministic across pages.
Using offset for deep pages — switch to keyset pagination beyond a few thousand rows.
Count query drift — count query omits a filter present in the data query (or vice versa).
Fetch join with pagination — produces unexpected row counts or extra queries; test per dialect.
Assuming setMaxResults limits collections — it does not.
Copy-pasting Hibernate 5 examples into Hibernate 6 — the Query interface moved to org.hibernate.query.Query and fetch-join behavior changed.


How to Verify in Your Environment

Enable Hibernate SQL logging and run a paginated query. Confirm the generated SQL contains the expected LIMIT/OFFSET (or dialect equivalent) and the ORDER BY you specified.
Execute page N and page N+1. The last ordering value on page N should be immediately followed by the first ordering value on page N+1 under the chosen sort key.
Test against the actual production database dialect. Limit/offset syntax and fetch-join handling differ between PostgreSQL, MySQL, SQL Server, Oracle, and others.
Check your dependency tree: hibernate-core version determines whether you use javax.persistence.Query (Hibernate 5) or org.hibernate.query.Query (Hibernate 6+).


Limitations to Keep in Mind

Offset pagination does not scale to very large offsets; plan for keyset pagination early if the dataset grows.
Total count queries double the query load per page request; consider caching the count or using an approximate count for huge tables.
Distinct projections (select distinct e) combined with pagination can produce incorrect results on some dialects because the distinct is applied after the limit.
Stateless sessions and scrollable results are alternatives for batch processing but do not support setFirstResult/setMaxResults in the same way.


Quick Reference: API by Hibernate Version


Version
Query Type
Package
Key Methods


Hibernate 5 (JPA)
javax.persistence.Query
javax.persistence
setFirstResult, setMaxResults, getResultList
Hibernate 5 (Native)
org.hibernate.Query
org.hibernate
setFirstResult, setMaxResults, list
Hibernate 6+ (JPA)
jakarta.persistence.Query
jakarta.persistence
setFirstResult, setMaxResults, getResultList
Hibernate 6+ (Native)
org.hibernate.query.Query
org.hibernate.query
setFirstResult, setMaxResults, getResultList


Choose the API that matches your dependency coordinates; mixing JPA and native APIs in the same codebase works but adds cognitive overhead.