How to Paginate and Sort a Spring Boot Rest API with SQL database

Pagination is often a requirement when our application handles a large dataset and we want to retrieve and return smaller chunks to the user.

Server-side pagination provides stability and scalability, so it is the preferable option to handle large amounts of data.

In this tutorial, we will learn how to easily guarantee that the data returned by our Spring Rest endpoint is already paginated and sorted, as well as how to retrieve the data from our SQL database already paginated, by using the Spring Data JPA.
We will also demonstrate how to configure a Rest endpoint to offer our client applications the freedom to query and display the data they prefer.

Dependencies required:

implementation("org.springframework.boot:spring-boot-starter-web")
implementation("org.springframework.boot:spring-boot-starter-data-jpa")
implementation("org.springframework.boot:spring-boot-starter-jdbc")

Setup Jpa repository

First, we start by creating our Hospital database entity.

@Entity
@NoArgsConstructor
@Getter
@Setter
@Table(name = "hospital")
public class Hospital {

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

    @Column
    private String name;

    @Column(name = "phone_number")
    private String phoneNumber;
}

To retrieve our data, we will configure our JPA repository:

public interface HospitalRepository extends JpaRepository<Hospital, Long> {
}

And finally, the HospitalDto that our Rest endpoint will return:

@NoArgsConstructor
@Setter
@Getter
public class HospitalDto {
    private Long id;
    private String name;
    private String phoneNumber;
}

Setup Spring Pageable API for Pagination

The Spring Pageable API can be configured with 3 parameters:

1. page — zero-based page index
2. size — the size of the page to be returned
3. sort — Spring Sort API which can be configured with the sort direction, and the database field that will be used for sorting

So, we should configure those 4 fields with default values on our rest endpoint, create that Pageable object, and then return our Page:

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

...

@GetMapping
public Page<HospitalDto> findAllByPage(@RequestParam(defaultValue = "0") int page,
                                       @RequestParam(defaultValue = "2") int sizePerPage,
                                       @RequestParam(defaultValue = "ID") SortField sortField,
                                       @RequestParam(defaultValue = "DESC") Sort.Direction sortDirection) {
    Pageable pageable = PageRequest.of(page, sizePerPage, sortDirection, sortField.getDatabaseFieldName());
    return hospitalService.findAllByPage(pageable);
}

You probably noticed the SortField object. This is a custom enum to limit the allowed values that we accept for sorting. The string that we need to pass to the Sort object is the name of the field in our Entity object. For example, we should pass the string phoneNumber, the name of the field in the Java object, instead of phone_number, the name of the field in the database table:

@Getter
@AllArgsConstructor
public enum SortField {
    ID("id"),
    NAME("name"),
    PHONE_NUMBER("phoneNumber");

    private final String databaseFieldName;
}

JPA Data Repository with Pagination

So, the finalize our tutorial we just need to complete our service layer, which will retrieve the page from the database and map our database object to a DTO:

@Service
public class HospitalServiceImpl implements HospitalService {

    private final HospitalRepository hospitalRepository;
    private final HospitalMapper hospitalMapper;

    @Autowired
    public HospitalServiceImpl(HospitalRepository hospitalRepository, HospitalMapper hospitalMapper) {
        this.hospitalRepository = hospitalRepository;
        this.hospitalMapper = hospitalMapper;
    }

    @Override
    public Page<HospitalDto> findAllByPage(Pageable pageable) {
        return hospitalRepository.findAll(pageable)
                                 .map(hospitalMapper::toDto);
    }
}

The JPA repository supports out of the box the find all method with the Pageable object, so we just need to do

hospitalRepository.findAll(pageable)

And finally, we can simply use a mapping class to map the Hospital object to HospitalDto:

hospitalRepository.findAll(pageable)
                  .map(hospitalMapper::toDto);

In this example, I am using the MapStruct library to set up the mapping class. This open-source is very easy to use and set up. For this tutorial, we just need to create:

import org.mapstruct.Mapper;
import org.mapstruct.ReportingPolicy;
import org.myduque.data.dto.HospitalDto;
import org.myduque.data.model.Hospital;

@Mapper(componentModel = "spring", unmappedTargetPolicy = ReportingPolicy.ERROR)
public interface HospitalMapper {
    HospitalDto toDto(Hospital hospital);
}

In case you never came across with MapStruct, you can easily add it to your dependencies:

implementation("org.mapstruct:mapstruct:1.5.3.Final")
annotationProcessor("org.mapstruct:mapstruct-processor:1.5.3.Final")

And you can read my article Mapstruct Tutorial Guide with Spring to learn how to extract the most out of this great library.

Conclusion

We just saw how easy it is to implement the useful pagination functionality in our Rest API and JPA repositories.