PagingAndSortingRepository in Spring Data JPA

PagingAndSortingRepository In Spring Data JPA | The PagingAndSortingRepository has two methods for pagination and sorting. It does not implement CrudRepository.

public interface PagingAndSortingRepository<T, ID> extends Repository<T, ID> {
   Iterable<T> findAll(Sort sort);
   Page<T> findAll(Pageable pageable);

In SQL, order by columns [ASC|DESC] fetches data in Sorting order. Example:-

select * from student order by sfee desc;
select * from student order by sfee; // default is ASC

To do sorting, an enum Direction is provided that has two possible values:- ASC, and DESC. The default value is ASC. This enum is Part of class.

class Sort {
    static Sort by(String... properties) {___}

    static Sort by(Direction direction, String... properties) {___}

    static enum Direction {
        ASC, DESC;

Using the static method ‘by()’ we can create a Sort object and pass it to the findAll(Sort) method, which gets data in Sorting order.

Dependencies:- Lombok, Spring Data JPA, MySQL Driver
In application.yml

    url: jdbc:mysql://localhost:3306/test
    username: root
    password: root
    show-sql: true
      ddl-auto: update
        format_sql: true

Entity class:-

public class Product {
   private Integer pid;
   private String pcode;
   private Double pcost;
   private String pvendor;

PagingAndSortingRepository interface does not implement CrudRepository. To perform CRUD operations & pagination we can extend our repository from both CrudRepository and PagingAndSortingRepository. Repository:-

public interface ProductRepository extends CrudRepository<Product, Integer>, 
      PagingAndSortingRepository<Product, Integer> {

Insert some data:-

public class ProductRunner implements CommandLineRunner {

   private ProductRepository productRepository;

   public void run(String... args) throws Exception {
      productRepository.saveAll(List.of(new Product(101, "PEN", 25.0, "A"), 
            new Product(102, "MOUSE", 125.0, "B"),
            new Product(103, "KYBRD", 250.0, "A"), 
            new Product(104, "BTL", 180.0, "B")));

For Sorting, we can call the method given in PagingAndSortingRepository:- Iterable<T> findAll(Sort sort);

public class ProductDataSortRunner implements CommandLineRunner {

   private ProductRepository productRepository;

   public void run(String... args) throws Exception {

      // select * from product order by pcode asc
      Sort sort ="pcode");
      // Or,
      // Sort sort =, "pcode");
      // Or,
      // Sort sort ="pcode").ascending();
      // Or,

      // select * from produce order by pcode desc
      Sort sort1 ="pcode").descending();
      // Or,
      // Sort sort1 =, "pcode");

      Sort sort2 ="pcode", "pcost");
      // Or,
      // Sort sort2 =,"pcode","pcost");

      Iterable<Product> iterable = productRepository.findAll(sort);

Assume we need to sort by pcost in descending order and by pcode in ascending order if pcost values are the same:-

Sort sort ="pcost"), Sort.Order.asc("pcode"));

There are multiple ways to create a Sort object. Below all are the same:-

Sort sort =, "pcode");

// Using Sort.Order and
Sort.Order order = new Sort.Order(Sort.Direction.DESC, "pcode");
Sort sort =;

// Using Sort with method chaining
Sort sort ="pcode").descending();

// Using with varargs
Sort sort = Sort.Order(Sort.Direction.DESC, "pcode"));

// Using Sort.Order directly in
Sort sort ="pcode"));

Pagination in Spring Data JPA

Pagination is the process of fetching database table data, Page by Page called parts (Equally divided parts).

We need to call the method given in PagingAndSortingRepository: Page<T> findAll(Pageable pageable);

Here Pageable means Input passed by the programmer for pagination. The pageable interface has an implementation class PageRequest class. They are given in package.

Pageable pageable = PageRequest.of(pageNum, pageSize);
Pageable pageable = PageRequest.of(pageNum, pageSize, sort);


public class ProductDataSortRunner implements CommandLineRunner {

   private ProductRepository productRepository;

   public void run(String... args) throws Exception {
      Pageable pageable = PageRequest.of(0, 5);
      Page<Product> page = productRepository.findAll(pageable);

      // read content
      List<Product> products = page.getContent();

      // meta data
      System.out.println("First page: " + page.isFirst());
      System.out.println("Last page: " + page.isLast());
      System.out.println("Has next page: " + page.hasNext());
      System.out.println("Has previous page" + page.hasPrevious());
      System.out.println("Empty page?: " + page.isEmpty());
      System.out.println("Page size: " + page.getSize());
      System.out.println("Page number: " + page.getNumber());
      System.out.println("Total pages?: " + page.getTotalPages());
      System.out.println("Total rows?: " + page.getTotalElements());

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *