Preventing Memory Crashes: Implementing Server-Side Pagination with Hibernate

The Danger of the 'Fetch All' Approach

A common mistake in Java backend development is retrieving an entire database table into application memory and then filtering or slicing that list for the UI. While this works with 100 records, it triggers OutOfMemoryError crashes once your production dataset grows to thousands or millions of rows.

The goal is to shift the heavy lifting—sorting and slicing—to the database engine. By using Hibernate's pagination and sorting features, you ensure the application only ever handles the specific subset of data required for the current view.

Offset-Based Pagination Logic

Hibernate implements pagination through two primary methods on the Query interface: setFirstResult() and setMaxResults(). These methods instruct the database to skip a specific number of rows and return a limited window of results.

• setFirstResult(int startPosition): The index of the first result to be returned (0-based). This acts as the "offset."
• setMaxResults(int maxResults): The maximum number of results to be returned. This acts as the "page size."

When these are called, Hibernate uses the configured Database Dialect to translate these calls into native SQL, such as LIMIT and OFFSET in PostgreSQL or FETCH FIRST in Oracle.

Implementing Sorting and Pageable Logic

Sorting must happen at the database level to be efficient. If you sort in Java using Collections.sort(), you have already loaded the entire dataset, defeating the purpose of pagination. Instead, use the ORDER BY clause in HQL (Hibernate Query Language) or the Criteria API.

In modern Spring-based projects developed in WebStorm, the Pageable abstraction is the standard way to pass these parameters from the REST controller down to the repository layer.

Worked Example: Paginated User Retrieval

Assume you are building a user management table. You need to retrieve users sorted by their registration date, 10 per page.

// Repository Layer Implementation
public Page<User> findUsers(int page, int size, String sortBy) {
    // 1. Create the HQL query with dynamic sorting
    String hql = "FROM User u ORDER BY u." + sortBy + " DESC";
    Query<User> query = session.createQuery(hql, User.class);

    // 2. Apply pagination
    query.setFirstResult(page * size);
    query.setMaxResults(size);

    List<User> results = query.getResultList();

    // 3. Execute a separate count query for the frontend pagination UI
    String countHql = "SELECT count(u) FROM User u";
    Long totalCount = (Long) session.createQuery(countHql).getSingleResult();

    return new PageImpl<<User>>(results, PageRequest.of(page, size), totalCount);
}

Execution Details: Run this logic within your service layer. Ensure your hibernate.show_sql=true property is enabled in application.properties to verify that the generated SQL contains the native limit keywords rather than returning all rows.

Critical Trade-offs and Limitations

Pagination is not a silver bullet. There are two specific scenarios where this approach can fail or degrade performance:

The 'Join Fetch' Memory Trap

If you use JOIN FETCH to load associated collections (e.g., fetching a User and all their Orders in one query) while using setFirstResult, Hibernate may realize it cannot perform the pagination in SQL without duplicating parent rows. In these cases, Hibernate often performs the pagination in memory, loading every single record into the JVM and then slicing the list. This will lead to a crash on large datasets.

Deep Pagination Performance

Offset-based pagination slows down as the page number increases. For example, OFFSET 100000 LIMIT 10 requires the database to scan through 100,000 rows before discarding them and returning the final 10. For extremely large datasets, consider "keyset pagination" (using the last ID seen as a filter) instead of offsets.

Verification Checklist

To confirm your implementation is working correctly, perform these three checks:

1. SQL Log Audit: Check the console. If you don't see LIMIT or OFFSET (or equivalent) in the SQL, Hibernate is likely fetching all records into memory.
2. Boundary Testing: Request page 0 with size 5, then page 1 with size 5. Verify that the first record of page 1 is exactly the record following the last record of page 0.
3. Count Accuracy: Ensure your total count query is executed separately; calculating the total size of a paginated list in Java will only give you the page size, not the total dataset size.