In this Spring Data JPA pagination and sorting example we’ll see how to use PagingAndSortingRepository to paginate access by providing number of records per page and page number. You can sort records too by passing the field (or group of fields)
on which sorting is done along with page properties or separately.
We’ll create a rest web service using Spring Web MVC, JPA implementation used is Hibernate and DB is MySQL.
For maven dependencies required for the example and configuration classes for setting up EntityManagerFactory and web application you can refer this post- Spring Data JPA @Query Annotation Example
DB table Query
MySQL DB table used for this Spring data JPA can be created using the following query.CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(45) DEFAULT NULL, `last_name` varchar(45) DEFAULT NULL, `department` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
JPA Entity class
This is the entity class which maps to the employee table in DB.
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="employee")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@Column(name="first_name")
private String firstName;
@Column(name="last_name")
private String lastName;
@Column(name="department")
private String dept;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getDept() {
return dept;
}
public void setDept(String dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Id= " + getId() + " First Name= " +
getFirstName() + " Last Name= " + getLastName() +
" Dept= "+ getDept();
}
}
@Entity annotation specifies that this model class is an entity.
@Table annotation specifies the primary table for the entity.
@Id annotation specifies the primary key of the entity.
@GeneratedValue specifies the primary key generation strategy which is autoincrement in this case.
@Column annotation specifies the mapped table column name for the field.
Spring Data JPA Repository
import java.util.List;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import com.knpcode.springproject.model.Employee;
public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
List<Employee> findByLastName(String lastName, Pageable pageable);
List<Employee> findByDept(String dept, Sort sort);
}
EmployeeRepository interface has two methods-
- findByLastName – Where Pageable object is passed to provide pagination properties.
- findByDept- In this method Sort object is passed to provide options for sorting.
You can see that EmployeeRepository interface extends JpaRepository which takes the domain class to manage (Employee in this case) as well as the id type of the domain class as type arguments. Since JpaRepository extends PagingAndSortingRepository so your interface indirectly extends PagingAndSortingRepository.
PagingAndSortingRepository extends CrudRepository interface and defines two methods of its own.
public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {
/**
* Returns all entities sorted by the given options.
*/
Iterable<T> findAll(Sort sort);
/**
* Returns a Page of entities meeting the paging restriction provided in the Pageable object.
*/
Page<T> findAll(Pageable pageable);
}
Rest Controller
Using a Rest controller class we’ll map the path to the methods that are to be called for the requests. Parameters for passing in Pageable and Sort objects creation are also received as @RequestParam in the methods in Controller class.
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;
import com.knpcode.springproject.model.Employee;
import com.knpcode.springproject.service.EmployeeService;
@RestController
@RequestMapping("/employee")
public class EmployeeController {
@Autowired
EmployeeService empService;
@GetMapping("/{id}")
public Employee getEmployeeById(@PathVariable int id) {
return empService.getEmployeeById(id);
}
@GetMapping
public List<Employee> getAllEmployees(@RequestParam(value="pageNo", defaultValue="0") Integer pageNo,
@RequestParam(value="sortKey", defaultValue="lastName") String sortKey)
{
return empService.getAllEmployees(pageNo, sortKey);
}
@DeleteMapping("/{id}")
@ResponseStatus(HttpStatus.OK)
public void deleteEmployeeById(@PathVariable int id){
empService.deleteEmployeeById(id);
}
@PostMapping
@ResponseStatus(HttpStatus.CREATED)
public Employee addEmployee(@RequestBody Employee emp) {
return empService.addEmployee(emp);
}
@GetMapping("/lastname/{lastName}")
public List<Employee> getEmployeeByLastName(@PathVariable String lastName,
@RequestParam(value="pageNo", defaultValue="0") Integer pageNo)
{
return empService.getEmployeeByLastName(lastName, pageNo);
}
@GetMapping("/dept/{department}")
public List<Employee> getEmployeeByDepartment(@PathVariable String department) {
return empService.getEmployeeByDepartment(department);
}
}
Spring Data JPA example – Service class
From the service layer we’ll call the repository methods. Notice that repository instance has to be injected in the service class.
In the Service class we’ll create Pageable and Sort object that are passed in the methods.
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.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import com.knpcode.springproject.dao.EmployeeRepository;
import com.knpcode.springproject.model.Employee;
@Service
public class EmployeeService {
@Autowired
private EmployeeRepository repository;
public Employee getEmployeeById(int id) {
return repository.findById(id).get();
}
public List<Employee> getAllEmployees(Integer pageNo, String sortKey)
{
// Setting no. of records in each page, page no., sort field
int noOfRecords = 2;
Pageable page = PageRequest.of(pageNo, noOfRecords, Sort.by(sortKey));
Page<Employee> pagedResult = repository.findAll(page);
// changing to List
return pagedResult.getContent();
}
public void deleteEmployeeById(int id){
repository.deleteById(id);
}
public Employee addEmployee(Employee emp) {
return repository.save(emp);
}
public List<Employee> getEmployeeByLastName(String lastName, Integer pageNo) {
// Setting no. of records in each page, page no., sort field
int noOfRecords = 2;
Pageable page = PageRequest.of(pageNo, noOfRecords, Sort.by("firstName"));
return repository.findByLastName(lastName, page);
}
public List<Employee> getEmployeeByDepartment(String department) {
// sort field
Sort sortKey = Sort.by("lastName");
return repository.findByDept(department, sortKey);
}
}
In getAllEmployees() and getEmployeeByLastName() methods a org.springframework.data.domain.Pageable object is created where
page number, number of records per page and sort key are passed as parameters.
In getEmployeeByDepartment() method a org.springframework.data.domain.Sort object is created by
passing the field on which the sorting is done.
Paging and Sorting options
If you want records to be sorted with in the paginated records then you can pass Sort instance while creating Pageable object.
Pageable page = PageRequest.of(pageNo, noOfRecords, Sort.by(sortKey));
When creating Pageable object passing sort field is optional so you can have a Pageable instance created as following too.
Pageable page = PageRequest.of(pageNo, noOfRecords);
If you want records to be displayed in the sorted order only without any pagination then pass only Sort object.
Sort sortKey = Sort.by("lastName");
If you want to sort on more than one fields then you combine sorting fields using and method. For example if you want to sort on both lastName and firstName fields.
Sort sortKey = Sort.by("lastName").and(Sort.by("firstName"));
To specify ascending (default order) or descending order for sorting there are ascending() and descending() methods.
Sort sortKey = Sort.by("lastName").ascending().and(Sort.by("firstName"));
Page Vs Slice
If you have noticed in the getAllEmployees() method of the Service class repository.findAll(page) call returns a Page instance. Other options for returning data with paginated access are Slice and List.
org.springframework.data.domain.Page<T>- It is an interface that represents a sublist of a list of objects. It has methods to get information about the contained entire list like getTotalElements() and getTotalPages()
org.springframework.data.domain.Slice<T>- A slice of data that indicates whether there's a next or previous slice available.
Deploying the Spring Data JPA application
Right clicking the project and select Run As – Maven build, provide goal as clean install. If the build is successful you will have your application packaged as a war which you can deploy on web container like Tomcat and then test the application.
For testing the RESTful webservice, Postman rest client is used.
Get all employees
Note that the request selected is GET and the URL is- http://localhost:8080/SpringJPAProject/employee/?pageNo=0&sortKey=lastName where pageNo and sortKey are request parameters.
For another page i.e. page number 1 you can send the URL as http://localhost:8080/SpringJPAProject/employee/?pageNo=1&sortKey=lastName
Note that page number starts from 0.
From the console you can see that query sent for page number 1 is as following-
Hibernate: select employee0_.id as id1_0_, employee0_.department as departme2_0_, employee0_.first_name as first_na3_0_, employee0_.last_name as last_nam4_0_ from employee employee0_ order by employee0_.last_name asc limit ?, ? Hibernate: select count(employee0_.id) as col_0_0_ from employee employee0_
As you can see a separate count query is also sent. That is one drawback of using Page instance which has the overhead of having this count query for getting total number of pages.
Get employees by department
That's all for the topic Spring Data JPA Pagination and Sorting Example. If something is missing or you have something to share about the topic please write a comment.
You may also like
No comments:
Post a Comment