Pagination is the process of dividing large sets of data into manageable “pages” rather than loading everything at once. It improves performance and user experience, especially in web apps and REST APIs.
Sorting is the process of arranging data in a specific order, either ascending or descending, based on a field or multiple fields. It is commonly used in querying databases to improve the user experience and ensure that data is displayed in an organized, predictable manner.
Why Pagination?
Prevents memory overload from loading thousands of records.
Enhances performance by querying only what is needed.
Enables efficient data browsing on front-end UIs.
Reduces response size in API results.
Why Sorting?
Enhances User Experience: Sorting data allows users to find relevant information easily (e.g., sorting by date, price, or name).
Improves Readability: Organized data is easier to read and interpret, especially for large datasets.
Optimizes Data Processing: In some cases, sorted data can improve the performance of certain queries or algorithms.
Supports Better Data Analysis: Sorting helps in finding trends and making sense of large volumes of data by looking at ordered sequences.
Characteristics
Feature
Description
Page Numbering
Typically zero-based (Page 0 = first page).
Page Size
Defines how many records per page (e.g., 10, 20, 50).
Sorting
Can be applied along with pagination.
Total Pages and Elements
Useful metadata returned by pagination logic.
Navigability
Allows going to next, previous, first, or last pages.
Pagination in JPA
JPA provides pagination using the javax.persistence.Query API via:
import jakarta.persistence.*;
import java.util.List;
// Entity class
@Entity
@Table(name = "employees")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String firstName;
private String lastName;
private String department;
// Constructors, getters, setters
public Employee() {}
public Employee(String firstName, String lastName, String department) {
this.firstName = firstName;
this.lastName = lastName;
this.department = department;
}
// Getters and setters omitted for brevity
}
import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import java.util.List;
public class EmployeeRepository {
private final EntityManager entityManager;
public EmployeeRepository(EntityManager entityManager) {
this.entityManager = entityManager;
}
public List<Employee> findPaginatedEmployees(int page, int pageSize) {
int offset = page * pageSize;
Query query = entityManager.createQuery("SELECT e FROM Employee e");
query.setFirstResult(offset); // starting point
query.setMaxResults(pageSize); // number of records to fetch
return query.getResultList();
}
}
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
@Query("SELECT e FROM Employee e WHERE e.department = :dept")
Page<Employee> findByDepartment(@Param("dept") String dept, Pageable pageable);
}
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class EmployeeService {
@Autowired
private EmployeeRepository employeeRepository;
public List<Employee> getPaginatedEmployeesByDepartment(String department, int page, int size) {
Pageable pageable = PageRequest.of(page, size); // no sorting
Page<Employee> resultPage = employeeRepository.findByDepartment(department, pageable);
return resultPage.getContent();
}
public List<Employee> getSortedEmployeesByDepartment(String department, int page, int size) {
Pageable pageable = PageRequest.of(page, size, Sort.by("salary").descending());
Page<Employee> resultPage = employeeRepository.findByDepartment(department, pageable);
return resultPage.getContent();
}
}
Spring Data automatically applies LIMIT and OFFSET.
Native Query Pagination (JPA + Spring Data)
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
@Query(
value = "SELECT * FROM employee WHERE department = :dept",
countQuery = "SELECT count(*) FROM employee WHERE department = :dept",
nativeQuery = true
)
Page<Employee> findByDeptNative(@Param("dept") String dept, Pageable pageable);
}
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class EmployeeService {
@Autowired
private EmployeeRepository employeeRepository;
public List<Employee> getNativePaginatedEmployeesByDepartment(String dept, int page, int size) {
Pageable pageable = PageRequest.of(page, size); // No sort
Page<Employee> resultPage = employeeRepository.findByDeptNative(dept, pageable);
return resultPage.getContent();
}
public List<Employee> getNativeSortedEmployeesByDepartment(String dept, int page, int size) {
Pageable pageable = PageRequest.of(page, size, Sort.by("salary").descending());
Page<Employee> resultPage = employeeRepository.findByDeptNative(dept, pageable);
return resultPage.getContent();
}
}
We must manually supply a countQuery when using native SQL.
Pagination with Criteria API
The Criteria API in JPA is a programmatic way to build dynamic, type-safe queries and it also supports pagination using the same concept as regular JPQL: setFirstResult() and setMaxResults() on the TypedQuery.
Recommended Approach for Complex Queries with Pagination and Sorting
1. Use JPQL for Moderate Complexity Queries
If our query can be expressed in JPQL (and we don’t need advanced SQL-specific features), JPQL is the most straightforward option. It’s simple to write, type-safe, and integrates seamlessly with Spring Data JPA.
2. Use Criteria API for Highly Dynamic Queries
If we need to dynamically build queries or work with complex conditions and filters at runtime, Criteria API is the best choice. It provides a lot of flexibility and is great for queries that depend on runtime conditions.
3. Use Native SQL for Performance-Critical Queries
When we need raw performance or need to use specific database features (e.g., advanced SQL functions, custom DB optimizations), then native SQL is ideal. This is particularly useful for very complex, non-standard queries.
4. Use Spring Data JPA Derived Queries for Simple Cases
For simple queries where no joins or complex logic are needed, Spring Data JPA’s derived query methods are the easiest and most convenient solution.
5. Use Named Queries for Reusable Static Queries
If we have predefined queries that will be used repeatedly across your app, consider using named queries. They can be more efficient and maintainable for repeated use.
