Hibernate Pagination: Avoiding Duplicate Rows and Memory Blowups with setMaxResults

The Problem: Ten Rows That Are Not Ten Entities

You ask for page two of a customer dashboard with setMaxResults(20). The generated SQL contains a LIMIT 20 clause, but the list that comes back holds only four Order objects. Each order appears multiple times, or the count is simply wrong. The cause is rarely a bug in Hibernate; it is the interaction between SQL row limits and ORM fetch strategy.

The takeaway: pagination in Hibernate ORM is not a pure LIMIT/OFFSET translation. It is shaped by how collection joins expand the result set, how long database cursors stay open, and which major version of the ORM you run.

Why setMaxResults Misbehaves with Fetch Joins

Query and TypedQuery expose setFirstResult(int) and setMaxResults(int). Hibernate translates these into dialect-specific LIMIT and OFFSET clauses. That works cleanly when you select a single entity without collection joins.

When you add join fetch on a one-to-many collection, the SQL result set contains one row per child element. Hibernate applies the limit to that expanded row set, then de-duplicates parent entities in memory. Ten rows might represent three parents, not ten. If the query fetches more than one collection bag, Hibernate may throw a QueryException or produce a Cartesian product.

A Worked Example

Assume Order has a @OneToMany collection of LineItem objects.

// Problematic: collection fetch join combined with a row limit
List<Order> page = entityManager.createQuery(
    "select o from Order o join fetch o.lineItems order by o.id", Order.class)
    .setFirstResult(0)
    .setMaxResults(10)
    .getResultList();

If each order has three line items, the database returns ten joined rows. Hibernate collapses those into roughly three distinct orders. The caller asked for ten orders and received three.

The safer pattern is to paginate the parent query without the collection fetch, then load collections separately or rely on @BatchSize to avoid N+1 queries.

// Paginate parents only
List<Order> orders = entityManager.createQuery(
    "select o from Order o order by o.id", Order.class)
    .setFirstResult(20)
    .setMaxResults(10)
    .getResultList();
// Collections are loaded lazily or via a follow-up query

If you only need scalar data for a table view, project into a DTO instead of fetching entities. That avoids the join expansion entirely.

ScrollableResults: Streaming Without Loading the World

For large exports or batch jobs, ScrollableResults lets you process rows one at a time instead of materializing a full List. It keeps a JDBC result set open and walks it with a forward-only cursor.

The risk is resource lifetime. An open cursor holds database resources and can pin rows or locks depending on the isolation level. Forgetting to close it, or wrapping it in a long-running transaction, can exhaust the connection pool.

Cursor Discipline

Close the cursor in a finally block or equivalent, and keep the surrounding transaction as short as your business rules allow.

// Inside a transactional service method
ScrollableResults results = session.createQuery(
    "select o from Order o order by o.id", Order.class)
    .scroll(ScrollMode.FORWARD_ONLY);
try {
    while (results.next()) {
        Order order = (Order) results.get(0);
        // process the row
    }
} finally {
    results.close();
}

Exact method signatures differ between Hibernate 5.6 and 6.x, so verify the scroll overload and ScrollableResults import for your version before relying on this snippet.

Keyset Pagination for Deep Pages

OFFSET 100000 forces the database to produce and discard a hundred thousand rows. On deep pages, performance degrades. Keyset pagination replaces the offset with a filter on an ordered, unique column.

List<Order> nextPage = entityManager.createQuery(
    "select o from Order o where o.id > :lastId order by o.id asc", Order.class)
    .setParameter("lastId", lastSeenId)
    .setMaxResults(50)
    .getResultList();

This assumes id is unique and indexed, which is true for most primary keys. If you order by a non-unique column, add the primary key as a tiebreaker so the cursor position is deterministic.

Limitations and Version Differences

• No deterministic order, no stable pagination. Without ORDER BY, rows can shift between pages as data changes.
• Dialect support varies. Some older databases emulate OFFSET poorly or not at all, which affects correctness as well as speed.
• Hibernate 5 vs 6. Limit handling and query translation changed between major releases. A pagination test that passes on 5.6 may behave differently on 6.x.
• ScrollableResults is not for user-facing requests. It holds a cursor open; concurrent traffic multiplied by open cursors can exhaust connections.

How to Verify Before You Ship

1. Enable SQL logging (hibernate.show_sql=true and hibernate.format_sql=true, or set the org.hibernate.SQL logger to DEBUG) and inspect the generated LIMIT/OFFSET clause for your target dialect.
2. Write an integration test against the actual database. Request page two with a deterministic order and assert the returned IDs match the expected slice.
3. For ScrollableResults, verify the cursor is closed and the session is released after iteration, ideally by asserting connection-pool metrics return to baseline.

Decision Guide

Use plain setFirstResult/setMaxResults for shallow pages of single entities. When collections are involved, paginate parents first and fetch children separately or with @BatchSize. Use ScrollableResults only for large, sequential, read-only processing with strict cursor cleanup. Switch to keyset pagination when page numbers grow large or the UI needs stable forward scrolling.