Implementing Efficient Pagination in Hibernate with Criteria/JPA

Desired outcome

Add pagination to a Hibernate‑based data access layer so that only a single page of results is fetched from the database, keeping memory usage low and response times predictable.

Prerequisites

• Hibernate ORM version 5.2 or later (the Criteria API used here is available from 5.2; JPA 2.1+ works similarly).
• An entity class mapped to a table, e.g., {@code Product} with a primary key {@code id} and a sortable column {@code name}.
• Access to a {@code EntityManager} or {@code Session} instance.
• Logging framework configured to show Hibernate‑generated SQL (set {@code hibernate.show_sql=true} in {@code persistence.xml} or {@code application.properties}).
• A test database with a known set of rows for validation.

Focused procedure

1. Define the base query. Using the JPA Criteria API (the same steps apply to a native SQL query or Hibernate {@code Criteria}).

   CriteriaBuilder cb = entityManager.getCriteriaBuilder();
   CriteriaQuery cq = cb.createQuery(Product.class);
   Root root = cq.from(Product.class);
   // Ensure deterministic ordering – required for correct pagination
   cq.orderBy(cb.asc(root.get("name")), cb.asc(root.get("id")));
   // Select the whole entity (you can also project specific columns)
   cq.select(root);
   

2. Create the typed query and apply pagination. Calculate the zero‑based offset from the page number and page size.

   int pageNumber = 2; // 1‑based page requested by UI
   int pageSize = 20;
   int offset = (pageNumber - 1) * pageSize;
   
   TypedQuery query = entityManager.createQuery(cq);
   query.setFirstResult(offset);   // LIMIT offset
   query.setMaxResult(pageSize);   // LIMIT pageSize
   

3. Execute and collect the page.

   List page = query.getResultList();
   

4. Verify the generated SQL. With Hibernate SQL logging enabled, the console should show a statement similar to:

   Hibernate: select product0_.id as id1_0_, product0_.name as name2_0_ ...
   from Product product0_
   order by product0_.name asc, product0_.id asc
   limit ? offset ?
   

   The two question marks correspond to {@code pageSize} and {@code offset} respectively. For dialects that use {@code FETCH NEXT … OFFSET …} (e.g., DB2, SQL Server 2012+), the pattern will be adapted automatically.
5. Optional: native SQL query. If you prefer a native query, the same pagination calls work:

   String sql = "SELECT p.* FROM Product p ORDER BY p.name, p.id";
   SQLQuery nativeQuery = entityManager.createNativeQuery(sql, Product.class);
   nativeQuery.setFirstResult(offset);
   nativeQuery.setMaxResult(pageSize);
   List page = nativeQuery.getResultList();
   

Expected checks

1. SQL logging check. Confirm that the logged SQL contains the dialect‑specific limit/offset construct and that the values match {@code setFirstResult} and {@code setMaxResult}.
2. Unit test. Insert a known number of rows (e.g., 235) into the test table, ordered by the same columns used in the query. For each page number, assert that the returned list size equals {@code pageSize} (except possibly the last page) and that the entities match the expected slice when ordered.
3. UI/manual check. Navigate to a high page number (e.g., page 50) in the application’s pagination UI. Verify that the response time remains acceptable and that no duplicate or missing entries appear when moving back and forth between pages.

Limitations and mitigation

• Deep offset penalty. Large {@code setFirstResult} values force the database to skip and discard many rows, which can degrade performance. For very large datasets consider keyset pagination (seek method) using the ordered columns as a cursor.
• Order stability. If the {@code ORDER BY} columns are not unique or can change between requests, rows may shift across pages, causing duplicates or gaps. Always include a unique column (e.g., primary key) as the final sort key.
• Dialect support. Hibernate translates {@code setFirstResult}/{@code setMaxResult} to the appropriate limit/offset syntax for the configured dialect. Verify that your dialect supports the construct; otherwise fallback to native SQL with vendor‑specific limit clauses.

Recovery options

Since pagination only affects the query execution and does not modify data, there is no transactional state to roll back. If an incorrect pagination configuration is discovered:

• Revert the changes to {@code setFirstResult} and {@code setMaxResult} (or remove them) and redeploy the data access layer.
• If you added an {@code ORDER BY} clause that caused instability, adjust the ordering columns to include a unique key and redeploy.

In case you introduced a native SQL query with pagination, simply replace it with the previous Criteria/JPA version or adjust the native SQL limit/offset syntax to match the dialect.