Efficient Pagination in Hibernate with setFirstResult and setMaxResults

Problem: Loading Too Much Data at Once

When a Hibernate query returns thousands of rows, the ORM materializes each row as an entity object. If the application tries to load the entire list into memory, the JVM can quickly hit an OutOfMemoryError. This is especially common in reporting screens, batch jobs, or APIs that need to stream data to a client.

Thesis: Use Database‑Level Limits for Predictable, Memory‑Friendly Pagination

Hibernate delegates the setFirstResult (offset) and setMaxResults (limit) calls to the underlying JDBC driver, which translates them into database‑specific LIMIT/OFFSET clauses. By fetching only the rows needed for a single page, you keep memory usage bounded and let the database do the filtering work.

Worked Example: Paginating a User Entity

Assume a simple User entity mapped to a table with columns id, username, and email. The service below returns a page of users and also calculates the total number of pages based on a separate count query.

@Service
@RequiredArgsConstructor
public class UserPaginationService {

    private final EntityManager entityManager;

    /**
     * Returns a page of User entities.
     * @param pageNumber zero‑based index of the page to fetch
     * @param pageSize   maximum number of entities per page
     * @return a PageResult containing the list and total pages
     */
    public PageResult fetchUsersPage(int pageNumber, int pageSize) {
        // 1️⃣ Build the query that selects the entities
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery cq = cb.createQuery(User.class);
        Root root = cq.from(User.class);
        cq.select(root);
        // Optional: add ordering for stable pagination
        cq.orderBy(cb.asc(root.get("id")));

        TypedQuery query = entityManager.createQuery(cq);
        query.setFirstResult(pageNumber * pageSize);
        query.setMaxResults(pageSize);

        List page = query.getResultList();

        // 2️⃣ Count total rows for page calculation
        CriteriaQuery countQuery = cb.createQuery(Long.class);
        Root countRoot = countQuery.from(User.class);
        countQuery.select(cb.count(countRoot));
        Long total = entityManager.createQuery(countQuery).getSingleResult();

        int totalPages = (int) Math.ceil((double) total / pageSize);
        return new PageResult<>(page, totalPages);
    }
}

/** Simple holder for page data */
class PageResult {
    private final List content;
    private final int totalPages;

    PageResult(List content, int totalPages) {
        this.content = content;
        this.totalPages = totalPages;
    }

    public List getContent() { return content; }
    public int getTotalPages() { return totalPages; }
}

When you enable Hibernate’s SQL logging (show_sql=true), the generated statement for the first page (pageNumber=0, pageSize=50) looks like:

select user0_.id as id1_0_, user0_.username as username2_0_, user0_.email as email3_0_
from users user0_
order by user0_.id asc
limit ? offset ?

The database applies the limit and offset, so Hibernate only materializes the 50 rows requested.

Trade‑offs and Limitations

• Extra round‑trips: Each page requires a separate query (data + count). For very high‑frequency APIs this adds latency.
• Inconsistent views: If rows are inserted or deleted between page fetches, a user could see duplicates or miss items. This is the classic limitation of offset‑based pagination.
• Join duplication: When the query includes fetch joins, the same root entity may appear multiple rows, causing the limit to return fewer distinct entities than expected. Use DISTINCT or adjust the query to avoid Cartesian products.
• Dialect differences: Not all databases implement LIMIT/OFFSET identically (e.g., older Oracle versions require ROWNUM tricks). Verify the generated SQL with your target dialect.

How to Verify the Implementation

1. Start an in‑memory H2 database (or your test container) and create a users table.
2. Insert a known number of rows, e.g., 250 User records.
3. Run fetchUsersPage with a page size of 50 and iterate through all pages.
4. Assert that each page (except possibly the last) contains exactly 50 distinct entities and that the union of all pages equals the original 250 rows.
5. Check the console output (with show_sql=true) to confirm each query includes a limit and clause matching the requested page.

If the assertions pass, you have concrete evidence that the pagination works as expected for your data set and dialect.

Actionable Closing

Offset‑based pagination with setFirstResult and setMaxResults is a straightforward, widely supported technique for keeping memory usage low in Hibernate applications. Use it when:

• Data changes relatively slowly between page requests, or you can tolerate occasional inconsistencies.
• You need a quick solution without adding complex cursor or keyset logic.
• Your target database has reliable LIMIT/OFFSET support (test with your dialect).

For workloads demanding strong consistency or ultra‑low latency, consider keyset pagination (seek method) or Hibernate’s ScrollableResults as alternatives. Start with the simple limit/offset approach, verify with the steps above, and evolve only when the trade‑offs become problematic.