Pagination Done Right: Hibernate Criteria API Patterns That Scale

The Problem: Pagination That Works in Dev But Fails in Production

You've built a REST endpoint that returns a list of entities. In development with 50 rows, list.subList(offset, offset + pageSize) works fine. Push that same code to production with 2 million rows and you'll hit OutOfMemoryError or watch the database scan the entire table for page 500. The fix isn't a library upgrade—it's moving pagination and filtering to the database layer where they belong.

Why Criteria API Over JPQL or Native SQL

The Criteria API (JPA 2.0+, Hibernate 5.x/6.x) lets you build queries programmatically. This matters when filters are optional—search by name, status, date range, any combination. With JPQL you'd concatenate strings or maintain a combinatorial explosion of named queries. Criteria API composes Predicate objects dynamically, and Hibernate translates them to parameterized SQL with proper LIMIT/OFFSET (or dialect equivalents).

Core Pattern: Filter First, Paginate Second

Always apply predicates to the CriteriaQuery before calling setFirstResult() and setMaxResults() on the TypedQuery. The database then returns only the page you need. A separate count query uses the same predicate tree so the total matches the filtered set.

public Page<Order> findOrders(OrderFilter filter, Pageable pageable) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    
    // 1. Build the data query
    CriteriaQuery<Order> cq = cb.createQuery(Order.class);
    Root<Order> root = cq.from(Order.class);
    List<Predicate> predicates = buildPredicates(cb, root, filter);
    cq.where(predicates.toArray(new Predicate[0]));
    cq.orderBy(cb.desc(root.get("createdAt")));
    
    TypedQuery<Order> query = entityManager.createQuery(cq);
    query.setFirstResult((int) pageable.getOffset());
    query.setMaxResults(pageable.getPageSize());
    List<Order> content = query.getResultList();
    
    // 2. Build the count query using the SAME predicates
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<Order> countRoot = countQuery.from(Order.class);
    List<Predicate> countPredicates = buildPredicates(cb, countRoot, filter);
    countQuery.select(cb.count(countRoot)).where(countPredicates.toArray(new Predicate[0]));
    long total = entityManager.createQuery(countQuery).getSingleResult();
    
    return new PageImpl<>(content, pageable, total);
}

private List<Predicate> buildPredicates(CriteriaBuilder cb, Root<Order> root, OrderFilter filter) {
    List<Predicate> predicates = new ArrayList<>();
    if (filter.getCustomerId() != null) {
        predicates.add(cb.equal(root.get("customerId"), filter.getCustomerId()));
    }
    if (filter.getStatus() != null) {
        predicates.add(cb.equal(root.get("status"), filter.getStatus()));
    }
    if (filter.getFromDate() != null) {
        predicates.add(cb.greaterThanOrEqualTo(root.get("createdAt"), filter.getFromDate()));
    }
    if (filter.getToDate() != null) {
        predicates.add(cb.lessThanOrEqualTo(root.get("createdAt"), filter.getToDate()));
    }
    return predicates;
}

Run this in a Spring Data JPA repository implementation or a plain @Repository class. Requires EntityManager injection (persistence context). No special permissions beyond DB read access.

The Fetch Join Trap

Adding root.fetch("lineItems", JoinType.LEFT) to eagerly load a collection seems convenient. With pagination, Hibernate 5.x and 6.x will often fall back to in-memory pagination because the join multiplies rows and the database can't apply LIMIT cleanly to the parent entity. You'll see a warning in logs: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!.

Fix: load the parent page first, then fetch collections in a separate query using IN with the parent IDs, or use @BatchSize / @Fetch(FetchMode.SUBSELECT) on the relationship. For the example above, remove the fetch join and let lazy loading or a second query handle lineItems.

Deep Paging Performance

OFFSET 100000 LIMIT 20 forces the database to scan and discard 100,000 rows. On PostgreSQL, MySQL, and SQL Server this degrades linearly. For UIs that need "page 5000", switch to keyset pagination (seek method): remember the last seen sort key and filter WHERE createdAt < :lastSeen ORDER BY createdAt DESC LIMIT 20. Criteria API supports this by adding a predicate on the sort column instead of using setFirstResult.

// Keyset pagination example
if (filter.getCursorCreatedAt() != null) {
    predicates.add(cb.lessThan(root.get("createdAt"), filter.getCursorCreatedAt()));
}
// No setFirstResult needed
query.setMaxResults(pageable.getPageSize());

Verification Checklist

1. Enable Hibernate SQL logging (logging.level.org.hibernate.SQL=DEBUG) and verify generated SQL contains LIMIT/OFFSET (or FETCH FIRST n ROWS ONLY / ROWNUM per dialect).
2. Run with a table of 10,000+ rows. Confirm content.size() == pageSize and no full-table scan in EXPLAIN ANALYZE.
3. Compare filtered vs unfiltered query times—indexes on filtered columns should keep both fast.
4. Check logs for the in-memory pagination warning when fetch joins are present.

Limitations & Trade-offs

• Two round trips per page (data + count). For high-throughput endpoints, consider caching the count or using a materialized view.
• Keyset pagination breaks traditional page-number UIs; you need "next/previous" or infinite scroll.
• Criteria API verbosity grows with complex filters. For stable, complex queries, a well-indexed native SQL view or stored procedure may be simpler.

Actionable Next Step

Pick one repository method that currently loads all entities into a List and paginates in Java. Replace it with the pattern above. Run the verification checklist. You'll reduce heap pressure, improve latency, and make the DBA happy.