Pagination in JPA: Efficient Data Retrieval with setFirstResult and setMaxResults

When working with large datasets in enterprise applications, fetching all records at once is inefficient and often unnecessary. Instead, we typically need data in chunks (pages) — for example, displaying 20 results per page in a web application.

JPA provides built-in support for pagination using the methods:

• setFirstResult(int startPosition) → Defines the offset (starting row).
• setMaxResults(int maxResult) → Defines the limit (maximum number of rows to fetch).

Together, these methods enable developers to fetch only the required subset of data, improving performance and scalability.

---

Entity Setup Example

import jakarta.persistence.*;

@Entity
@Table(name = "employees")
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String department;
    private Double salary;

    // getters and setters
}

---

Pagination with JPQL

Example: Fetch employees page-wise

int pageNumber = 2;
int pageSize = 5;

List<Employee> employees = entityManager
    .createQuery("SELECT e FROM Employee e ORDER BY e.id", Employee.class)
    .setFirstResult((pageNumber - 1) * pageSize)
    .setMaxResults(pageSize)
    .getResultList();

This will fetch employees from row 6 to 10.

Equivalent SQL:

SELECT * FROM employees ORDER BY id LIMIT 5 OFFSET 5;

---

Pagination with Criteria API

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
Root<Employee> root = cq.from(Employee.class);
cq.select(root).orderBy(cb.asc(root.get("id")));

List<Employee> employees = entityManager.createQuery(cq)
    .setFirstResult(0)
    .setMaxResults(10)
    .getResultList();

This retrieves the first 10 employees.

---

CRUD Operations Context

Create

Employee emp = new Employee();
emp.setName("Alice");
emp.setDepartment("Finance");
emp.setSalary(85000.0);

entityManager.getTransaction().begin();
entityManager.persist(emp);
entityManager.getTransaction().commit();

Read (with Pagination)

List<Employee> employees = entityManager
    .createQuery("SELECT e FROM Employee e", Employee.class)
    .setFirstResult(0)
    .setMaxResults(20)
    .getResultList();

Update

entityManager.getTransaction().begin();
emp.setSalary(95000.0);
entityManager.merge(emp);
entityManager.getTransaction().commit();

Delete

entityManager.getTransaction().begin();
entityManager.remove(emp);
entityManager.getTransaction().commit();

---

Real-World Use Cases

• Web Applications → Displaying search results with pagination.
• APIs → Providing pageable endpoints for clients.
• Analytics Dashboards → Loading reports in chunks to improve performance.

---

Performance Considerations

• Always use ORDER BY for consistent results.
• Use indexes on columns used in pagination for speed.
• For very large datasets, prefer keyset pagination (based on last seen ID).

---

Anti-Patterns and Pitfalls

• Fetching without ordering → results may vary across pages.
• Using setFirstResult with extremely high offsets → degrades performance in large tables.
• Ignoring caching → frequent pagination queries may cause repeated database hits.

---

Best Practices

• Use pagination with consistent ordering.
• For APIs, return both data and metadata (page number, total count).
• Consider keyset pagination for infinite scrolling scenarios.
• Combine pagination with DTOs to fetch only necessary fields.

---

📌 JPA Version Notes

• JPA 2.0 → Introduced Criteria API with pagination support.
• JPA 2.1 → Added stored procedures, entity graphs.
• JPA 2.2 → Enhanced Java 8 Date/Time support.
• Jakarta Persistence (EE 9/10/11) → Package renamed (javax.persistence → jakarta.persistence).

---

Conclusion and Key Takeaways

• Pagination in JPA is achieved using setFirstResult and setMaxResults.
• It’s essential for performance and scalability in enterprise systems.
• Combine pagination with sorting, caching, and DTOs for production-ready solutions.
• Prefer keyset pagination for large datasets to avoid performance bottlenecks.

---

FAQ: Expert-Level Questions

1. What’s the difference between JPA and Hibernate?
JPA is a specification; Hibernate is one of its implementations.

2. How does JPA handle the persistence context?
It works like a classroom attendance register, tracking managed entities.

3. What are the drawbacks of eager fetching in JPA?
It loads unnecessary data, slowing down pagination queries.

4. How can I solve the N+1 select problem with JPA?
Use JOIN FETCH, entity graphs, or batch fetching.

5. Can I use JPA without Hibernate?
Yes, alternatives include EclipseLink, OpenJPA, and DataNucleus.

6. What’s the best strategy for inheritance mapping in JPA?
Options: SINGLE_TABLE, JOINED, or TABLE_PER_CLASS.

7. How does JPA handle composite keys?
By using @IdClass or @EmbeddedId annotations.

8. What changes with Jakarta Persistence?
The package name changed to jakarta.persistence.

9. Is JPA suitable for microservices?
Yes, but lighter frameworks may be better for high-performance microservices.

10. When should I avoid using JPA?
For ultra-high-performance, analytics-heavy, or batch-oriented applications.

---