Implementing Database Pagination with Hibernate Criteria API

The Problem: Memory Exhaustion from Large Result Sets

Loading thousands of database records into a Java application's memory often leads to OutOfMemoryError or severe latency. When building interfaces that display data in tables, you must limit the data retrieved from the database to only what is visible on the current screen. In a Hibernate-based environment, this is achieved through result limiting and offsetting.

The Solution: FirstResult and MaxResults

To implement pagination, you use two primary methods on the Hibernate Query or CriteriaQuery object: setFirstResult(int) and setMaxResults(int). Together, these define a "window" of data to retrieve.

• setFirstResult: The offset. It specifies the index of the first row to be retrieved (0-indexed).
• setMaxResults: The page size. It specifies the maximum number of records the database should return.

Implementation Example

The following example demonstrates how to calculate the offset dynamically based on a page number and apply it to a Criteria query. This assumes you are using Hibernate 5.x or 6.x within an Eclipse-based Java project.

// Required imports: javax.persistence.criteria.* or jakarta.persistence.criteria.*
public List<User> getUsersPaged(int pageNumber, int pageSize) {
    // Calculate the offset: page 0 starts at 0, page 1 starts at pageSize
    int offset = pageNumber * 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("status"), "ACTIVE"));

    TypedQuery<User> query = entityManager.createQuery(cq);
    
    // Apply pagination
    query.setFirstResult(offset);
    query.setMaxResults(pageSize);

    return query.getResultList();
}

Mechanism and Database Translation

Hibernate does not perform pagination in Java; it translates these methods into the specific SQL dialect of your database. For example:

• PostgreSQL/MySQL: Translates to LIMIT [maxResults] OFFSET [firstResult].
• Oracle 12c+: Translates to OFFSET [firstResult] ROWS FETCH NEXT [maxResults] ROWS ONLY.
• SQL Server: Uses OFFSET [firstResult] ROWS FETCH NEXT [maxResults] ROWS ONLY.

Critical Limitations and Common Mistakes

The Collection Fetch Warning (HHH000104)

A common mistake is applying pagination to a query that uses fetch join on a OneToMany relationship. If you join a collection and apply setMaxResults, Hibernate may log a warning: "firstResult/maxResults specified with collection fetch; applying in memory!"

This is dangerous because Hibernate will pull every matching record from the database into the JVM memory and then discard the extras to satisfy the limit. To avoid this, use a two-step approach: fetch the IDs of the primary entities first with pagination, then fetch the full entities and their collections using those IDs.

Deep Paging Performance

As the firstResult value increases (e.g., page 10,000), performance drops. The database must still scan through all preceding rows before it can return the requested window. For extremely large datasets, consider "keyset pagination" (filtering by the last ID of the previous page) instead of offsets.

Off-by-One Errors

Always verify if your API's pageNumber starts at 0 or 1. If the UI sends page=1 for the first page, your calculation must be (pageNumber - 1) * pageSize to avoid skipping the first page of data.

Verification and Diagnostics

To verify that pagination is happening at the database level and not in memory, enable SQL logging in your persistence.xml or application.properties:

hibernate.show_sql=true
hibernate.format_sql=true

Checklist for validation:

1. Run a query with setFirstResult(10) and setMaxResults(5).
2. Verify the result list contains exactly 5 elements.
3. Inspect the console logs to ensure the SQL contains a LIMIT, OFFSET, or FETCH clause.
4. If the SQL shows a simple SELECT * without limiting clauses, Hibernate is paginating in memory, and you must review your joins.