Sorting and Pagination
We can define our Repository Interface using 'PagingAndSortingRepository' also. It is sub type of CrudRepository.
PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {}
=> Addtional methods:
a. findAll(Sort sort):List
b. findAll(Pageable pageable):Page
SQL Code
order by sname asc => Sort.by("sname") //default is ASC
Sort.by(Direction.ASC,"sname)
order by sname desc => Sort.by(Direction.DESC,"sname")
=> Sort class is having static method name by that returns same object
class Sort {
static Sort by(String property) { ___ }
static Sort by(Direction direction, String property) { ___ }
static class Order {
static Order asc(String property) { }
static Order desc(String property) { }
}
static Sort by(Order ords..) { ___ }
public static enum Direction {
ASC, DESC;
//...
}
}
call:
Sort s = Sort.by("sname");
Sort s = Sort.by("sname","sfee"); //both comes in ASC
Sort s = Sort.by(Direction.DESC,"sname");
Sort s = Sort.by(Direction.DESC,"sname","sfee); //both comes in desc
Sort s = Sort.by(Order.asc("sname"),Order.desc("sfee"));
1. Model
package in.nareshit.raghu.model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.NonNull;
import lombok.RequiredArgsConstructor;
@Data
@NoArgsConstructor
@RequiredArgsConstructor
@AllArgsConstructor
@Entity
@Table(name="stdtab")
public class Student {
@Id
@Column(name="sid")
@NonNull
private Integer stdId;
@Column(name="sname")
private String stdName;
@Column(name="sfee")
private Double stdFee;
}
2. Repository
package in.nareshit.raghu.repo;
import org.springframework.data.repository.PagingAndSortingRepository;
import in.nareshit.raghu.model.Student;
public interface StudentRepository
extends PagingAndSortingRepository<Student, Integer>
{
}
3. Runner class
package in.nareshit.raghu.runner;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;
import org.springframework.stereotype.Component;
import in.nareshit.raghu.model.Student;
import in.nareshit.raghu.repo.StudentRepository;
@Component
public class TestStudentOprPageAndSort implements CommandLineRunner {
@Autowired
private StudentRepository repo;
public void run(String... args) throws Exception {
repo.save(new Student(101, "SYED MD", 580.0));
repo.save(new Student(102, "AMRIT", 480.0));
repo.save(new Student(103, "ANIL", 600.0));
repo.save(new Student(104, "SAM", 320.0)); //INSERT
repo.saveAll(
List.of(
new Student(105, "ABC", 800.0),
new Student(106, "XYZ", 900.0),
new Student(107, "MNO", 500.0)
)
);
repo.findAll().forEach(System.out::println);
System.out.println("###########################");
Sort s1 = Sort.by("stdName");// ASC
Sort s1 = Sort.by(Direction.ASC,"stdName");// ASC
Sort s1 = Sort.by(Direction.DESC,"stdName","stdFee");// ASC
Sort s1 = Sort.by(Order.asc("stdName"),Order.desc("stdFee"));// ASC
repo.findAll(s1).forEach(System.out::println);
}
}
Sorting using Pageble Interface
findAll(Pageable):Page This method is used to get data page by page.
Here Pageable is a Interface. Impl class is PageRequest having method of(number,size) return Pageable.
Pageable p = PageRequest.of(0,10); //number, size
1. Model
package in.nareshit.raghu.model;
import javax.persistence.Column;
//ctrl+shift+O
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.NonNull;
import lombok.RequiredArgsConstructor;
@Data
@NoArgsConstructor
@RequiredArgsConstructor
@AllArgsConstructor
@Entity
@Table(name="stdtab")
public class Student {
@Id
@Column(name="sid")
@NonNull
private Integer stdId;
@Column(name="sname")
private String stdName;
@Column(name="sfee")
private Double stdFee;
}
2. Repository
package in.nareshit.raghu.repo;
import org.springframework.data.repository.PagingAndSortingRepository;
import in.nareshit.raghu.model.Student;
public interface StudentRepository
extends PagingAndSortingRepository<Student, Integer>
{
}
3. Runner class
package in.nareshit.raghu.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 in.nareshit.raghu.model.Student;
import in.nareshit.raghu.repo.StudentRepository;
@Component
public class TestStudentOprPageAndSortTwo implements CommandLineRunner {
@Autowired
private StudentRepository repo;
public void run(String... args) throws Exception {
repo.save(new Student(101, "SYED", 580.0));
repo.save(new Student(102, "AMRIT", 480.0));
repo.save(new Student(103, "ANIL", 600.0));
repo.save(new Student(104, "SAM", 320.0));
repo.save(new Student(105, "ABC", 800.0));
repo.save(new Student(106, "XYZ", 900.0));
repo.save(new Student(107, "MNO", 500.0));
repo.save(new Student(108, "XYZ", 500.0));
repo.save(new Student(109, "MTY", 600.0));
repo.save(new Student(110, "IUJ", 700.0));
repo.save(new Student(111, "NJI", 800.0));
//Input (pageable) = number, size
Pageable pageable = PageRequest.of(333, 5);
//execute query
Page page = repo.findAll(pageable);
//print output
List data = page.getContent();
data.forEach(System.out::println);
//meta data
System.out.println(" Is Empty Page? " + page.isEmpty());
System.out.println(" Is First Page? " + page.isFirst());
System.out.println(" Is Last Page? " +page.isLast());
System.out.println(" Next Page? " +page.hasNext());
System.out.println(" Previous Page? " +page.hasPrevious());
System.out.println(" Total Pages? " +page.getTotalPages());
System.out.println(" Current Page Number? " +page.getNumber());
System.out.println(" Page Size? " + page.getSize());
}
}
Pagination:-
---Internal Formuals: (Hibernate Ex)---
Inputs:
pageNumber = pn
pageSize = ps
totalRows = tr
start Row = sr = (pn-1)*ps
end row = current row to ps limit
-Ex-----------
UI Index (pn=1 internally page=0) , ps =3
start row = (pn-1)*ps = (1-1)*3 = 0*3 = 0
end row = 0th row to 3 rows = 3
---------------------------------------------------------
toatalPages = tr/ps + ( tr%ps > 0? 1:0)
= 9/3 + (9%3>0? 0:1 )
= 3 + 0 > 0 ? 1:0 = 3+0 = 3
UI pn=2, ps=3
Startr row = (pn-1 ) * ps = 1 * 3 = 3
105 rows , pagesize=50
1st page - 0-49
2nd page - 50-99
3rd page - 100-104
-------------------------------------------------------------
While Implementing Pagination we can also send Sorting details
by default pagination output comes without Sorting (unsorted)
Inside PageRequest class use method (3 param)
of(int page, int size, Sort sort)
//Pageable pageable = PageRequest.of(1, 5,Sort.by("stdName","stdId"));
//Pageable pageable = PageRequest.of(1, 5,Sort.by(Order.asc("stdName"),Order.desc("stdId")));
Pageable pageable = PageRequest.of(2, 5,Sort.by(Direction.DESC,"stdName","stdId"));