Implementing Pagination and Sorting in Spring Data JPA

Course: Advanced Java Training

Topic: Implementing Pagination and Sorting

Course Link: Advanced Java Training at JustAcademy

Demo Link: Register for a Free Course Demo

Website: Visit JustAcademy

1. Introduction to Pagination and Sorting

Pagination and sorting are important features in Java applications that retrieve data from databases. When a database contains thousands or millions of records, displaying every record on a single page can increase response time, consume memory, and make the user interface difficult to navigate.

Pagination divides a large result set into smaller pages, while sorting arranges records according to a specified field and direction. Spring Data JPA provides built-in support for these features through interfaces and classes such as Pageable, PageRequest, Page, and Sort.

For example, an employee management application may display 10 employees per page and sort them alphabetically by employee name.

Learning Objectives

• Understand pagination and sorting in database-driven applications.
• Learn the roles of Pageable, PageRequest, Page, Slice, and Sort.
• Configure repository methods for paginated queries.
• Implement pagination and sorting in Spring Boot REST APIs.
• Validate request parameters and handle invalid pagination input.
• Apply performance, security, and testing best practices.

2. What Is Pagination?

Pagination is the process of retrieving a limited number of records at a time rather than loading the entire result set. The client requests a particular page, and the application retrieves only the records required for that page.

Example

Suppose an employee table contains 100 employees and the application displays 10 employees per page.

In Spring Data JPA, page numbering is zero-based. Therefore, page index 0 represents the first page, page index 1 represents the second page, and so on.

Benefits of Pagination

• Improved response time: Retrieves a smaller subset of records per request.
• Reduced memory usage: Avoids loading unnecessarily large result sets into application memory.
• Better user experience: Supports page navigation and manageable result lists.
• Efficient APIs: Enables clients to request only the data they need.
• Scalability: Helps applications manage large datasets more effectively.

3. What Is Sorting?

Sorting arranges records according to one or more fields in ascending or descending order. In Spring Data JPA, sorting can be applied using the Sort class or included in a Pageable object.

Types of Sorting

4. Pagination and Sorting in Spring Data JPA

Spring Data JPA simplifies database access by generating repository implementations for common operations. When a repository extends JpaRepository, it can use pagination and sorting without requiring developers to manually write limit and offset logic for ordinary repository queries.

Important Classes and Interfaces

5. Setting Up the Spring Boot Project

Create a Spring Boot project with the following dependencies:

• Spring Web
• Spring Data JPA
• MySQL Driver or another supported database driver
• Validation

Example Maven dependencies:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>
</dependencies>

Use dependency versions managed by your chosen Spring Boot version. Configure the database connection in application.properties.

spring.datasource.url=jdbc:mysql://localhost:3306/company_db
spring.datasource.username=${DB_USERNAME}
spring.datasource.password=${DB_PASSWORD}
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Set the database credentials through environment variables or an appropriate secret-management mechanism. The ddl-auto=update setting is useful for selected development scenarios, but production database schema changes should generally be managed with migration tools.

6. Creating the Employee Entity

The entity represents the database table whose records will be paginated and sorted.

package com.example.demo.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String department;
    private double salary;

    public Employee() {
    }

    public Employee(String name, String department, double salary) {
        this.name = name;
        this.department = department;
        this.salary = salary;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }
}

Explanation:

• @Entity marks the class as a JPA entity.
• @Id identifies the primary key.
• @GeneratedValue configures automatic identifier generation.
• The remaining fields represent employee attributes that can be used for sorting.

The example uses jakarta.persistence, as used by modern Spring Boot versions. Older projects may use the javax.persistence package.

7. Creating a Repository with Pagination Support

Create an interface that extends JpaRepository. Spring Data JPA provides the repository implementation automatically.

package com.example.demo.repository;

import com.example.demo.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface EmployeeRepository
        extends JpaRepository<Employee, Long> {
}

The repository inherits methods such as:

• findAll(Pageable pageable) — retrieves a page of employees.
• findAll(Sort sort) — retrieves employees in a specified order.
• findById(Long id) — retrieves an employee by identifier.
• save(Employee employee) — saves an employee.
• count() — counts the available records.

8. Implementing Basic Pagination

Use PageRequest.of(pageNumber, pageSize) to create a pagination request. Remember that the page number starts at zero.

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;

Pageable pageable = PageRequest.of(0, 10);
Page<Employee> employeePage = employeeRepository.findAll(pageable);

employeePage.getContent().forEach(System.out::println);

How it works:

