Server-Side Pagination with JPA Criteria in Hibernate

Problem: Controlling Memory During Large Result Retrieval

Loading an entire table into the Hibernate persistence context can exhaust heap memory and cause OutOfMemoryError. Server-side pagination pushes limit and offset logic into the database so that only the required rows are transferred and instantiated. This guide shows how to implement pagination with the JPA Criteria API, why JOIN FETCH breaks page-size guarantees, and how to verify the generated SQL.

Prerequisites and Version Notes

• Hibernate 5.x or 6.x configured as the JPA provider. Hibernate 6 removed the legacy org.hibernate.Criteria API; use the standard JPA CriteriaBuilder from javax.persistence.criteria (Hibernate 6.0/6.1) or jakarta.persistence.criteria (Hibernate 6.2+). Confirm your Hibernate version to choose the correct package.
• An active EntityManager or EntityManagerFactory available to the calling code.
• A database dialect that translates limit/offset clauses (for example, PostgreSQL, MySQL, SQL Server 2012+, Oracle 12c+). Verify the generated SQL for your specific database version.

Procedure: Basic Offset Pagination

1. Obtain a CriteriaBuilder from the EntityManager.
2. Create a CriteriaQuery for the target entity type.
3. Define the query root, predicates, and a deterministic orderBy clause.
4. Instantiate a TypedQuery from the CriteriaQuery.
5. Calculate the zero-based offset: offset = (pageNumber - 1) * pageSize.
6. Apply pagination with query.setFirstResult(offset) and query.setMaxResults(pageSize).
7. Execute query.getResultList().

// Run in your repository or DAO layer, typically inside a transaction
// if lazy associations will be accessed after the query returns.
public List<User> getUsersPage(int pageNumber, int pageSize) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<User> cq = cb.createQuery(User.class);
    Root<User> user = cq.from(User.class);

    cq.select(user)
      .where(cb.equal(user.get("active"), true))
      .orderBy(cb.asc(user.get("id")));

    TypedQuery<User> query = entityManager.createQuery(cq);
    int offset = (pageNumber - 1) * pageSize;
    query.setFirstResult(offset);
    query.setMaxResults(pageSize);

    return query.getResultList();
}

Why JOIN FETCH Breaks Page-Size Guarantees

setMaxResults limits the number of SQL rows returned, not the number of distinct root entities. When a query joins a collection with JOIN FETCH, the database returns one row per collection element. If a root entity has three associated rows and pageSize is 10, the database may return only four distinct entities. Adding distinct(true) does not reliably fix this because the limit is applied to the row set before or during deduplication, depending on the dialect and provider version. Collecting results into a Set after execution is also unreliable because the SQL has already discarded rows.

The reliable pattern is two-step pagination: first query only the root identifiers with the limit, then fetch the full entities with fetch joins using those identifiers.

// Step 1: paginate identifiers only
CriteriaQuery<Long> idCq = cb.createQuery(Long.class);
Root<User> idRoot = idCq.from(User.class);
idCq.select(idRoot.get("id"))
    .where(cb.equal(idRoot.get("active"), true))
    .orderBy(cb.asc(idRoot.get("id")));

TypedQuery<Long> idQuery = entityManager.createQuery(idCq);
idQuery.setFirstResult(offset);
idQuery.setMaxResults(pageSize);
List<Long> ids = idQuery.getResultList();

if (ids.isEmpty()) {
    return Collections.emptyList();
}

// Step 2: fetch entities with collections initialized
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> user = cq.from(User.class);
user.fetch("roles", JoinType.LEFT);
cq.select(user)
  .where(user.get("id").in(ids))
  .orderBy(cb.asc(user.get("id")));

return entityManager.createQuery(cq).getResultList();

Expected Checks and Verification

Check	Expected result
Generated SQL	Enable DEBUG logging for org.hibernate.SQL in your logging framework (Logback, Log4j2, java.util.logging). The statement should contain a dialect-specific limit/offset clause such as LIMIT ? OFFSET ? or an equivalent vendor construct. Avoid relying on the legacy hibernate.show_sql=true property, which does not integrate with modern logging and often omits bound parameters.
Page size (no fetch joins)	resultList.size() <= pageSize. The final page may return fewer rows.
Out-of-range page	Requesting an offset beyond the total row count returns an empty list without throwing.
Page boundaries	Iterating consecutive pages with a deterministic orderBy produces no skipped or duplicated root entities.
Fetch-join pagination	If you use the two-step pattern, the first query returns exactly pageSize identifiers (or fewer on the last page), and the second query returns the same number of distinct root entities.

Managing Persistence Context Growth

Pagination limits the number of entities loaded per query, but repeated calls within the same transaction still accumulate managed instances in the first-level cache. To avoid unbounded growth:

• Call entityManager.clear() after processing each page if the entities do not need to remain managed.
• For bulk-processing scenarios, consider a Hibernate StatelessSession, which bypasses the first-level cache entirely.

Limitations and Alternatives

Deep pagination: Very high offset values force the database to scan and discard many rows before reaching the requested page, degrading performance. If users frequently navigate far into a result set, evaluate keyset pagination (the seek method), where you filter on the last seen value of a unique, ordered column instead of using an offset.

Non-deterministic ordering: Without an explicit orderBy, the database may return rows in varying physical order, causing pages to overlap or skip records. Always order by a stable, unique column when paginating.

Dialect differences: While most modern Hibernate dialects translate setFirstResult and setMaxResults into native limit/offset syntax, older or less common databases may require vendor-specific constructs. Verify the generated SQL against your target database version before deploying.

Namespace uncertainty: Hibernate 6.2+ migrated to the jakarta.persistence namespace. If you are upgrading across major versions, confirm whether your project uses javax or jakarta imports and adjust accordingly.