Choosing Hibernate offset pagination with setFirstResult and setMaxResults

Problem: paginate large result sets without loading the whole table

You need a stable, repeatable page of entities from a large table in a Java application using Hibernate. The practical question is whether to use ORM-level offset pagination with setFirstResult and setMaxResults, or to move to keyset pagination or native SQL paging. Offset pagination is convenient for shallow pages and works with HQL, JPQL and Criteria. It degrades with deep offsets and interacts badly with eager fetching and joins.

Decision context and constraints

Offset pagination assumes a stable ordering and a read-only view. Constraints that matter:

• Page depth: first pages are cheap, page 10,000 is expensive.
• Fetch plan: associations fetched eagerly or via join fetch change row multiplication.
• Dialect support: Hibernate translates setFirstResult/setMaxResults to database LIMIT/OFFSET for HQL/JPQL/Criteria. Native SQL bypasses this translation.
• Consistency: long-running queries can see inserts/deletes between pages.

Supported pagination options

Option	Where it works	Pros	Cons
HQL/JPQL/Criteria with setFirstResult/setMaxResults	Hibernate Session, ORM cache aware	Simple API, portable across dialects, integrates with filters and entity graph	Large offset forces DB to scan and discard rows; join fetch can multiply rows
Native SQL with manual LIMIT/OFFSET	Session.createNativeQuery	Full control over SQL	Hibernate pagination not applied; you must implement paging and mapping yourself
Keyset pagination	HQL/JPQL with WHERE on indexed sort column	Stable performance for deep pages, no offset scan	Requires a unique, monotonic sort key; harder for arbitrary page numbers

Trade-offs of setFirstResult/setMaxResults

setFirstResult(int) defines a zero-based offset of the first row to return. setMaxResults(int) caps the number of rows fetched. Hibernate applies these at the SQL generation layer for HQL/JPQL/Criteria.

Performance degrades when offset grows because the database still processes preceding rows. This is not a Hibernate bug; it is how LIMIT/OFFSET works.

Fetch plan interaction matters. Lazy loading can trigger additional queries per row after the page is materialized. Eager fetching with joins can cause row multiplication, so the number of database rows returned can exceed setMaxResults. Use entity graphs or DTO projections to control the shape.

hibernate.jdbc.fetch_size influences JDBC driver buffering, not the ORM pagination logic. It does not replace setMaxResults.

Concrete implementation pattern

Run in a Java application with a Hibernate SessionFactory and a read-only transaction. Requires permission to read the target entity and to enable SQL logging for verification.

// session is an open Hibernate Session, tx is active
int page = 0; // zero based
int pageSize = 50;

var q = session.createQuery("from Order o where o.status = :status order by o.id", Order.class);
q.setParameter("status", Status.PAID);
q.setFirstResult(page * pageSize);
q.setMaxResults(pageSize);

// optional: control fetch plan to avoid N+1
q.setHint("org.hibernate.fetchSize", pageSize);

List pageContent = q.list();

Risks: using a large page value creates a large offset. Mixing setMaxResults with join fetch on a collection can return fewer distinct entities than requested due to row multiplication. Native queries ignore the Hibernate pagination API.

Validation without assuming output

Verify the pagination is applied as intended.

1. Enable SQL logging in configuration, e.g. hibernate.show_sql and hibernate.format_sql. Inspect the generated SQL for LIMIT and OFFSET clauses that correspond to the parameters you set.
2. Run a separate SELECT COUNT(*) with the same filters to understand total size and reasonableness of page depth.
3. Check the size of the returned list against the requested pageSize and confirm ordering is stable via an indexed column.

Limitations: offset pagination is not suitable for infinite scroll over millions of rows. For deep navigation, prefer keyset pagination on an indexed column. Do not use Hibernate pagination with native SQL; implement paging manually in that case.