PagingAndSorting Repository

The PagingAndSortingRepository provides methods to retrieve entities using the pagination and sorting abstraction. It provides two methods.

  1. 1. Page findAll(Pageable pageable)- returns a Page of entities meeting the paging restriction provided in the Pageable object.
  2. 2. Iterable findAll(Sort sort)- eturns all entities sorted by the given options. No paging is applied here.
We can add more methods that accept Pageable and Sort as parameters for any custom requirements. 

                      List<CustomerModel> findCustomersByEmailAddress(String emailAddress, Pageable pageable);

Accepting Page and Sort Parameters

Generally, paging and sorting parameters are optional and thus part of the request URL as query parameters. If any API supports paging and sorting, ALWAYS provide default values to these parameters – to be used when the client does not choose to specify any paging or sorting preferences.

we are accepting paging and sorting parameters using the following query parameters:

  1. 1. pageNo: The current page number.
  2. 2. pageSize: Number of records on each page.
  3. 3. sortBy: Field names if any sorting has to be applied.

To perform pagination and/or sorting, we must create org.springframework.data.domain.Pageable or org.springframework.data.domain.Sort instances are passed to the findAll() method.

Lets see an example

First, we need to create a simple SprintBoot and SprintDataJPA applicaiton using MySql, which can be created with the help of following tutorial Customer Registration on top of this we need to make changes to fetch data using Native and JPQL queries.

First create new PagingAndSorting Repository 


                    package com.sb.sdjpa.crud.repository;

                    import com.sb.sdjpa.crud.model.CustomerModel;
                    import org.springframework.data.repository.PagingAndSortingRepository;

                    public interface PaginationRepository extends PagingAndSortingRepository<CustomerModel, Long> {
                    }

Introduce below new endpoints in customer controller.

  1. 1. /api/v1/customer/getCustomer/paging
  2. 2. /api/v1/customer/getCustomer/paging/sorting
  3. 3. /api/v1/customer/getCustomer/sorting


                    /**
                    * Load the customer using paging and sorting algorithm.
                    *
                    * @param pageNo page number
                    * @param pageSize page size
                    * @return response entity object
                    */
                   @GetMapping("/getCustomer/paging")
                   public ResponseEntity<APIResponse> getCustomersUsingPagination(
                           @RequestParam(defaultValue = "0") int pageNo,
                           @RequestParam(defaultValue = "5") int pageSize
                   ) {
                       return customerService.getCustomersUsingPagination(pageNo, pageSize);
                   }
               
                   /**
                    * Load the customer using paging and sorting algorithm.
                    *
                    * @param pageNo page number
                    * @param pageSize page size
                    * @param sortBy sorting order by
                    * @return response entity object
                    */
                   @GetMapping("/getCustomer/paging/sorting")
                   public ResponseEntity<APIResponse> getCustomersUsingPagingAndSorting(
                           @RequestParam(defaultValue = "0") int pageNo,
                           @RequestParam(defaultValue = "5") int pageSize,
                           @RequestParam(defaultValue = "customerId") String sortBy
                   ) {
                       return customerService.getCustomersUsingPagingAndSorting(pageNo, pageSize, sortBy);
                   }
               
                   /**
                    * Load the customer using paging and sorting algorithm.
                    *
                    * @param sortBy sorting order by
                    * @return response entity object
                    */
                   @GetMapping("/getCustomer/sorting")
                   public ResponseEntity<APIResponse> getCustomersUsingSorting(
                           @RequestParam(defaultValue = "customerId") String sortBy
                   ) {
                       return customerService.getCustomersUsingSorting(sortBy);
                   }

Introduce the methods inside the service interface


                      ResponseEntity<APIResponse> getCustomersUsingPagination(int pageNo, int pageSize);
                      ResponseEntity<APIResponse> getCustomersUsingPagingAndSorting(int pageNo, int pageSize, String sortBy);
                      ResponseEntity<APIResponse> getCustomersUsingSorting(String sortBy);

customer service implementation class changes


                    /**
                    * Load the customer using paging algorithm.
                    *
                    * @param pageNo page number
                    * @param pageSize page size
                    * @return response entity object
                    */
                    @Override
                    public ResponseEntity<APIResponse> getCustomersUsingPagination(int pageNo, int pageSize) {
                        PageRequest pageRequest = PageRequest.of(pageNo, pageSize);
                        Page<CustomerModel> pagedResult = paginationRepository.findAll(pageRequest);
                        return ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(SUCCESS_CODE)
                                        .errorMessage(SUCCESSFULLY_UPDATED)
                                        .data(pagedResult.stream().map(customerModel -> modelToResponseMapper(customerModel)).toList())
                                        .build()
                        );
                    }

                    /**
                    * Load the customer using paging and sorting algorithm.
                    *
                    * @param pageNo page number
                    * @param pageSize page size
                    * @param sortBy sorting order by
                    * @return response entity object
                    */
                    @Override
                    public ResponseEntity<APIResponse> getCustomersUsingPagingAndSorting(int pageNo, int pageSize, String sortBy) {
                        PageRequest pageRequest = PageRequest.of(pageNo, pageSize, Sort.by(sortBy));
                        Page<CustomerModel> pagedResult = paginationRepository.findAll(pageRequest);
                        return ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(SUCCESS_CODE)
                                        .errorMessage(SUCCESSFULLY_UPDATED)
                                        .data(pagedResult.stream().map(customerModel -> modelToResponseMapper(customerModel)).toList())
                                        .build()
                        );
                    }

                    /**
                    * Load the customer using paging and sorting algorithm.
                    *
                    * @param sortBy sorting order by
                    * @return response entity object
                    */
                    @Override
                    public ResponseEntity<APIResponse> getCustomersUsingSorting(String sortBy) {
                        Sort sortOrder = Sort.by(sortBy);
                        Iterable<CustomerModel> pagedResult = paginationRepository.findAll(sortOrder);
                        List<CustomerModel> customerModels = new ArrayList<>();
                        pagedResult.forEach(customerModels::add);
                        return ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(SUCCESS_CODE)
                                        .errorMessage(SUCCESSFULLY_UPDATED)
                                        .data(customerModels.stream().map(customerModel -> modelToResponseMapper(customerModel)).toList())
                                        .build()
                        );
                    }

Testing through postman

Send the GET HTTP request call by passing the Query Params from Postman like http://localhost:8092/api/v1/customer/getCustomer/paging/sorting?pageNo=1&pageSize=3&sortBy=customerId


Full source code is available in follwong GitHub repository: SpringBoot PagingAndSorting