Learn to request and display only chunk of data from database using pagination and sorting inputs and query parameters in spring boot and spring data applications.
Paging and sorting is mostly required when we are displaying domain data in tabular format in UI.
Pagination consist of two fields – page size and page number. Sorting is done on a single of multiple fields in the table.
1. JPA Entity
In this post, we are taking example of EmployeeEntity
class. Each entity instance represent an employee record in database.
import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name="TBL_EMPLOYEES") public class EmployeeEntity { @Id @GeneratedValue private Long id; @Column(name="first_name") private String firstName; @Column(name="last_name") private String lastName; @Column(name="email", nullable=false, length=200) private String email; //Setters and getters @Override public String toString() { return "EmployeeEntity [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", email=" + email + "]"; } }
2. PagingAndSortingRepository
PagingAndSortingRepository is an extension of CrudRepository
to provide additional methods to retrieve entities using the pagination and sorting abstraction. It provides two methods :
- Page findAll(Pageable pageable) – returns a
Page
of entities meeting the paging restriction provided in thePageable
object. - 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> { }
3. Accepting paging and sorting parameters
Generally, paging and sorting parameters are optional and thus part of request URL as query parameters. If any API supports paging and sorting, ALWAYS provide default values to its parameters – to be used when client does not choose 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 below spring mvc controller, we are accepting paging and sorting parameters using pageNo, pageSize and sortBy query parameters. Also, by default '10'
employees will be fetched from database in 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 pass 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 techniques
4.1. Paging WITHOUT sorting
To apply only pagination in 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. Paging WITH sorting
To apply only pagination in 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);
4.3. 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 sorting 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);
5. Difference between Page and Slice
5.1. Page
The findAll(Pageable pageable)
method by default returns a Page
object. A Page
object provides lots of extra useful information other than just list of employees in current page.
E.g. A Page
object has the number of total pages, number of the current page
and well as whether current page is first page or last page.
Finding total pages invokes an additional count() query causing an extra overhead cost. Be sure when you are using it.
5.2. Slice
Slice
is very much similar to Page
, except it does not provide the number of total pages in database. It helps in improving performance when we do not need to display total number pages in UI.
Generally, Slice
is used in case navigation is consist of Next page and Previous page links.
To use Slice
, we have implement our own custom methods.
public interface EmployeeRepository extends CrudRepository<EmployeeEntity, Long> { public Slice<EmployeeEntity> findByFirstName(String firstName, Pageable pageable); }
Remember that is we use PagingAndSortingRepository
, 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. Spring boot paging and sorting demo
In this demo, default page number is 0, page size is 10 and 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 comments related to how to paginate and sort using Spring Data JPA.
Happy Learning !!
what is the best way if i want to do pagination but i cant use hibernate. I can only use jdbctemplate. Right now I am manually appending Limit and Offset params to query to fetch the results.
Hi
Can the same example be used to do pagination with MongoDB too?
Is there any way to paginate an aggregated list being fetched as a part of the entity object I am fetching from SQL DB?? For example:
Class A contains an aggregated list of objects of Class B
I wish to fetch an Object of Class A (using “fetchById()”) but the aggregated list of Objects of Class B need to be paginated.
the sorting isn’t working in this example
Can you plz explain?
Example worked but not working with thymleaf
Is it possible to use pagingandsorting repository with spring data jdbc
i want the pagination for the mysql procedure response . how can i proceed with that kind of responses in spring boot
In above example it will show 10 employees based on ID. but what if I have 10000 Employees and I want to fetch in chunk of 100 employees. above will give first 100 what about next set of data ?? How to get remaining ??
http://localhost:8080/employees?pageSize=100&pageNo=1 //1 to 100
http://localhost:8080/employees?pageSize=100&pageNo=2 //101 to 200
http://localhost:8080/employees?pageSize=100&pageNo=3 //201 to 300
and so on. Am I missing anything?
Is there any way if we can override default Paging configurations?
A questions here: https://stackoverflow.com/questions/27032433/set-default-page-size-for-jpa-pageable-object
Hi,
Is there any way if we can get the data in paginated formated and where status is Active ?
Absolutely. Why you see it a challenge?
Your example looks very nice because you are implementing PagingAndSortingRepository which is better and new way doing pagination with Spring & Spring Boot.