"Welcome To Ashok IT" "Spring Boot and MicroServices" Topic : Spring Data JPA - Paging & Sorting Date : 17/10/2024 (Session - 46) _____________________________________________________________________________________________________________________________ Yesterday & Today Session ========================= PagingAndSortingRepoistory Interface ==================================== * It is child Interface of Repoistory Interface. * This interface contains two abstract methods related to pagination & sorting. Pagination ========== * The Process of displaying bulk of records from Database as page by page is called Pagination. * Each page will contains specific no of records. * As business owner he/she will fixing about no of records to be display per page. * In Spring data JPA Pagination can be achieved through "Pageable" Object. * Inorder to get the Pageable object we need to use the below Statement Pageable pg = PageRequest.of(PageNo,PageSize); PageNo Index always Start from "0". PageSize will provide input based on business requirement(5,10,15,20 etc.,) ********************* Example on Pagination ********************* 1000 Records are available In Database Table Dividing the Records into Pages and Each Page will contains 100 Records Total No Of Pages = No Of Records in Database Table / Page per Count Total No Of Pages = 1000 / 100 Total No Of Pages = 10 (0 - 9) Pageable pg = PageRequest.of(0,100); //1-100 Records Pageable pg1 = PageRequest.of(3,100); //400-500 Records Sorting ======= * When we are retriveing records of DB Table either ascending order (or) descending order based on some property. * Sorting will takes place in the following order 1) Special Characters(?,+,',' etc) 2) Numbers 3) UpperCase Alphabets 4) LowerCase Alphabets * In Spring Data JPA Sorting will be achieved through "Sort" Object. Hierarchy ========= Repoistory(I) >>>>> Parent Interface (Marker Interface) ^ | PagingAndSortingRepoistory(I) >>>>> Child Interface(2 Abstract Methods) |-> Iterable findAll(Sort Object); >>> This method supports for Sorting Entities based on some Property. |-> Page findAll(Pageable pageable) >>> This Method supports for getting Entities based on Pageable Object Example Application =================== Customer.java ============= package com.ashokit.enities; import jakarta.persistence.Column; import jakarta.persistence.Entity; import jakarta.persistence.GeneratedValue; import jakarta.persistence.Id; import jakarta.persistence.Table; @Entity @Table(name = "ashokit_customers") public class Customer { @Id @GeneratedValue //Auto Strategy @Column(name="customer_id") private Integer cutomerId; @Column(name="customer_name") private String customerName; @Column(name="customer_location") private String customerLocation; public Customer() { } public Customer(Integer cutomerId, String customerName, String customerLocation) { this.cutomerId = cutomerId; this.customerName = customerName; this.customerLocation = customerLocation; } public Customer(String customerName, String customerLocation) { this.customerName = customerName; this.customerLocation = customerLocation; } public void setCutomerId(Integer cutomerId) { this.cutomerId = cutomerId; } public Integer getCutomerId() { return cutomerId; } public void setCustomerName(String customerName) { this.customerName = customerName; } public String getCustomerName() { return customerName; } public void setCustomerLocation(String customerLocation) { this.customerLocation = customerLocation; } public String getCustomerLocation() { return customerLocation; } @Override public String toString() { return "Customer [cutomerId=" + cutomerId + ", customerName=" + customerName + ", customerLocation=" + customerLocation + "]"; } } CustomerDao.java ================ package com.ashokit.dao; import org.springframework.data.repository.CrudRepository; import org.springframework.data.repository.PagingAndSortingRepository; import com.ashokit.enities.Customer; public interface CustomerDao extends PagingAndSortingRepository,CrudRepository{ } CustomerService.java ==================== package com.ashokit.services; import java.util.List; import com.ashokit.enities.Customer; public interface CustomerService { //Method for Fetching records based on supplied PageNo public List getCustomerInfo(int pageNo, int pageSize); //Method for Fetching all the page of Records public void getCustomerInfo(); //Method for saving customers information for dummy data public Iterable saveAllCustomers(List customers); } CustomerServiceImpl.java ======================== package com.ashokit.services; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Sort; import org.springframework.data.domain.Sort.Direction; import org.springframework.stereotype.Service; import com.ashokit.dao.CustomerDao; import com.ashokit.enities.Customer; @Service public class CustomerServiceImpl implements CustomerService{ //Injecting the DAO Object @Autowired private CustomerDao customerDao; @Override public List getCustomerInfo(int pageNo, int pageSize) { //Creating PageRequest For Pulling customer based on pageNo & pageSize PageRequest pageableCustomer = PageRequest.of(pageNo, pageSize); //Passing the PageRequest object to findAll Page pageCustomers = customerDao.findAll(pageableCustomer); //getting the Content of an Page...... List customersList =pageCustomers.getContent(); return customersList; } @Override public void getCustomerInfo() { //Wanted to display all the pages of information int pageSize = 5; //Getting the customers count from Database Table long customerCount = customerDao.count(); //Find the page count // 15/5 = 3, 15/4 = 4 (modify pagecount) long pageCount = customerCount/pageSize; // 16/5 = 4 //Modifying the pageCount pageCount = (customerCount % pageSize == 0)? pageCount : ++pageCount; //displaying the Records page by page for(int pageNo = 0 ; pageNo < pageCount; pageNo++) { //Preparing the PageRequest Object //PageRequest pageCustomers = PageRequest.of(pageNo, pageSize); //PageRequest pageCustomers = PageRequest.of(pageNo, pageSize,Sort.by("customerName")); PageRequest pageCustomers = PageRequest.of(pageNo, pageSize,Sort.by(Direction.DESC, "customerName")); //Passing PageRequest object to findAll() Page customersPage = customerDao.findAll(pageCustomers); //getting the content from Page System.out.println("Page No :: "+(pageNo+1)+ " Records........"); customersPage.getContent().forEach(System.out::println); } } @Override public Iterable saveAllCustomers(List customers) { Iterable savedCustomers = customerDao.saveAll(customers); return savedCustomers; } } application.properties ====================== #configuring the Database properties spring.datasource.driver-class-name=oracle.jdbc.OracleDriver spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe spring.datasource.username=system spring.datasource.password=manager #Hibernate Configuration spring.jpa.hibernate.ddl-auto=update spring.jpa.show-sql=true Application.java =============== package com.ashokit; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import com.ashokit.enities.Customer; import com.ashokit.services.CustomerService; @SpringBootApplication public class Application implements CommandLineRunner { @Autowired private CustomerService customerService; public static void main(String[] args) { SpringApplication.run(Application.class, args); } @Override public void run(String... args) throws Exception { //saveAll the Customers Information //customerService.saveAllCustomers(getCustomersData()); //PageSize=5, PageNo=0, TotalRecords=15, No of Pages=3(0 to 2), //List customers = customerService.getCustomerInfo(3, 5); //Displaying the page of customers information //customers.forEach(eachCustomer -> System.out.println(eachCustomer)); customerService.getCustomerInfo(); } public List getCustomersData(){ Customer customer1 = new Customer("Mahesh","Hyderabad1"); Customer customer2 = new Customer("Suresh","Hyderabad2"); Customer customer3 = new Customer("Rajesh","Hyderabad3"); Customer customer4 = new Customer("Ramesh","Hyderabad4"); Customer customer5 = new Customer("Nagesh","Hyderabad5"); Customer customer6 = new Customer("Yagnesh","Hyderabad6"); Customer customer7 = new Customer("Ganesh","Hyderabad7"); Customer customer8 = new Customer("Naresh","Hyderabad8"); Customer customer9 = new Customer("Rameshwar","Hyderabad1"); Customer customer10 = new Customer("Anil","Hyderabad10"); Customer customer11 = new Customer("Avinash","Hyderabad11"); Customer customer12 = new Customer("Smith","Hyderabad12"); Customer customer13 = new Customer("John","Hyderabad13"); Customer customer14 = new Customer("Mark","Hyderabad14"); Customer customer15 = new Customer("Bhoopal","Hyderabad15"); //Java9 Style for Creating Immutable Collection List customers = List.of(customer1,customer2,customer3,customer4,customer5, customer6,customer7,customer8,customer9,customer10, customer11,customer12,customer13,customer14,customer15); return customers; } } OUTPUT ====== . ____ _ __ _ _ /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \ ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \ \\/ ___)| |_)| | | | | || (_| | ) ) ) ) ' |____| .__|_| |_|_| |_\__, | / / / / =========|_|==============|___/=/_/_/_/ Hibernate: select count(*) from ashokit_customers c1_0 Hibernate: select * from (select c1_0.customer_id c0,c1_0.customer_location c1,c1_0.customer_name c2,row_number() over(order by c1_0.customer_name desc) rn from ashokit_customers c1_0) r_0_ where r_0_.rn<=?+? and r_0_.rn>? order by r_0_.rn Hibernate: select count(c1_0.customer_id) from ashokit_customers c1_0 Page No :: 1 Records........ Customer [cutomerId=6, customerName=Yagnesh, customerLocation=Hyderabad6] Customer [cutomerId=2, customerName=Suresh, customerLocation=Hyderabad2] Customer [cutomerId=12, customerName=Smith, customerLocation=Hyderabad12] Customer [cutomerId=9, customerName=Rameshwar, customerLocation=Hyderabad1] Customer [cutomerId=4, customerName=Ramesh, customerLocation=Hyderabad4] Hibernate: select * from (select c1_0.customer_id c0,c1_0.customer_location c1,c1_0.customer_name c2,row_number() over(order by c1_0.customer_name desc) rn from ashokit_customers c1_0) r_0_ where r_0_.rn<=?+? and r_0_.rn>? order by r_0_.rn Hibernate: select count(c1_0.customer_id) from ashokit_customers c1_0 Page No :: 2 Records........ Customer [cutomerId=3, customerName=Rajesh, customerLocation=Hyderabad3] Customer [cutomerId=8, customerName=Naresh, customerLocation=Hyderabad8] Customer [cutomerId=5, customerName=Nagesh, customerLocation=Hyderabad5] Customer [cutomerId=14, customerName=Mark, customerLocation=Hyderabad14] Customer [cutomerId=1, customerName=Mahesh, customerLocation=Hyderabad1] Hibernate: select * from (select c1_0.customer_id c0,c1_0.customer_location c1,c1_0.customer_name c2,row_number() over(order by c1_0.customer_name desc) rn from ashokit_customers c1_0) r_0_ where r_0_.rn<=?+? and r_0_.rn>? order by r_0_.rn Hibernate: select count(c1_0.customer_id) from ashokit_customers c1_0 Page No :: 3 Records........ Customer [cutomerId=13, customerName=John, customerLocation=Hyderabad13] Customer [cutomerId=7, customerName=Ganesh, customerLocation=Hyderabad7] Customer [cutomerId=15, customerName=Bhoopal, customerLocation=Hyderabad15] Customer [cutomerId=11, customerName=Avinash, customerLocation=Hyderabad11] Customer [cutomerId=16, customerName=Ashok, customerLocation=Hyderabada16] Hibernate: select * from (select c1_0.customer_id c0,c1_0.customer_location c1,c1_0.customer_name c2,row_number() over(order by c1_0.customer_name desc) rn from ashokit_customers c1_0) r_0_ where r_0_.rn<=?+? and r_0_.rn>? order by r_0_.rn Page No :: 4 Records........ Customer [cutomerId=10, customerName=Anil, customerLocation=Hyderabad10] Hierarchy ========= Repoistory(I) >>>>> Parent Interface (Marker Interface) ^ | CrudRepoistory(I) >>>>>> Child Interface(12 Abstract Methods, Iterable Objects) ^ | ListCrudRepoistory(CI) >>>>> Child Interface (3 Abstract Methods return List Objects) Repoistory(I) ^ | PagingAndSortingRepoistory(I) >>>>> Child Interface(2 Abstract Methods) |-> Iterable findAll(Sort Object); >>> This method supports for Sorting Entities based on some Property. |-> Page findAll(Pageable pageable) >>> This Method supports for getting Entities based on Pageable Object IIQ) Differences between crudRepoistory & ListCrudRepoistory ? ---> In CrudRepoistory saveAll(), findAll(),findAllById() return types are Iterable object which can't be performed Sorting,Transformation,Filtering etc., ---> In ListCrudRepoistory saveAll(),findAll(),findAllById() return types are List objects which can be performed sorting,Transformation,Filtering etc., * CrudRepoistory(I),PagingAndSortingRepoistory(I) interfaces are common for both SQL Databases & No-SQL Databases. * If We use methods of the above Repoistory interfaces we need not to change code any thing service class even though the application moves from SQL Database Software to No-SQL Database Software. * As part of Spring Data JPA Module we have another important Repoistory interface i.e.,JPARepoistory. * JPARepoistory Interface is a child interface of ListPagingAndSortingRepoistory & ListCrudRepoisotry & QueryByExampleExecutor Interfaces. * JPARepoistory Interface contains 12 abstract Methods * JPARepoistory = CurdRepoistory Interface Methods + PagingAndSortingRepoistory Interface Methods + QueryByExampleExecutor * Most of the methods are available in JPA Repoistory are also available in CrudRepoistory interface and PagingAndSortingRepository Interface but they are work in Underlying JPA Implementation. * For Example in our application DAO Interface extending services from JPARepoistory Interface it will supports the Database Operations for only SQL Database. Differences Between CrudRepoistory and JPARepoistory ==================================================== CrudRepoistory ============== 1) SaveAll(),findAll() Methods return type is Iterable object 2) CrudRepoistory Methods doesn't have support of Example Object. 3) findById() return type is optional Object i.e,Optional and will throw Custom Exception if id is not available 4) CrudRepoistory deleteXXX() will not support Batch Deletion because when we working these methods for each id (or) entity will have seperate delete statement. Example ======= Hibernate: delete from ashokit_customers where customer_id=? Hibernate: delete from ashokit_customers where customer_id=? Hibernate: delete from ashokit_customers where customer_id=? 5) These methods are common for both SQL Databases and NOSQL Databases. JPARepoistory ============= 1) SaveAll(),findAll() methods return type is List Object 2) JPARepoistory Methods having support of Example Object 3) getById() return type is Object i.e., and it will throw EntityNotFoundException supplied id is not available 4) JPARepoistory deleteXXX() will support for Batch Deletion for all entities (or) given ids will be deleted with the help single delete statement. Example ======= Hibernate: delete from ashokit_customers c1_0 where c1_0.customer_id in (?,?,?) 5) These methods are specific to SQL Database only. JpaRepoistory Methods ===================== 1) void deleteAllByIdInBatch(Iterable ids) ========================================== * This method is used to perform bulk deletion (or) batch deletion by taking primary key column values of the Database table. * This Methods won't throws an error If the given id record is not existed in the Database table. * If we remember when we are working deleteAllById(Iterable ids) from CrudRepoistory Interfaces if the given id record is not existed in the database table it will throw an error. * This method can be used only for SQL Database Softwares. 2) List findAll(Example example) ====================================== * This Method is used to return List of object by given Example object based entity object. * Example is an predefined Interfaces from Spring Data JPA Module. * Example Object is JPA Supplied Object containing other object and which can works like Optional class from Java8 Version. Optional opt = Optional.of(customer); >>>> opt.isPresent() >>>>> opt.get() Customer c = new Customer(); c.setCustomerLocation(null); Example example = Example.of(c); customerDao.findAll(example); >>> Retreive the all records for table based on customerName is non null value and simply example object is taking entity object and avoiding null values properties in entity object. * When we pass an entity object to the Example object then entity object allows only non null data will be supplied to Example Object 3) saveAndFlush(entity) =========================== * This method is used to save the given entity into database. * If we are working with CrudRepoistory we used save(entity) method for saving the given entity but where as in JPARepository need to use this method only. * Flushing is the process of synchronizing the state of the persistence context with the underlying database. * When using saveAndFlush() data immediately flush to the database and to do it with the save method we need to call flush() method explicitly. 4) T getReferenceById(Id) ========================= * This Method is used to get the entity object based on given primary key. * When we working with CrudRepoistory for this equivalent method we have Optional findById(ID id) here programmer can have option to throw custom Exception because if given id is not availble in database table. Optional cust = customerDao.findById(123); if(cust.isPresent()){ Customer c = cust.get(); }else{ throw new IllegalArgumentException("Given Id is not existed"); // throw new ResourceNotFoundException("Given ID is not existed"); } * When we are working with this method we do not have option to throw userdefined exception because it already throwing EntityNotFoundException if given id is not existed database.