Pagination & Sorting with Spring Data JPA

Business Scenario

In this lab, we will enhance the existing Medicare Doctor Search API by implementing pagination and sorting alongside the filtering features developed in the previous lab.

Pre-Lab Preparation

Phase 7 — Pagination & Sorting

What problem are we solving?

Currently:

Module:

1) Additional Components in Project

GET /api/doctors

could eventually return:

10 doctors
100 doctors
1,000 doctors
50,000 doctors

Returning everything is inefficient.

Database - 50,000 Doctors
Request page=0
size=6 -- Only 6 Doctors returned

Instead :

Then: page=1 returns the next 6.

Suppose we have: 25 doctors and: size = 6

Understand Pagination

Then:

Page 0 → Doctors 1–6
Page 1 → Doctors 7–12
Page 2 → Doctors 13–18
Page 3 → Doctors 19–24
Page 4 → Doctor 25

Understand the Pagination Classes

We'll mainly use:

Pageable

Represents:

Which page?
How many records?
How should records be sorted?

PageRequest

Creates that pagination request.

Example: PageRequest.of(0, 6)

means: Page = 0 , Size = 6

Page<DoctorProfile>

Doctor records
+
Current page
+
Page size
+
Total elements
+
Total pages
+
Whether last page

contains:

Add Pagination Response DTO

We don't want the frontend depending directly on Spring's internal Page response structure.

dto/
└── DoctorPageResponse.java

So this is another justified DTO.

Create :

package com.medicare.dto;
import java.util.List;

public class DoctorPageResponse {

    private List<DoctorResponse> doctors;
    private int pageNumber;
    private int pageSize;
    private long totalElements;
    private int totalPages;
    private boolean last;

    public DoctorPageResponse(
            List<DoctorResponse> doctors,
            int pageNumber,
            int pageSize,
            long totalElements,
            int totalPages,
            boolean last) {

        this.doctors = doctors;
        this.pageNumber = pageNumber;
        this.pageSize = pageSize;
        this.totalElements = totalElements;
        this.totalPages = totalPages;
        this.last = last;
    }

    public List<DoctorResponse> getDoctors() {
        return doctors;
    }

    public int getPageNumber() {
        return pageNumber;
    }

    public int getPageSize() {
        return pageSize;
    }

    public long getTotalElements() {
        return totalElements;
    }

    public int getTotalPages() {
        return totalPages;
    }

    public boolean isLast() {
        return last;
    }
}

Update DoctorProfileRepository

Previously our method returned: List<DoctorProfile>

Now change it to: Page<DoctorProfile>

package com.medicare.repository;
import java.math.BigDecimal;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import com.medicare.entity.DoctorProfile;

public interface DoctorProfileRepository extends JpaRepository<DoctorProfile, Long> {

    boolean existsByUserId(Long userId);

    @Query(""" SELECT d
            FROM DoctorProfile d
            JOIN d.user u
            WHERE
                (:specialization IS NULL
                    OR LOWER(d.specialization) = LOWER(:specialization))
            AND
                (:maxFee IS NULL
                    OR d.consultationFee <= :maxFee)
            AND
                (:name IS NULL
                    OR LOWER(u.name)
                    LIKE CONCAT(CONCAT('%', LOWER(:name)), '%'))
            """)

