Implementing Database-Level Pagination with Hibernate setFirstResult and setMaxResults

The Problem: Memory Exhaustion from Large Result Sets

Loading thousands or millions of records into a Java application's memory frequently leads to OutOfMemoryError (OOME) and severe JVM garbage collection pauses. To prevent this, pagination must occur at the database level, ensuring the application only receives the specific subset of records required for the current view or process.

The Smallest Suitable Design

Hibernate provides a standardized way to implement offset-based pagination via the Query interface. This approach abstracts the underlying SQL dialect, allowing the same Java code to work across PostgreSQL, MySQL, Oracle, and SQL Server.

// Example: Fetching page 3 with 20 records per page
int pageNumber = 3;
int pageSize = 20;
int firstResult = (pageNumber - 1) * pageSize;

List<User> users = session.createQuery("from User u where u.status = :status", User.class)
    .setParameter("status", UserStatus.ACTIVE)
    .setFirstResult(firstResult) // The offset: how many rows to skip
    .setMaxResults(pageSize)    // The limit: how many rows to return
    .getResultList();

Trust and Data Boundaries

The boundaries of this operation are defined by the translation from the Hibernate ORM to the database dialect. The setFirstResult and setMaxResults methods accept integers, which Hibernate converts into dialect-specific SQL clauses:

• PostgreSQL/MySQL: LIMIT {max} OFFSET {first}
• Oracle (older versions): Nested queries using ROWNUM
• SQL Server: OFFSET {first} ROWS FETCH NEXT {max} ROWS ONLY

Operational Checks and Performance

While the implementation is simple, the operational cost is not constant. As the firstResult (offset) increases, the database must still scan and discard all preceding rows before returning the requested set. This leads to linear performance decay.

Diagnostic Verification

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

hibernate.show_sql=true

Check the logs for the presence of LIMIT or OFFSET clauses. If these are missing but the result set is truncated, Hibernate may be performing pagination in memory, which is a critical performance failure. For deeper analysis, run EXPLAIN ANALYZE on the generated SQL with a high offset to confirm whether the database performs a full scan before discarding rows.

Failure Modes and Critical Limitations

The 'Join Fetch' Memory Trap

A common failure occurs when using join fetch on a collection (One-to-Many or Many-to-Many) alongside pagination. Because joining a collection multiplies the number of rows returned by the database, Hibernate cannot accurately calculate the offset in SQL without risking data loss.

The result: Hibernate will fetch all matching rows into the JVM memory and perform the pagination in Java. This typically triggers a warning in the logs: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!. This is a primary cause of OOME in production environments. Monitor JVM heap usage when paginating any query that joins a collection.

Deep Pagination Latency

When requesting a page far into a multi-million row table (e.g., setFirstResult(1000000)), the database server may experience high CPU utilization and disk I/O. This can result in a QueryTimeoutException or slow response times for all users sharing the database instance.

When to Change the Design

Offset-based pagination is suitable for small-to-medium datasets or applications where users rarely navigate beyond the first few pages. You should migrate to Keyset Pagination (also known as the Seek Method) if the following conditions are met:

• The dataset grows to a size where deep-offset queries exceed acceptable latency thresholds.
• The data is frequently updated, causing "drifting" results (where a record moves from page 1 to page 2 while a user is scrolling, resulting in duplicate or skipped entries).

Keyset pagination replaces setFirstResult with a WHERE clause filtering by a unique, indexed identifier (e.g., WHERE u.id > :lastSeenId ORDER BY u.id ASC with setMaxResults(20)), ensuring constant-time performance regardless of page depth. The trade-off is that users can no longer jump to an arbitrary page number; navigation becomes sequential.