Controlling Result Reach in Hibernate with setMaxResults and setFirstResult

When a repository method returns more entities than a UI can display at once, the database has to decide which rows make it into the result set. Without explicit boundaries, a JPQL or Criteria query can fetch every matching row, pulling gigabytes of data into the persistence context and overwhelming both memory and network latency.

The pagination problem in Hibernate

Hibernate provides two methods on the Query interface that let you define those boundaries portably: setMaxResults and setFirstResult. Together they form an offset-based pagination mechanism that works across database dialects, but their behavior shifts subtly between Hibernate versions and depends on whether you're using JPQL or native SQL.

How setMaxResults and setFirstResult translate to SQL

In Hibernate 5.x and later, setMaxResults(10) typically becomes a LIMIT 10 clause, while setFirstResult(5) becomes OFFSET 5. The generated SQL depends on the database dialect registered in your hibernate.dialect configuration. For example, PostgreSQL produces LIMIT 10 OFFSET 5, MySQL produces the same syntax, and Oracle may require a subquery or FETCH FIRST 10 ROWS ONLY with an offset adapter.

Important: the methods operate on the managed query object, not on the result set after fetching. That means the database still executes a full scan of the underlying table, and Hibernate discards rows beyond the offset in the JDBC layer. Unfamiliar readers may confuse this with cursor-based pagination, which tracks a stable identifier rather than a numeric offset.

A concrete worked example

// JPQL query with pagination
String jpql = "SELECT u FROM User u WHERE u.active = true ORDER BY u.createdAt DESC";
Query query = entityManager.createQuery(jpql);
query.setMaxResults(20);
query.setFirstResult((page - 1) * 20);
List page = query.getResultList();

If page is 1, setFirstResult receives 0 and the database returns the first 20 rows. If page is 2, the offset is 20, and the database skips the first 20 rows before returning the next 20. The SQL you'll see in the Hibernate log (enable show_sql or format_sql) should resemble:

Hibernate: 
SELECT user0_.id AS id1_0_,
       user0_.created_at AS created2_0_,
       user0_.active AS active3_0_
FROM user user0_
WHERE user0_.active = true
ORDER BY user0_.created_at DESC
LIMIT 20 OFFSET 0

For page 2, the offset clause changes to OFFSET 20. Verify that the entity count returned matches setMaxResults and that no extra rows appear, even when the table contains hundreds of thousands of records.

Version-sensitive behavior and first-level cache

Hibernate 3.x handled the interaction between setMaxResults, setFirstResult, and the first-level cache differently than 5.x and 6.x. In older versions, the cache could sometimes retain entities from a previous paginated query, leading to duplicate appearances if the same session persisted results across pages. Modern versions isolate the query's result set more strictly, but the safest pattern is to clear or close the session between distinct pagination cycles when working with large offsets.

Another version nuance: native SQL queries sometimes ignore setMaxResults entirely, depending on the driver. If you need portable limit/offset with a native query, consider wrapping it in a subquery or using a vendor-specific hint. Always confirm the generated SQL for your target dialect.

Trade-offs: offset depth and performance

The deeper the offset, the more rows the database must examine before returning the requested page. On a table with millions of rows, an OFFSET 100000 query can take seconds longer than OFFSET 0, even with an index on the sort column, because the database must walk past every skipped row. This is an inherent cost of offset-based pagination.

A practical way to check the impact is to enable Hibernate's SQL log, run the same query with offsets 0, 100, 1000, and 10000, and compare execution times. If the gap grows linearly or worse, it's a signal that offset pagination will degrade user experience at higher pages.

When offset depth becomes a bottleneck, many teams migrate to keyset (cursor-based) pagination, where the query references the last seen value of the sorted column (e.g., WHERE created_at < :lastSeen). That approach turns the seek into an index lookup, which remains fast regardless of page number. The trade-off is that you can't jump arbitrarily to page 50 without traversing the preceding pages, and sorting must be on a unique, monotonically increasing column.

Actionable next steps

1. Write a JPQL query with setMaxResults and setFirstResult for your first pagination use case.
2. Enable spring.jpa.show-sql or hibernate.show_sql and inspect the generated LIMIT/OFFSET clauses for your database dialect.
3. Run the query with increasing offset values and record execution times. If growth is steep, prototype a keyset cursor query on the same sorted column.
4. Document the version-specific behavior for your Hibernate version and note any native-query edge cases.

Offset-based pagination is simple to implement and sufficient for many admin UIs and small datasets. When your result sets grow or your pages deepen, a quick performance check will tell you whether to stay with offsets or switch to a cursor pattern. Either way, knowing how setMaxResults and setFirstResult translate to SQL gives you the control to keep your data reach under control.