  LIKE CONCAT(CONCAT('%', LOWER(:name)), '%'))
            """)
    Page<DoctorProfile> searchDoctors(
            @Param("specialization")
            String specialization,

            @Param("maxFee")
            BigDecimal maxFee,

            @Param("name")
            String name,

            Pageable pageable
    );
}

How Pageable Works With Repository

Our service will create:

Pageable pageable = PageRequest.of(page,size,sort

Then pass it:

doctorRepository.searchDoctors(
        specialization,maxFee,name,pageable);

Spring/Hibernate handles:

Filtering
+
Sorting
+
Limit
+
Offset
+
Total count

Decide Our Sorting Options

We shouldn't allow random frontend values such as:?sortBy=password

or: ?sortBy=somethingThatDoesNotExist

name
specialization
experienceYears
consultationFee

So we will allow only:

Update DoctorService

Keep your existing methods.

Keep your existing methods.Replace the existing searchDoctors() method with this version and add the helper methods below.

public DoctorPageResponse searchDoctors(
        String specialization,
        BigDecimal maxFee,
        String name,
        int page,
        int size,
        String sortBy,
        String direction) {

    specialization = normalizeText(specialization);
    name = normalizeText(name);

    validatePagination(page,size);

    if (maxFee != null && maxFee.compareTo(BigDecimal.ZERO) <= 0) {

        throw new IllegalArgumentException(
        "Maximum consultation fee must be greater than zero"
        );
    }

    String sortField = resolveSortField(sortBy);

    Sort.Direction sortDirection = resolveSortDirection(direction);

    Sort sort = Sort.by(sortDirection,sortField);

    Pageable pageable = PageRequest.of(page,size,sort);

    Page<DoctorProfile> doctorPage = doctorRepository.searchDoctors(
                    specialization, maxFee, name, pageable);

    List<DoctorResponse> doctorResponses = new ArrayList<>();

    for (DoctorProfile doctor : doctorPage.getContent()) {

        doctorResponses.add( mapToDoctorResponse(doctor));
    }

    return new DoctorPageResponse(
            doctorResponses,
            doctorPage.getNumber(),
            doctorPage.getSize(),
            doctorPage.getTotalElements(),
            doctorPage.getTotalPages(),
            doctorPage.isLast()
    );
}

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 com.medicare.dto.DoctorPageResponse;

Add imports:

Add Pagination Validation

Inside DoctorService add:

private void validatePagination( int page, int size) {

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

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

Why limit it to: 50 ?

Because otherwise somebody could request: ?size=1000000 which defeats the entire purpose of pagination.

For this project: 1–50 is plenty.

Add Sort Field Validation

private String resolveSortField(
        String sortBy) {

    if (sortBy == null || sortBy.isBlank()) {

        return "consultationFee";
    }

    return switch (sortBy) {

        case "name" -> "user.name";
        case "specialization" -> "specialization";
        case "experienceYears" -> "experienceYears";
        case "consultationFee" -> "consultationFee";
        default -> throw new IllegalArgumentException("Invalid sort field");
    };
}

Validate Sorting Direction

private Sort.Direction resolveSortDirection(String direction) {

    if (direction == null || direction.isBlank() || direction.equalsIgnoreCase("asc")) {
    
        return Sort.Direction.ASC;
    }
    
    if (direction.equalsIgnoreCase("desc")) {

           return Sort.Direction.DESC; 
    }

    throw new IllegalArgumentException( "Sort direction must be asc or desc");
}

Therefore:

asc  
ASC  
Asc  
desc 
DESC

up   
down 
abc

Updated DoctorService Imports

Your relevant imports should now include:

Update DoctorController

Change our GET /api/doctors method.

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
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;

Previously:

@GetMapping
public ResponseEntity<List<DoctorResponse>>searchDoctors(...)

@GetMapping
public ResponseEntity<DoctorPageResponse> searchDoctors(

                @RequestParam(required = false)
                String specialization,

                @RequestParam(required = false)
                BigDecimal maxFee,

                @RequestParam(required = false)
                String name,

                @RequestParam(defaultValue = "0")
                int page,

                @RequestParam(defaultValue = "6")
                int size,

                @RequestParam(defaultValue = "consultationFee")
                String sortBy,

                @RequestParam(defaultValue = "asc")
                String direction) {

Now use:

    DoctorPageResponse response = doctorService.searchDoctors(
                    specialization, maxFee, name, page, size, sortBy, direction
            );

    return ResponseEntity.ok(response);
}

Add: import com.medicare.dto.DoctorPageResponse;

Our Default Request

Change our GET /api/doctors method.

page = 0
size = 6
sortBy = consultationFee
direction = asc

Test Basic Pagination

Change our GET /api/doctors method.

GE - http://localhost:8080/api/doctors?page=0&size=3

Postman :

Suppose we have 8 doctors, Expected response approximately:

{
    "doctors": [
        {
            "id": 2,
            "name": "Dr. Rohan Mehta",
            "specialization": "Dentist",

            "experienceYears": 5,
            "consultationFee": 700.00,
            "photoUrl": "https://example.com/rohan.jpg"
        },
        {
            "id": 3,
            "name": "Dr. Priya Nair",
            "specialization": "General Physician",
            "experienceYears": 10,
            "consultationFee": 900.00,
            "photoUrl": "https://example.com/priya.jpg"
        },
        {
            "id": 5,
            "name": "Dr. Sneha Kulkarni",
            "specialization": "Dermatologist",
            "experienceYears": 7,
            "consultationFee": 1000.00,
            "photoUrl": "https://example.com/sneha.jpg"

        }
    ],
    "pageNumber": 0,
    "pageSize": 3,
    "totalElements": 8,
    "totalPages": 3,
    "last": false
}

Request Page 2

GET - http://localhost:8080/api/doctors?page=0&size=3

Postman :

Response:

{
    "doctors": [
        ...
    ],
    "pageNumber": 1,
    "pageSize": 3,
    "totalElements": 8,
    "totalPages": 3,
    "last": false
}

Last Page

GET /api/doctors?page=2&size=3

Postman :

{
    "pageNumber": 2,
    "pageSize": 3,
    "totalElements": 8,
    "totalPages": 3,
    "last": true
}

Create DoctorResponse

GET - /api/doctors?sortBy=consultationFee&direction=asc

Ascending:

Result:

GET - /api/doctors?sortBy=consultationFee&direction=desc

Descending:

Result:

Sort by Experience

GET /api/doctors?sortBy=experienceYears&direction=desc

Sort by Doctor Name

GET /api/doctors?sortBy=name&direction=asc

Filters + Pagination Together

GET /api/doctors?specialization=Cardiologist&page=0&size=6

Conceptually:

Find Cardiologists
      ↓
Paginate Results
      ↓
Return First 6

Filters + Sorting + Pagination

GET /api/doctors?specialization=Cardiologist&maxFee=1500&name=ananya&page=0&size=6&sortBy=consultationFee&direction=asc

Test Invalid Page

GET /api/doctors?page=-1

Expected:

400 BAD REQUEST

Test Invalid Page Size

GET /api/doctors?size=0

Expected:

400 BAD REQUEST

Because our accepted range is: 1–50

Test Invalid Sort Property

GET /api/doctors?sortBy=randomField

Expected:

400 BAD REQUEST

Invalid sort field

Test Invalid Direction

GET /api/doctors?direction=up

Expected:

400 BAD REQUEST

Sort direction must be asc or desc

What If Page Doesn't Exist?

Suppose there are: 8 doctors but you request:

GET /api/doctors?page=20&size=6

I recommend returning: 200 OK with

{
    "doctors": [],
    "pageNumber": 20,
    "pageSize": 6,
    "totalElements": 8,
    "totalPages": 2,
    "last": true
}

We don't need to turn that into an exception.

The collection exists; this particular page simply contains no results.

We are done with this lab. The latest source code has been uploaded to GitHub. You can access the latest commit using the link below:

Github_repository_link

Great job!

You have successfully implemented pagination and sorting in the Medicare Doctor Search API using Spring Data JPA.

Checkpoint

Git Push

git push origin branchName

Next-Lab Preparation

Module:

1) Mastering Springboot Security

SpringBoot Lab - 8

By Content ITV

SpringBoot Lab - 8

Content ITV PRO

This is Itvedant Content department

Pagination & Sorting with Spring Data JPA

SpringBoot Lab - 8

SpringBoot Lab - 8

Content ITV PRO

More from Content ITV