1. PageRequest.of(0, 10) requests the first page with up to 10 records.
2. The repository executes the paginated query.
3. getContent() returns the employee records for that page.
4. The Page object also provides metadata about the result set.

Pagination Metadata Methods

9. Implementing Sorting with the Sort Class

When the application needs to sort results without pagination, pass a Sort object to the repository.

Ascending Order

import org.springframework.data.domain.Sort;

Sort sortByName = Sort.by("name").ascending();
List<Employee> employees = employeeRepository.findAll(sortByName);

This retrieves employee records sorted by the name property in ascending order.

Descending Order

Sort sortBySalary = Sort.by("salary").descending();
List<Employee> employees = employeeRepository.findAll(sortBySalary);

This retrieves employee records with higher salaries before lower salaries.

Sorting by Multiple Fields

Sort sort = Sort.by("department").ascending()
        .and(Sort.by("salary").descending());

List<Employee> employees = employeeRepository.findAll(sort);

In this example, records are grouped by department in ascending order. Within each department, employees are sorted by salary in descending order.

Important: Sorting properties should correspond to valid entity property names or valid aliases supported by the query. Do not assume that database column names and Java entity property names are always identical.

10. Combining Pagination and Sorting

Pagination and sorting can be combined in one PageRequest. This is a common approach for listing screens and REST APIs.

Sort sort = Sort.by("name").ascending();
Pageable pageable = PageRequest.of(0, 10, sort);

Page<Employee> employeePage =
        employeeRepository.findAll(pageable);

This request retrieves the first 10 employees and sorts them by name in ascending order.

Combining Multiple Sort Conditions with Pagination

Sort sort = Sort.by("department").ascending()
        .and(Sort.by("salary").descending());

Pageable pageable = PageRequest.of(0, 10, sort);
Page<Employee> result = employeeRepository.findAll(pageable);

This technique allows the database to apply the sorting and pagination as part of the query rather than requiring the application to load all records and sort them in memory.

11. Returning Pagination Results from a REST API

A Spring Boot REST controller can accept page, size, and sort parameters from an HTTP request. The following example provides an endpoint for listing employees.

package com.example.demo.controller;

import com.example.demo.entity.Employee;
import com.example.demo.repository.EmployeeRepository;
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.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class EmployeeController {

    private final EmployeeRepository employeeRepository;

    public EmployeeController(EmployeeRepository employeeRepository) {
        this.employeeRepository = employeeRepository;
    }

    @GetMapping("/api/employees")
    public Page<Employee> getEmployees(
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "10") int size,
            @RequestParam(defaultValue = "name") String sortBy,
            @RequestParam(defaultValue = "asc") String direction) {

        Sort.Direction sortDirection =
                "desc".equalsIgnoreCase(direction)
                ? Sort.Direction.DESC
                : Sort.Direction.ASC;

        Pageable pageable = PageRequest.of(
                page,
                size,
                Sort.by(sortDirection, sortBy)
        );

        return employeeRepository.findAll(pageable);
    }
}

This controller demonstrates the basic pattern. In a production API, validate page and size values, restrict sorting to an allowlist of supported properties, and consider returning a dedicated response DTO rather than exposing persistence entities directly.

Example API Requests

GET /api/employees?page=0&size=10
GET /api/employees?page=1&size=10
GET /api/employees?page=0&size=5&sortBy=name&direction=asc
GET /api/employees?page=0&size=10&sortBy=salary&direction=desc

Note: These requests use the parameter names defined by the example controller. Spring Data's automatic Pageable argument resolver conventionally uses parameters such as page, size, and sort when configured and used directly in a controller.

12. Validating Pagination and Sorting Parameters

Request parameters originate outside the application and should not be trusted without validation. For example, a negative page number or a zero page size can cause an exception. An extremely large page size can also create unnecessary database and network load.

Example with Basic Validation

@GetMapping("/api/employees")
public Page<Employee> getEmployees(
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "10") int size,
        @RequestParam(defaultValue = "name") String sortBy,
        @RequestParam(defaultValue = "asc") String direction) {

    if (page < 0) {
        throw new IllegalArgumentException(
                "Page number cannot be negative");
    }

    if (size < 1 || size > 100) {
        throw new IllegalArgumentException(
                "Page size must be between 1 and 100");
    }

    Set<String> allowedFields =
            Set.of("id", "name", "department", "salary");

    if (!allowedFields.contains(sortBy)) {
        throw new IllegalArgumentException(
                "Unsupported sorting field");
    }

    if (!direction.equalsIgnoreCase("asc")
            && !direction.equalsIgnoreCase("desc")) {
        throw new IllegalArgumentException(
                "Direction must be asc or desc");
    }

    Sort.Direction sortDirection =
            direction.equalsIgnoreCase("desc")
            ? Sort.Direction.DESC
            : Sort.Direction.ASC;

    Pageable pageable = PageRequest.of(
            page, size, Sort.by(sortDirection, sortBy));

    return employeeRepository.findAll(pageable);
}

