Server-Side Pagination with the JPA Criteria API in Hibernate 5.2+ and 6.x

Preventing Heap Exhaustion with Server-Side Pagination

Loading thousands of entities into the JVM heap to display a small subset of data leads to OutOfMemoryError and high latency. While application-level filtering is possible, it requires the database to transmit the entire result set over the network. Server-side pagination solves this by instructing the database to return only the specific slice of data required for the current view.

Prerequisites

• Hibernate 5.2+ or 6.x configured with a valid SessionFactory (the legacy org.hibernate.Criteria API was deprecated in 5.2 and removed in 6.x; use the JPA CriteriaBuilder/CriteriaQuery API).
• A JPA-compliant entity mapped to a database table.
• Access to enable SQL logging (hibernate.show_sql=true or TRACE on org.hibernate.SQL) for verification.

Implementing the Pagination Logic

Pagination in the JPA Criteria API is managed via setFirstResult() and setMaxResults() on the Query object. These methods abstract the underlying SQL syntax, allowing the same Java code to work across different database dialects (e.g., translating to LIMIT/OFFSET in PostgreSQL/MySQL or OFFSET/FETCH NEXT in SQL Server 2012+ and Oracle 12c+).

Deterministic ordering is mandatory. Without an ORDER BY clause, the database may return rows in an arbitrary order, causing overlapping or missing rows across pages.

// Example: Retrieving page 3 with 20 records per page
int pageNumber = 3; // 1-based page number from UI
int pageSize = 20;

// Calculate the zero-based offset
int offset = (pageNumber - 1) * pageSize;

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> root = cq.from(User.class);

// Define filters (e.g., active users only)
cq.select(root).where(cb.equal(root.get("active"), true));

// REQUIRED: deterministic ordering
cq.orderBy(cb.asc(root.get("id")));

Query<User> query = session.createQuery(cq);

// Apply pagination
query.setFirstResult(offset);
query.setMaxResults(pageSize);

List<User> results = query.getResultList();

Critical Performance Constraints

The Collection Fetch Trap

Applying pagination to a query that uses an explicit join fetch (via FetchType.EAGER with @Fetch(FetchMode.JOIN) or a joinFetch in the criteria) on a @OneToMany or @ManyToMany relationship triggers a warning (HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!).

When this occurs, Hibernate cannot push the limit/offset to the database because the join multiplies rows. It loads the entire result set into the JVM and performs pagination in memory, defeating the purpose and risking OutOfMemoryError.

Version note: In Hibernate 6, the behavior can be changed via the setting hibernate.query.fail_on_pagination_over_collection_fetch=true to throw an exception instead of silently falling back to in-memory pagination. FetchType.EAGER alone does not necessarily cause this; Hibernate typically loads eager collections via separate queries unless a join fetch is explicitly requested.

Deep Pagination Latency

As firstResult (offset) increases, query performance degrades because the database must still scan and discard all preceding rows. For datasets with millions of rows, consider "keyset pagination" (filtering by the last seen value of the ordering column, e.g., WHERE id > :lastId ORDER BY id LIMIT :pageSize) instead of offset-based pagination.

Verification and Diagnostics

To ensure pagination happens at the database level and not in the JVM, perform the following checks:

Check	Method	Expected Result
SQL Generation	Enable hibernate.show_sql=true or TRACE logging for org.hibernate.SQL	The SQL query must contain dialect-specific limit/offset clauses (LIMIT/OFFSET, FETCH FIRST, etc.). Note: show_sql does not print bind parameter values; use TRACE on org.hibernate.type.descriptor.sql to see the actual offset/limit values.
Result Size	Call results.size()	The size must be equal to or less than the setMaxResults value.
Data Shift	Increment firstResult by pageSize (next page)	The first entity in the returned list should be the entity immediately following the last entity of the previous page (guaranteed only with deterministic ORDER BY).
In-Memory Fallback Detection	Check logs for HHH000104 warning	No warning should appear. If present, remove the join fetch and load collections separately (e.g., via @BatchSize or a dedicated query).

Recovery Options

Since pagination is a read-only operation, it does not change database state. To revert to a full-load implementation for debugging, remove the setFirstResult and setMaxResults calls. If you encounter HHH000104 warnings, eliminate the join fetch from the criteria query and use a separate query or a @BatchSize annotation on the collection to fetch related entities efficiently.

Limitations and Uncertainties

• The exact SQL clauses generated depend on the configured Dialect; verify against your target database.
• Hibernate 6.x introduced hibernate.query.fail_on_pagination_over_collection_fetch; behavior may differ from 5.x. This article reflects common behavior but has not been verified against a live Hibernate 6.x instance.
• Keyset pagination requires a unique, non-null, sequentially ordered column (e.g., primary key) and is not a drop-in replacement for offset pagination when random page access is needed.