Choosing a Pagination Strategy in Hibernate for Large Datasets

Decision: pagination technique for large Hibernate result sets

Retrieving large datasets with Hibernate requires balancing memory consumption with query execution time. A plain list() can trigger OutOfMemoryError, while inefficient pagination forces the database to scan and discard thousands of rows per page.

The decision is which pagination method to use given constraints: avoid memory exhaustion, guarantee stable ordering, and support dynamic parameters.

Comparing supported options

Trade‑offs

LIMIT/OFFSET is dialect‑independent and simple. Hibernate translates setFirstResult and setMaxResults to LIMIT and OFFSET. The database scans from the start up to the offset before returning the page, so page 1000 reads and discards many rows. Ordering is also unstable when rows are inserted or deleted.

ScrollableResult uses a JDBC ResultSet cursor to stream rows with constant memory. The Hibernate Session and transaction must remain open until the cursor is closed to avoid connection leaks. This makes it unsuitable for stateless REST services.

Keyset pagination uses a WHERE clause on the last seen key, for example id > :lastId, with ORDER BY and setMaxResults. It leverages an index for fixed‑time retrieval and is stable against inserts. It requires a unique non‑null orderable column and does not support jump to arbitrary page numbers.

Concrete implementation

Run within a transactional service method with read access to the Item table. Placeholder :lastSeenId is the ID of the last item on the previous page, null for the first page.

// Permissions: read access to Item table
String hql = "FROM Item i WHERE (:lastId IS NULL OR i.id > :lastId) ORDER BY i.id ASC";
List<Item> items = session.createQuery(hql, Item.class)
    .setParameter("lastId", lastSeenId)
    .setMaxResults(pageSize)
    .getResultList();

Limitations

Keyset pagination does not support jump to page X without knowing the starting key. Ensure ordering columns form a unique non‑null composite key to prevent duplicate or missing rows. ScrollableResult requires the Session and transaction to stay active until the cursor is closed.