Include these imports when using the example:

import java.util.Set;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

For a complete application, replace generic IllegalArgumentException handling with a consistent API error response, such as a 400 Bad Request response created by a global exception handler using @RestControllerAdvice.

13. Understanding Page, Slice, and List

Spring Data JPA supports several result types for paginated repository queries. The appropriate choice depends on whether the application needs total-count information.

A Page commonly requires an additional count query to calculate the total number of matching records. Depending on the query and database, this can be expensive. A Slice can be more efficient when the application only needs to know whether more results are available. A repository method returning a List with a Pageable can retrieve a limited result range without creating the full page metadata.

Repository Examples

Page<Employee> findByDepartment(
        String department, Pageable pageable);

Slice<Employee> findByNameContainingIgnoreCase(
        String name, Pageable pageable);

List<Employee> findByDepartment(
        String department, Pageable pageable);

Choose one return type based on the requirements of each repository method. Do not combine a Pageable and a separate Sort parameter in the same standard query method because the Pageable already carries sorting information.

14. Using Pagination with Custom Repository Queries

Pagination can also be used with query methods that filter records. For example, the following method retrieves employees from a particular department.

import com.example.demo.entity.Employee;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository
        extends JpaRepository<Employee, Long> {

    Page<Employee> findByDepartment(
            String department, Pageable pageable);
}

Example service call:

Pageable pageable = PageRequest.of(
        0, 10, Sort.by("name").ascending());

Page<Employee> employees =
        employeeRepository.findByDepartment(
                "Engineering", pageable);

Using the @Query Annotation

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

@Query("SELECT e FROM Employee e WHERE e.salary > :minimumSalary")
Page<Employee> findEmployeesWithSalaryAbove(
        @Param("minimumSalary") double minimumSalary,
        Pageable pageable);

Spring Data JPA can apply pagination to supported JPQL queries. For complex native SQL queries, a suitable count query may need to be provided explicitly, depending on the query structure and Spring Data JPA version.

15. Creating a Custom Pagination Response DTO

Although returning a Page directly is convenient, an API may benefit from a stable response structure that separates application-facing data from Spring Data's internal response details.

package com.example.demo.dto;

import java.util.List;

public record PageResponse<T>(
        List<T> content,
        int page,
        int size,
        long totalElements,
        int totalPages,
        boolean first,
        boolean last,
        boolean hasNext,
        boolean hasPrevious) {
}

Convert a Spring Data page into this response object:

Page<Employee> result =
        employeeRepository.findAll(pageable);

PageResponse<Employee> response = new PageResponse<>(
        result.getContent(),
        result.getNumber(),
        result.getSize(),
        result.getTotalElements(),
        result.getTotalPages(),
        result.isFirst(),
        result.isLast(),
        result.hasNext(),
        result.hasPrevious()
);

This record syntax requires a Java version that supports records. In projects using an older Java version, create a regular DTO class with fields, constructors, and getters.

16. How Pagination Works at the Database Level

Pagination is generally implemented by limiting the number of records returned by a query and specifying how many matching records should be skipped. The exact SQL syntax depends on the database and its dialect.

A simplified SQL example is:

SELECT id, name, department, salary
FROM employee
ORDER BY name ASC
LIMIT 10 OFFSET 20;

This query orders the records by name, skips the first 20 records, and returns up to 10 records. It represents a page of size 10 starting at offset 20, which corresponds to the third page when page numbering begins at zero.

Spring Data JPA and the configured JPA provider typically generate the database-specific query from the pagination request. Developers should generally use the repository abstraction rather than manually building SQL limit and offset clauses for ordinary paginated queries.

17. Performance Optimization and Best Practices

