Implementing Server-Side Pagination with Hibernate Criteria API

The Problem: Memory Exhaustion from Large Result Sets

Loading thousands of database records into the Java Virtual Machine (JVM) heap often leads to OutOfMemoryError or severe garbage collection pauses. When a user requests a list of data, retrieving the entire dataset and filtering it in application memory is inefficient. The solution is server‑side pagination, where the database only returns a specific subset of rows for a given request.

Prerequisites

• Hibernate 5.x or 6.x configured with a valid SessionFactory.
• A JPA‑compliant entity class.
• A database dialect configured (e.g., PostgreSQL, MySQL, or Oracle) to allow Hibernate to translate pagination methods into native SQL.

Implementing Pagination via Criteria API

Pagination in Hibernate is controlled by two primary methods: setFirstResult() and setMaxResults(). These methods instruct the database to skip a specific number of rows and return only a limited count.

Step‑by‑Step Implementation

1. Initialize the CriteriaBuilder: Create a query based on your entity.
2. Define the Sort Order: Pagination without a deterministic sort order (like a primary key or timestamp) can result in duplicate or missing records across pages.
3. Apply the Offset: Use setFirstResult(int start) to define the starting index (0‑based).
4. Apply the Limit: Use setMaxResults(int limit) to define the page size.

Concrete Implementation Example

The following example demonstrates retrieving a specific page of Product entities from a database.

// Run this within a Transactional service method
public List<Product> getProductsPaged(int pageNumber, int pageSize) {
    Session session = sessionFactory.getCurrentSession();
    CriteriaBuilder cb = session.getCriteriaBuilder();
    CriteriaQuery<Product> cq = cb.createQuery(Product.class);
    Root<Product> root = cq.from(Product.class);

    // 1. Ensure consistent ordering to prevent shifting results
    cq.orderBy(cb.asc(root.get("id")));

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

    // 2. Calculate the offset (e.g., Page 2 with size 20 starts at index 20)
    int offset = pageNumber * pageSize;
    query.setFirstResult(offset);

    // 3. Limit the result set size
    query.setMaxResults(pageSize);

    return query.getResultList();
}

Performance and Implementation Trade‑offs

While pagination solves memory issues, it introduces specific architectural risks depending on the query structure.

The Join Fetch Warning (HHH000104)

If you use fetch join on a OneToMany collection while applying pagination, Hibernate may trigger a warning: "firstResult/maxResults specified with collection fetch". Because joining a parent to multiple children multiplies the number of rows returned by the SQL query, Hibernate cannot accurately calculate the offset at the database level. To avoid this, Hibernate will pull all records into memory and paginate them in the JVM, defeating the purpose of server‑side pagination.

Solution: Fetch the parent IDs first using pagination, then perform a second query to fetch the associated collections for those specific IDs.

Deep Paging Degradation

As the offset value increases (e.g., setFirstResult(100000)), performance drops. The database must still scan through the first 100,000 rows before discarding them and returning the requested page. For extremely large datasets, consider keyset pagination (filtering by the last ID of the previous page) instead of offset‑based pagination.

Verification and Diagnostics

To ensure the pagination is occurring on the database server and not in memory, perform the following checks:

1. SQL Log Inspection

Enable SQL logging in your hibernate.cfg.xml or application.properties:

hibernate.show_sql=true
hibernate.format_sql=true

Verify that the generated SQL contains dialect‑specific keywords such as LIMIT and OFFSET (PostgreSQL/MySQL) or OFFSET ... FETCH NEXT (SQL Server).

2. Boundary Testing

• Empty Page: Request a page index that exceeds the total record count. The result should be an empty List, not a NullPointerException or an error.
• Exact Match: Request a page size that exactly matches the total record count to ensure no records are truncated.