Preventing OutOfMemoryErrors: Implementing Pagination in Java with Hibernate

The Cost of Loading Everything

A common failure point in Java applications occurs when a developer calls findAll() or a similar query on a table that has grown from a few hundred records to several hundred thousand. Because Hibernate attempts to load all resulting entities into the persistence context (the first-level cache), the JVM heap quickly saturates, leading to an OutOfMemoryError (OOME).

The solution is pagination: limiting the number of records retrieved in a single database round-trip. By requesting only a small subset of data, you keep the memory footprint stable regardless of the total table size.

Offset-Based Pagination with Hibernate

The most straightforward way to implement pagination in Hibernate is through offset-based pagination. This uses two primary methods on the Query object: setFirstResult(int) and setMaxResults(int).

• setFirstResult: Defines the starting row (the offset). The first row is 0.
• setMaxResults: Defines the page size (the limit).

When these methods are called, Hibernate translates them into the dialect-specific SQL of your database, such as LIMIT and OFFSET in PostgreSQL or MySQL.

Implementation Example: Spring Data JPA

While you can use the Hibernate API directly, Spring Data JPA provides the Pageable interface to abstract this logic. This allows the service layer to define the page request without worrying about the underlying SQL syntax.

// Repository Interface
public interface ProductRepository extends JpaRepository<Product, Long> {
    Page<Product> findByCategory(String category, Pageable pageable);
}

// Service Layer Usage
public Page<Product> getProductsByCategory(String category, int page, int size) {
    // PageRequest is a concrete implementation of Pageable
    // page is 0-indexed
    Pageable pageable = PageRequest.of(page, size, Sort.by("name").ascending());
    return productRepository.findByCategory(category, pageable);
}

Verification: To ensure the pagination is happening at the database level and not in the JVM, enable SQL logging in your application.properties:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Check your logs for the LIMIT and OFFSET clauses. If you see a query that selects all rows without these clauses, Hibernate may be performing "in-memory pagination," which defeats the purpose of the optimization.

The "Join Fetch" Performance Trap

A critical limitation occurs when using JOIN FETCH to solve the N+1 select problem while paginating. If you fetch a collection (e.g., Product and its Reviews) using a join, the database returns a Cartesian product. Hibernate cannot safely apply a LIMIT to the SQL because one Product might span five rows due to its reviews.

In this scenario, Hibernate will log a warning: "firstResult/maxResults specified with collection fetch; applying in memory!". It will pull the entire dataset into the JVM and paginate it there, which will likely trigger the OutOfMemoryError you were trying to avoid.

Decision Matrix: Offset vs. Keyset Pagination

Feature	Offset Pagination	Keyset (Seek) Pagination
Implementation	Simple (setFirstResult)	Complex (Filter by last ID)
Deep Page Performance	Slow (Scans all previous rows)	Fast (Uses index seek)
Consistency	Items shift if rows are deleted	Stable cursor
Use Case	UI with page numbers (1, 2, 3)	Infinite scroll / API feeds

Practical Constraints

Offset pagination is sufficient for most administrative dashboards, but it degrades linearly. If a user requests page 10,000, the database must still scan the first 9,999 pages before discarding them to return the requested 10 rows. For massive datasets or high-frequency APIs, implement Keyset Pagination by filtering for records where the ID is greater than the last ID of the previous page (e.g., WHERE id > :lastSeenId LIMIT 20).

Actionable Summary

To safely manage large result sets in Java:

1. Use Pageable and PageRequest to enforce limits at the database level.
2. Avoid JOIN FETCH on collections when paginating; use separate queries or @BatchSize instead.
3. Monitor your SQL logs to confirm LIMIT and OFFSET are present.
4. Switch to Keyset pagination if your application requires "deep paging" into millions of records.