• Set a maximum page size: Prevent requests from fetching excessively large result sets.
• Use appropriate indexes: Index columns frequently used for filtering and sorting, based on query patterns and execution plans.
• Use stable sorting: Add a unique field such as an identifier as a secondary sort key to make ordering deterministic when multiple records share the same value.
• Choose Page or Slice carefully: Use Page when total counts are needed and consider Slice when they are not.
• Use DTOs for API responses: Avoid exposing internal entity structure unnecessarily.
• Avoid in-memory pagination: Do not retrieve all database records and then apply Java list slicing for large datasets.
• Validate sorting fields: Accept only supported fields instead of passing arbitrary client input to sorting logic.
• Inspect generated SQL: Confirm that pagination and sorting are performed by the database.
• Consider deep-pagination costs: Large offsets may become expensive as the database skips more rows.
• Consider keyset pagination: For some large, continuously changing datasets, cursor-style retrieval based on indexed sort keys can be more efficient than large offsets.

18. Common Errors and Troubleshooting

19. Practical Exercise

Task: Build an employee listing API using Spring Boot and Spring Data JPA.

1. Create an Employee entity with ID, name, department, and salary fields.
2. Create an EmployeeRepository that extends JpaRepository.
3. Insert at least 30 employee records into the database.
4. Create an endpoint that accepts page number and page size.
5. Add sorting by employee name and salary.
6. Support both ascending and descending order.
7. Reject negative page numbers and page sizes outside an allowed range.
8. Return pagination metadata in the API response.
9. Test first-page, middle-page, and final-page requests using Postman.
10. Check the generated SQL and verify that the database returns only the requested page.

Expected API Examples

GET /api/employees?page=0&size=10&sortBy=name&direction=asc
GET /api/employees?page=1&size=5&sortBy=salary&direction=desc

20. Interview Questions and Answers

Q1. What is pagination?

Answer: Pagination divides a large set of records into smaller subsets and retrieves one subset per request. It helps manage response sizes and improve application usability.

Q2. What is sorting in Spring Data JPA?

Answer: Sorting arranges query results by one or more entity properties. Spring Data JPA provides the Sort class for specifying sorting properties and directions.

Q3. What is the purpose of Pageable?

Answer: Pageable represents pagination details, including the page index, page size, and sorting options.

Q4. What is PageRequest?

Answer: PageRequest is a concrete implementation of Pageable used to create pagination requests.

Q5. Why does Spring Data use zero-based page numbering?

Answer: The first page has index 0, the second page has index 1, and so on. This convention is used by PageRequest.

Q6. What is the difference between Page and Slice?

Answer: A Page provides total-element and total-page information, typically requiring a count query. A Slice provides information about the current subset and whether more results are available, without requiring total counts.

Q7. How do you sort results in descending order?

Answer: Use Sort.by("salary").descending() and pass the resulting sort to the repository or a PageRequest.

Q8. Can pagination and sorting be used together?

Answer: Yes. Create a PageRequest with a page index, page size, and Sort object, then pass it to the repository method.

Q9. Why should page size be restricted?

Answer: A maximum page size helps prevent oversized database queries and API responses that may consume excessive resources.

Q10. How can sorting be made deterministic?

Answer: Use a consistent sort order and add a unique field, such as the employee ID, as a secondary sort key when the primary property may contain duplicate values.

Q11. When might keyset pagination be useful?

Answer: Keyset pagination can be useful for large datasets where deep offset queries are expensive. It retrieves the next batch based on the last-seen sort key or keys and usually requires a suitable index and stable ordering.

Q12. Does a Page always require a count query?

Answer: A Page needs total-count information, and Spring Data JPA commonly obtains it through a count query. The actual query execution can vary based on the result and implementation. A Slice is an alternative when total counts are unnecessary.

21. Quick Revision Checklist

• Pagination retrieves a limited subset of records.
• Sorting orders records by specified properties.
• PageRequest.of(0, 10) requests the first page of 10 records.
• Pageable carries pagination and optional sorting information.
• Sort.by("name").ascending() sorts by name in ascending order.
• Sort.by("salary").descending() sorts by salary in descending order.
• Page provides total-count metadata; Slice indicates whether more results are available.
• Validate page number, page size, sorting direction, and sorting fields.
• Use stable ordering, suitable indexes, and DTOs where appropriate.
• Test both normal requests and invalid parameter values.

22. Conclusion

Pagination and sorting are essential techniques for building responsive and scalable Spring Boot applications. Spring Data JPA simplifies their implementation through Pageable, PageRequest, Page, Slice, and Sort. By combining repository methods with validated REST parameters, developers can deliver organized database results without retrieving every record at once.

For deeper learning, explore the Advanced Java Training course, visit the JustAcademy website, or register for a course demo.

23. Further Reading

• Spring Data JPA Query Methods and Sorting Documentation
• Spring Data PageRequest API Reference
• Spring Data JPA Project Overview