Master Hibernate 7 Pagination: The Ultimate Guide for High-Performance Java Apps

When your database grows from hundreds to millions of records, fetching everything in a single query isn’t just slow—it’s a recipe for an OutOfMemoryError. Whether you are building a modern dashboard or a high-traffic e-commerce site, Hibernate 7 pagination is the essential technique to keep your application responsive and your memory footprint low. In this guide, we’ll explore how to implement efficient pagination using the latest Jakarta Persistence (JPA) standards.

The Problem: The “Data Avalanche”

Imagine a user searching for products on your site. If your backend attempts to load 50,000 rows into memory just to display the first 10, the server will lag, the database will lock up, and the user will likely bounce before the page even loads. For high-concurrency systems, even a few unoptimized queries can saturate the database connection pool, leading to a total system outage.

The Agitation: Why “Limit 10” Isn’t Enough

Many developers treat pagination as an afterthought, adding a simple limit at the end of a query. However, inefficient queries lead to high CPU usage and increased cloud infrastructure costs. Without a structured approach to Hibernate pagination, you risk:

The N+1 Problem: Accidentally triggering thousands of extra queries for related data while trying to paginate the main list.
Broken Sort Orders: Unpredictable result sets when new data is inserted between page loads.
The Memory Trap: Fetching millions of rows into the application layer just to discard 99% of them in Java code.

If your application can’t scale its data delivery, it can’t scale its user base.

The Solution: Hibernate 7 Pagination Simplified

Hibernate 7, fully embracing Jakarta EE 10+, provides a clean, standardized way to handle subset fetching. By leveraging setFirstResult() and setMaxResults(), Hibernate translates your high-level HQL or Criteria queries into optimized SQL specific to your database (MySQL, PostgreSQL, Oracle, etc.).

1. How Hibernate Pagination Works Under the Hood

Hibernate uses the Database Dialect to determine the most efficient SQL syntax. The power of Hibernate lies in its abstraction; you write your query once, and it adapts to the target engine:

MySQL & PostgreSQL: Uses LIMIT ? OFFSET ?.
Oracle 12c+ & SQL Server 2012+: Uses OFFSET ? ROWS FETCH NEXT ? ROWS ONLY.
Legacy Oracle: Uses complex subqueries involving ROWNUM.

By abstracting this, your Java code remains portable and optimized for the specific performance characteristics of each database vendor.

2. Implementation using HQL (Hibernate Query Language)

HQL is the most readable and common method for static queries. In Hibernate 7, we use TypedQuery to ensure type safety.

import jakarta.persistence.TypedQuery;
import org.hibernate.Session;
import java.util.List;

/**
 * Fetches a specific page of products.
 * @param pageNumber The current page (1-based index)
 * @param pageSize The number of items per page
 */
public List<Product> getProducts(int pageNumber, int pageSize) {
    try (Session session = HibernateUtil.getSessionFactory().openSession()) {
        
        // 1. Create the HQL query
        // Always include an ORDER BY for deterministic results
        String hql = "FROM Product p ORDER BY p.createdAt DESC";
        TypedQuery<Product> query = session.createQuery(hql, Product.class);

        // 2. Set Pagination Parameters
        // Offset = (pageNumber - 1) * pageSize
        int firstResult = (pageNumber - 1) * pageSize;
        query.setFirstResult(firstResult); 
        query.setMaxResults(pageSize);

        // 3. Execute
        return query.getResultList();
    }
}

Output Example:

If pageNumber = 2 and pageSize = 10, Hibernate generates SQL similar to:

SELECT p.id, p.name, p.created_at 
FROM products p 
ORDER BY p.created_at DESC 
LIMIT 10 OFFSET 10;

3. Using Criteria API for Dynamic Pagination

For complex filtering—like an admin search panel with ten optional fields—string manipulation for HQL is dangerous and error-prone. The Criteria API provides a programmatic, type-safe alternative.

import jakarta.persistence.criteria.*;
import org.hibernate.query.Query;

public List<Product> findProductsDynamic(String category, int start, int limit) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    CriteriaBuilder cb = session.getCriteriaBuilder();
    CriteriaQuery<Product> cq = cb.createQuery(Product.class);
    Root<Product> root = cq.from(Product.class);

    // Dynamic Predicate
    if (category != null) {
        cq.where(cb.equal(root.get("category"), category));
    }
    
    cq.orderBy(cb.desc(root.get("price")));

    Query<Product> query = session.createQuery(cq);
    query.setFirstResult(start);
    query.setMaxResults(limit);

    return query.getResultList();
}

Pro Tip: For more on how to construct complex predicates, refer to the Jakarta Persistence Specification or the Hibernate User Guide.

4. Advanced Technique: Scrollable Results for Batch Processing

