Diagnosing Hibernate Pagination Defects with setFirstResult and setMaxResults

Recognizable Condition

You page through a result set using Hibernate's setFirstResult and setMaxResults, but the application returns the wrong rows: the first record is missing, the same entity appears on two pages, or deep pages respond slowly. These symptoms usually trace back to how the object-relational mapper (ORM) translates pagination into SQL LIMIT/OFFSET clauses and how the persistence context caches entities between queries.

Cause and Diagnostic Table

Symptom	Likely Cause	Diagnostic Check
First row never appears	setFirstResult(1) used instead of 0	Inspect the generated SQL for the offset value
Rows shift or duplicate between pages	Missing or non-deterministic ORDER BY	Execute the same page twice and compare primary keys
Deep pages take seconds to return	Database scans and discards all rows before the offset	Compare response time for page 1 versus page 1000
Updated database values not reflected in entities	Stale first-level cache in a container-managed persistence context	Call clear() or refresh() and re-query

Ordered Checks

1. Confirm the offset is zero-based. setFirstResult accepts a zero-based index. Passing 1 skips the first row. Verify the argument in your repository or data-access layer.
2. Verify deterministic ordering. Pagination without a total order is unstable. Check that your Hibernate Query Language (HQL) or Criteria query includes an ORDER BY on a unique column, typically the primary key.
3. Inspect the generated SQL. Enable Hibernate SQL logging to confirm the LIMIT and OFFSET values match your intent. In a persistence.xml or hibernate.cfg.xml, set show_sql=true. For Hibernate 6, the equivalent property is hibernate.show_sql=true.
4. Measure offset depth. Run a test query with setFirstResult(0) and setMaxResults(10), then increase the offset incrementally. Note where latency climbs sharply.
5. Check persistence context state. In container-managed environments (for example, EJB or CDI beans with an extended persistence context), entities may remain cached. Compare an entity's version field or last-updated timestamp after calling clear().

Fixes Tied to Findings

Offset off by one

If the first record is missing, change the first page call to setFirstResult(0). For page n with page size s, the offset is (n - 1) * s.

Unstable result order

Add an explicit unique sort key. The following snippet shows a stable page query in Hibernate 6:

Query<Account> query = session.createQuery(
    "FROM Account ORDER BY id", Account.class);
query.setFirstResult(0);   // zero-based
query.setMaxResults(10);
List<Account> page = query.getResultList();

Ordering by a non-unique column such as lastName can still produce ties that reorder between queries.

Large-offset latency

When offsets exceed a few thousand rows, the database must read and discard rows before returning the page. If your table is append-mostly and sorted by an immutable key, switch to keyset (seek) pagination:

Query<Account> query = session.createQuery(
    "FROM Account WHERE id > :lastId ORDER BY id", Account.class);
query.setParameter("lastId", previousPageLastId);
query.setMaxResults(10);
List<Account> page = query.getResultList();

This avoids OFFSET entirely, but it requires a stable, gap-free sort key and does not support arbitrary page jumps.

Stale persistence context

If another transaction modified rows after your persistence context loaded them, call session.clear() before the pagination query, or use session.refresh(entity) on individual results. In a stateless session, this is unnecessary because no first-level cache is maintained.

Database-Specific Considerations

Not every database optimizes LIMIT/OFFSET identically. MySQL with the InnoDB engine, for example, may handle large offsets differently than PostgreSQL or SQL Server. The Hibernate-generated SQL is standard, but the execution plan is not. If deep-page latency appears only on one environment, capture the execution plan for the exact SQL emitted by Hibernate rather than assuming the ORM is the bottleneck.

Escalation Criteria

• Keyset pagination is impossible because the business requirement demands random page access and the offset is consistently above 10,000 rows.
• The execution plan shows the database is not using an index for the ORDER BY column, and a DBA must add or tune the index.
• Second-level cache staleness is suspected after clear() and refresh() fail to resolve the discrepancy.
• The same query behaves differently across database vendors and the application must support multiple Hibernate dialects.

Limitations and Verification

These diagnostics assume Hibernate ORM 5.6 or 6.x with a relational database that supports LIMIT/OFFSET syntax. Hibernate dialects that lack native pagination support may emulate it with subqueries or window functions, changing the performance profile.

To verify a fix, enable show_sql=true, run the first page with setFirstResult(0) and setMaxResults(10), and compare the returned row count and primary keys against a direct SQL count. If the counts match and ordering is stable across repeated runs, the pagination path is behaving as intended.