PagingAndSortingRepository in Spring Data JPA

➤ Java Bean with Examples
➤ How to Code a Game
➤ Array Programs in Java
➤ Java Inline Thread Creation
➤ Java Custom Exception
➤ Hibernate vs JDBC
➤ Object Relational Mapping
➤ Check Oracle DB Size
➤ Check Oracle DB Version
➤ Generation of Computers
➤ XML Pros & Cons
➤ Git Analytics & Its Uses
➤ Top Skills for Cloud Professional
➤ How to Hire Best Candidates
➤ Scrum Master Roles & Work
➤ CyberSecurity in Python
➤ Protect from Cyber-Attack
➤ Solve App Development Challenges
➤ Top Chrome Extensions for Twitch Users
➤ Mistakes That Can Ruin Your Test Metric Program

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 org.springframework.data.domain.Sort 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.

Create a spring starter project with the following dependencies:- Lombok, Spring Data JPA, MySQL Driver.

In application.yml

spring:
  
## DB Details 
  datasource:
    url: jdbc:mysql://localhost:3306/test
    username: root
    password: root
    
## JPA Details
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        format_sql: true

Entity class:-

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Product {
   @Id
   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 (or JpaRepository).

Repository:-

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

Insert some data:-

@Component
public class ProductRunner implements CommandLineRunner {

   @Autowired
   private ProductRepository productRepository;

   @Override
   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);

package com.example.demo.model.runner;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.stereotype.Component;

import com.example.demo.model.Product;
import com.example.demo.model.repo.ProductRepository;

@Component
public class TestRunner implements CommandLineRunner {

    @Autowired
    private ProductRepository productRepository;

    @Override
    public void run(String... args) throws Exception {
        productRepository.findAll().forEach(System.out::println);

        // select * from product order by pcode asc
        Sort sort = Sort.by("pcode");
        Sort sort1 = Sort.by(Direction.ASC, "pcode");
        Sort sort2 = Sort.by("pcode").ascending();

        // select * from product order by pcode desc
        Sort sort3 = Sort.by("pcode").descending();
        Sort sort4 = Sort.by(Direction.DESC, "pcode");

        // SELECT * FROM Product ORDER BY pcode ASC, pcost ASC;
        Sort sort5 = Sort.by("pcode", "pcost");
        // SELECT * FROM Product ORDER BY pcode DESC, pcost DESC;
        Sort sort6 = Sort.by(Direction.DESC, "pcode", "pcost");

        Iterable<Product> iterable = productRepository.findAll(sort);
        iterable.forEach(System.out::println);
    }
}

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

// SELECT * FROM PRODUCT ORDER BY PCODE DESC, PCOST ASC;
Sort sort7 = Sort.by(Sort.Order.desc("pcost"), Sort.Order.asc("pcode"));

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

Sort sort = Sort.by(Direction.DESC, "pcode");

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

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

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

// Using Sort.Order directly in Sort.by
Sort sort = Sort.by(Sort.Order.desc("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. They are given in org.springframework.data.domain package.

Pageable pageable = PageRequest.of(pageSize); // default pageNum=0
Pageable pageable = PageRequest.of(pageNum, pageSize);
Pageable pageable = PageRequest.of(pageNum, pageSize, sort);
Pageable pageable = PageRequest.of(pageNum, pageSize, direction, String... properties);

Example:-

package com.example.demo.model.runner;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Component;

import com.example.demo.model.Product;
import com.example.demo.model.repo.ProductRepository;

@Component
public class ProductDataSortRunner implements CommandLineRunner {

    @Autowired
    private ProductRepository productRepository;

    @Override
    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();
        System.out.println(products);

        // 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());
    }
}
Hibernate: 
    select
        p1_0.pid,
        p1_0.pcode,
        p1_0.pcost,
        p1_0.pvendor 
    from
        product p1_0 
    limit
        ?, ?

[Product(pid=101, pcode=PEN, pcost=25.0, pvendor=A), 
 Product(pid=102, pcode=MOUSE, pcost=125.0, pvendor=B), 
 Product(pid=103, pcode=KYBRD, pcost=250.0, pvendor=A), 
 Product(pid=104, pcode=BTL, pcost=180.0, pvendor=B)]

First page: true
Last page: true
Has next page: false
Has previous page: false
Empty page?: false
Page size: 5
Page number: 0
Total pages?: 1
Total rows?: 4

We can also pass sort objects to PageRequest to fetch data based on a given sorting order. Example of Method:- PageRequest.of(pageNum, pageSize, sort);

Sort sort = Sort.by(Sort.Direction.DESC, "pcode");
Page<Product> page = productRepository.findAll(PageRequest.of(0, 5, sort));

Example of method:- PageRequest.of(pageNum, pageSize, direction, String… properties)

String[] properties = { "pcode", "pcost" };
Page<Product> page = productRepository.findAll(
                 PageRequest.of(0, 5, Sort.Direction.DESC, properties));
Hibernate: 
    select
        p1_0.pid,
        p1_0.pcode,
        p1_0.pcost,
        p1_0.pvendor 
    from
        product p1_0 
    order by
        p1_0.pcode desc,
        p1_0.pcost desc 
    limit
        ?, ?

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 *