Learn to request and display only chunks of data from database using pagination and sorting inputs and query parameters in spring boot and spring data applications. It is mostly required when we are displaying domain data in tabular format in UI.
Pagination consists of two fields – page size and page number. Sorting is done on a single or multiple fields in the table.
1. JPA Entity
In this post, we are taking examples of EmployeeEntity class. Each entity instance represents an employee record in the database.
@Entity
public class EmployeeEntity {
@Id @GeneratedValue
private Long id;
private String firstName;
private String lastName;
private String email;
//Setters, getters and toString()
}2. Using PagingAndSortingRepository
The PagingAndSortingRepository is an extension of CrudRepository to provide additional methods to retrieve entities using the pagination and sorting abstraction. It implicitly provides two methods:
- Page findAll(Pageable pageable) – returns a
Pageof entities meeting the paging restriction provided in thePageableobject. - Iterable findAll(Sort sort) – returns all entities sorted by the given options. No paging is applied here.
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
import com.howtodoinjava.demo.entity.EmployeeEntity;
@Repository
public interface EmployeeRepository
extends PagingAndSortingRepository<EmployeeEntity, Long> {
}We can add more methods that accept Pageable and Sort as parameters for any custom requirements.
List<EmployeeEntity> findEmployeesByDepartmentId(long departmentId, Pageable pageable);3. Accepting Page and Sort Parameters
Generally, paging and sorting parameters are optional and thus part of the request URL as query parameters. If any API supports paging and sorting, ALWAYS provide default values to these parameters – to be used when the client does not choose to specify any paging or sorting preferences.
The default paging and sorting values shall be clearly documented in API docs. In UI, these default values can be highlighted with separate colors.
Page number values start with 0. So in UI, if you are displaying page number from 1, then do not forget to subtracting ‘1’ while fetching records.
In the below Spring MVC controller, we are accepting paging and sorting parameters using the following query parameters:
- pageNo: The current page number.
- pageSize: Number of records on each page.
- sortBy: Field names if any sorting has to be applied.
Also, by default '10' employees will be fetched from the database on page number '0', and employee records will be sorted based on 'id' field.
@RestController
@RequestMapping("/employees")
public class EmployeeController
{
@Autowired
EmployeeService service;
@GetMapping
public ResponseEntity<List<EmployeeEntity>> getAllEmployees(
@RequestParam(defaultValue = "0") Integer pageNo,
@RequestParam(defaultValue = "10") Integer pageSize,
@RequestParam(defaultValue = "id") String sortBy)
{
List<EmployeeEntity> list = service.getAllEmployees(pageNo, pageSize, sortBy);
return new ResponseEntity<List<EmployeeEntity>>(list, new HttpHeaders(), HttpStatus.OK);
}
}To perform pagination and/or sorting, we must create org.springframework.data.domain.Pageable or org.springframework.data.domain.Sort instances are passed to the findAll() method.
@Service
public class EmployeeService
{
@Autowired
EmployeeRepository repository;
public List<EmployeeEntity> getAllEmployees(Integer pageNo, Integer pageSize, String sortBy)
{
Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by(sortBy));
Page<EmployeeEntity> pagedResult = repository.findAll(paging);
if(pagedResult.hasContent()) {
return pagedResult.getContent();
} else {
return new ArrayList<EmployeeEntity>();
}
}
}4. Pagination and Sorting Examples
4.1. Paging Only
To apply only pagination in the result set, we shall create Pageable object without any Sort information.
Pageable paging = PageRequest.of(pageNo, pageSize);
Page<EmployeeEntity> pagedResult = repository.findAll(paging);4.2. Sorting Only
If there is no need to page, and only sorting is required, we can create Sort object for that.
Sort sortOrder = Sort.by("email");
List<EmployeeEntity> list = repository.findAll(sortOrder);If we wish to apply to sort on multiple columns or group by sort, then that is also possible by creating Sort using simple builder pattern steps.
Sort emailSort = Sort.by("email");
Sort firstNameSort = Sort.by("first_name");
Sort groupBySort = emailSort.and(firstNameSort);
List<EmployeeEntity> list = repository.findAll(groupBySort);4.3. Paging and Sorting Together
To apply only pagination in the result set, we shall create Pageable object with desired Sort column name.
Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by("email"));
Page<EmployeeEntity> pagedResult = repository.findAll(paging);By default, records are ordered in DESCENDING order. To choose ASCENDING order, use .ascending() method.
Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by("email").ascending());
Page<EmployeeEntity> pagedResult = repository.findAll(paging);5. Difference between Page and Slice
5.1. Page
The findAll(Pageable pageable) method by default returns a Page object. A Page object provides much more useful information than just a list of employees on the current page. For example, a Page object has the number of total pages, the current page number and whether the current page is the first or the last page.
Finding total pages invokes an additional count() query causing an extra overhead cost. Be sure when we are using it.
5.2. Slice
Slice is very much similar to Page, except it does not provide the number of total pages in the database. It helps in improving performance when we do not need to display the total number of pages in UI.
Generally, Slice is used in case navigation is consists of Next page and Previous page links.
To use Slice, we need to implement our own custom methods.
public interface EmployeeRepository extends CrudRepository<EmployeeEntity, Long>
{
public Slice<EmployeeEntity> findByFirstName(String firstName, Pageable pageable);
}Remember that if we use PagingAndSortingRepository, the default return type is Page.
Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by("email").descending());
Slice<EmployeeEntity> slicedResult = repository.findByFirstName("alex", paging);
List<EmployeeEntity> employeeList = slicedResult.getContent();6. Demo
In this demo, the default page number is 0, the page size is 10, and the default sort column is ‘id’.
Now invoke these URLs one by one and observe the outputs.
- http://localhost:8080/employees?pageSize=5
- http://localhost:8080/employees?pageSize=5&pageNo=1
- http://localhost:8080/employees?pageSize=5&pageNo=2
- http://localhost:8080/employees?pageSize=5&pageNo=1&sortBy=email
- http://localhost:8080/employees?pageSize=5&pageNo=1&sortBy=firstName
Drop me your questions in the comments related to how to paginate and sort using Spring Data JPA.
Happy Learning !!