Sometimes you don’t want a “page” for a UI, but you need to process 1,000,000 rows in batches of 100 to avoid memory exhaustion (e.g., generating a CSV report). Using ScrollableResults is more efficient than standard pagination for this use case.

import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.query.Query;

public void processLargeDataSet() {
    try (Session session = HibernateUtil.getSessionFactory().openSession()) {
        Query<Product> query = session.createQuery("FROM Product", Product.class);
        
        // FORWARD_ONLY is memory efficient as it doesn't keep old rows in cache
        try (ScrollableResults<Product> scrollable = query.scroll(ScrollMode.FORWARD_ONLY)) {
            int count = 0;
            while (scrollable.next()) {
                Product p = scrollable.get();
                // Process product...
                
                // Clear session periodically to release memory
                if (++count % 50 == 0) {
                    session.clear();
                }
            }
        }
    }
}

5. Critical: The “Total Count” and UI Feedback

A user needs to see “Page 1 of 50.” To provide this, you must run a count query alongside your paginated query.

public PaginationResult<Product> getPaginatedProducts(int page, int size) {
    Long totalCount = session.createQuery("SELECT count(p) FROM Product p", Long.class)
                             .getSingleResult();
    
    List<Product> data = session.createQuery("FROM Product p ORDER BY p.id", Product.class)
                                .setFirstResult((page - 1) * size)
                                .setMaxResults(size)
                                .getResultList();
                                
    return new PaginationResult<>(data, totalCount, page, size);
}

6. Potential Pitfalls and Deep Technical Issues

A. The JOIN FETCH Memory Warning

One of the most dangerous traps in Hibernate pagination is using JOIN FETCH on a collection.

The Issue: If you fetch a User and their Orders using JOIN FETCH and apply pagination, Hibernate will fetch all records and perform pagination in Java memory.
The Log Warning: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!
The Fix: Use a two-step approach: Fetch the IDs of the parent entities with pagination first, then fetch the full objects using those IDs.

B. Deep Pagination (The Offset Problem)

When you use OFFSET 1000000, the database must still read all those rows to discard them.

Solution: Keyset Pagination (also known as the “Seek Method”). Instead of an offset, you use the last ID of the previous page: WHERE p.id > :last_id LIMIT 20.

C. Floating Result Sets

If an item is deleted from Page 1 while a user is navigating to Page 2, an item that was on Page 2 might “jump” to Page 1, causing the user to miss it or see duplicates. This is an inherent limitation of offset pagination; consider using immutable timestamps for sorting to mitigate this.

Frequently Asked Questions (FAQ)

1. Does Hibernate pagination happen in memory or in the database?

Hibernate pagination happens at the database level by default. It generates SQL specifically using keywords like LIMIT or OFFSET. It only switches to in-memory pagination if it detects a query (like a collection join fetch) that would produce incorrect results when limited by SQL.

2. How to handle pagination with many-to-many relationships in Hibernate?

To avoid the in-memory pagination trap, do not use JOIN FETCH with setMaxResults. Instead, use @BatchSize on your collection mapping or use a subselect strategy to load associated data efficiently after the main paginated list is retrieved.

3. What is the difference between setFirstResult() and setMaxResults()?

setFirstResult(int start) sets the row offset — the number of rows to skip before returning results (0 is the first row). setMaxResults(int limit) caps the number of rows returned. Together they define a window: setFirstResult((page-1) * size).setMaxResults(size) gives you page N of size M.

Q4: What is Keyset Pagination and when should I use it?

Keyset Pagination (also called the “seek method”) replaces OFFSET with a WHERE id > :lastSeenId clause. Instead of skipping rows, the database uses an index to start exactly where the last page ended. This is dramatically faster for deep pages (page 1000+) because OFFSET 10000 forces the database to scan and discard 10,000 rows even if it never returns them. Use Keyset Pagination for infinite scrolling feeds, cursor-based API responses (like GitHub’s pagination headers), or any UI where users rarely jump to arbitrary pages.

Q5: Why should I always include ORDER BY when paginating?

Without an ORDER BY, the database is free to return rows in any order — and that order can change between requests as the query planner chooses different execution plans or new rows are inserted. This causes “floating pages” where the same item appears on two consecutive pages or disappears entirely. Always sort by a stable, indexed column (typically id or created_at DESC) to guarantee deterministic pagination results.

Conclusion

Mastering pagination in Hibernate 7 is a non-negotiable requirement for any production-grade Java application. Offset-based pagination with setFirstResult/setMaxResults covers the vast majority of UI use cases; combine it with a stable ORDER BY and a count query for full page-number UI. For batch processing, use ScrollableResults with periodic session.clear(). For deep pagination on high-volume tables, adopt the Keyset method. Avoid JOIN FETCH with pagination limits — it silently falls back to in-memory pagination and will cause outages at scale. With these tools and guardrails in place, Hibernate 7 will handle any data volume your application encounters.

@ankurm