Implementing Database Pagination with Hibernate Query API

The Problem: Memory Exhaustion from Large Result Sets

Loading thousands of records into application memory to display a small subset to a user causes high heap usage and slows down response times. To prevent OutOfMemoryErrors and reduce database load, you must shift the filtering logic from the application layer to the database engine using pagination.

Prerequisites

• A Java project with Hibernate (or Spring Data JPA) integrated.
• A configured SessionFactory or EntityManager.
• An entity class mapped to a database table.

Implementing Result Limiting and Offsets

Hibernate provides a database-agnostic way to handle pagination through the setFirstResult and setMaxResults methods. This abstracts the underlying SQL syntax, whether your database uses LIMIT/OFFSET (PostgreSQL, MySQL) or FETCH FIRST (Oracle).

To ensure consistent results across pages, you must always include an ORDER BY clause. Without a deterministic sort order, the database may return records in a different sequence between requests, leading to duplicate or missing items on different pages.

Implementation Example

The following example demonstrates how to retrieve a specific "page" of Product entities using a TypedQuery for type safety.

// Run this within a transaction-managed service layer
// Required permissions: Read access to the Product table

public List<Product> getProductsPaged(int pageNumber, int pageSize) {
    // Calculate the zero-based offset
    // Example: Page 1 starts at 0, Page 2 starts at 20 (if pageSize is 20)
    int offset = (pageNumber - 1) * pageSize;

    String hql = "FROM Product p ORDER BY p.createdAt DESC";
    TypedQuery<Product> query = entityManager.createQuery(hql, Product.class);

    // Define the starting row (offset)
    query.setFirstResult(offset);

    // Define the number of records to return (page size)
    query.setMaxResults(pageSize);

    return query.getResultList();
}

Critical Performance Constraints

The Join Fetch Trap

A common mistake is combining pagination with JOIN FETCH on a collection (One-to-Many). Because the resulting SQL join produces a Cartesian product, Hibernate cannot accurately calculate the row limit at the database level. In these cases, Hibernate will fetch all matching rows into memory and perform pagination in the JVM, which can crash your application with an OutOfMemoryError.

Solution: Use a separate query to fetch IDs first, then fetch the full entities using those IDs, or use a @BatchSize configuration on the collection.

Deep Pagination Degradation

As the offset 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 seen) instead of offsets.

Verification and Diagnostics

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

hibernate.show_sql=true
hibernate.format_sql=true

Check the console output for the following indicators:

• Expected: The SQL query ends with a LIMIT or OFFSET clause.
• Warning: If you see a JOIN FETCH query without a limit clause, but the returned list is small, Hibernate is paginating in memory.

Functional Check:

1. Request Page 1 (offset 0, limit 10). Verify 10 records are returned.
2. Request Page 2 (offset 10, limit 10). Verify the 11th record in the database is the first item in the list.
3. Verify that no records overlap between Page 1 and Page 2.

Rollback and State Recovery

Since pagination is a read-only operation (SELECT), there is no state change to roll back in the database. If a pagination logic error occurs (e.g., negative offsets), the query will simply throw an IllegalArgumentException, which can be caught and handled by returning an empty list or a 400 Bad Request response.