Spring Boot Pagination and Sorting Example | Spring RestAPIs + Spring JPA using Pageable & Sort object requests

SpringBoot Pagination Sorting Examples (Filtering) with MySQL database using Spring JPA

When we have a large dataset and we want to present it to the user in smaller chunks, pagination and sorting is often helpful solution. So in the tutorial, I introduce how to build “Spring Boot Pagination and Sorting Example” use Spring JPA APIs of PagingAndSortingRepository to do the task with SpringBoot project example.

Related posts:


Youtube Video Guide – How to implement SpringBoot Pagination RestAPI

Goal – Spring Boot Pagination and Sorting Example

We create a ‘SpringBoot Pagination and Sorting Example’ project as below struture:

SpringBoot Pagination and Sorting Structure
SpringBoot project Structure – Pagination and Sorting Examples

– Paging Request:

SpringBoot Paging Request Examples with page=0, size=5
SpringBoot Paging Request Examples with page=0, size=5

– Pagination and Sorting request:

SpringBoot Pagination and Sorting Request Examples with page=0, size=5
SpringBoot Pagination and Sorting Request Examples with page=0, size=5

PagingAndSorting Repository

PagingAndSortingRepository is an extension of CrudRepository to provide additional methods to retrieve entities using the pagination and sorting abstraction. Here is the sourcecode of it:


@NoRepositoryBean
public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {
	Iterable<T> findAll(Sort sort);
	Page<T> findAll(Pageable pageable);
}
  • findAll(Sort sort) returns all entities sorted by the given options.
  • findAll(Pageable pageable) Returns a page of entities meeting the paging restriction provided in the Pageable object.

Here is the hierarchy of PagingAndSortingRepository :

SpringBoot PagingAndSortingRepository hierarchy
PagingAndSortingRepository hierarchy

So JpaRepository of Spring JPA is an alternative solution for PagingAndSortingRepository:


@NoRepositoryBean
public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {

Spring JPA Pagination

With the tutorial “Spring Boot Pagination and Sorting Example” we use Spring JPA’s methods:
Page<T> findAll(Pageable pageable);

It returns a Page of entities meeting the paging restriction provided in the Pageable object.

– Here is how the Page interface is defined:

SpringBoot Pagination and Sorting Example - Page Interface
Page Interface

– Here is how to Pageable interface is defined:

Pagable Interface for pagination, filtering and sorting
Pagable Interface for pagination, filtering and sorting

Examples coding:


@Autowired
CustomerRepository customerRepository;

@GetMapping("/pageable")
public Page<Customer> retrieveCustomerWithPaging(@Param(value = "page") int page, 
											@Param(value = "size") int size){
	Pageable requestedPage = PageRequest.of(page, size);
	Page<Customer> customers  = customerRepository.findAll(requestedPage);
	return customers;
}

We use the PageRequest to construct a Pageable object then pass it to the findAll() method of PagingAndSortingRepository.


public class PageRequest extends AbstractPageRequest {
	private final Sort sort;

	protected PageRequest(int page, int size, Sort sort) {}

	public static PageRequest of(int page, int size) {
		return of(page, size, Sort.unsorted());
	}

	public static PageRequest of(int page, int size, Sort sort) {}

	public static PageRequest of(int page, int size, Direction direction, String... properties) {}

	public Sort getSort() {}

	@Override
	public Pageable next() {}
	
	@Override
	public PageRequest previous() {}

	@Override
	public Pageable first() {}
}

We can also do a pagination with the findAll method included a condition:

@GetMapping("/pageablebysalary")
public Slice<Customer> retrieveCustomerBySalaryWithPaging(@Param(value = "salary") double salary,
															@Param(value = "page") int page, 
															@Param(value = "size") int size){
	Pageable requestedPage = PageRequest.of(page, size);
	Slice<Customer> customers  = customerRepository.findAllBySalary(salary, requestedPage);
	return customers;
}

We can return a Page object to client or an alternative solution likes Slice or a list objects. We also can return a custom class object to client with just needed properties:

Class org.springframework.data.domain.Slice<T>

	Methods:	
		org.springframework.data.domain.Slice.getNumber()
		org.springframework.data.domain.Slice.getSize()
		org.springframework.data.domain.Slice.getNumberOfElements()
		org.springframework.data.domain.Slice.getContent()
		org.springframework.data.domain.Slice.hasContent()
		org.springframework.data.domain.Slice.getSort()
		org.springframework.data.domain.Slice.isFirst()
		org.springframework.data.domain.Slice.isLast()
		org.springframework.data.domain.Slice.hasNext()
		org.springframework.data.domain.Slice.hasPrevious()
		org.springframework.data.domain.Slice.getPageable()
		org.springframework.data.domain.Slice.nextPageable()
		org.springframework.data.domain.Slice.previousPageable()
		org.springframework.data.domain.Slice.map(Function<? super T, ? extends U>)
		org.springframework.data.domain.Slice.nextOrLastPageable()
		org.springframework.data.domain.Slice.previousOrFirstPageable()

1. create a custom Response object from a returned Page object:


Pageable requestedPage = PageRequest.of(page, size);
Page<Customer> customers  = customerRepository.findAll(requestedPage);
Response res = new Response(customers.getContent(), customers.getTotalPages(),
								customers.getNumber(), customers.getSize());

2. return just a list of customers to client:


Pageable requestedPage = PageRequest.of(page, size);
Page<Customer> customers  = customerRepository.findAll(requestedPage);
return customers.toList();

3. return a Slice object:


Pageable requestedPage = PageRequest.of(page, size);
Slice<Customer> customers  = customerRepository.findAllBySalary(salary, requestedPage);

A Page<T> instance, in addition to having the list of Customer, also knows about the total number of available pages. It triggers an additional count query to achieve it. To avoid such an overhead cost, we can instead return a Slice<T> or a List<T>.

A Slice only knows about whether the next slice is available or not.

How to use Spring Boot Pagination with Filtering?

For filtering data with pagination, Spring JPA provides many useful Query Creation from method names:


Slice findAllBySalary (double salary, Pageable pageable);
Page findAllByAgeGreaterThan(int age, Pageable pageable);
  • GreaterThan examples findAllByAgeGreaterThan(int age, Pageable pageable); means … where x.age> ?1
  • Is, Equals examples findAllBySalary (double salary, Pageable pageable) means … where x.salary = ?1

You can findout more about the supporting keywords for searching with SpringJPA, go to the refference link: here

Paging and Sorting

For sorting with Spring data JPA, we use the function: Iterable<T> findAll(Sort sort);.

We can use public static methods by() to build the Sort objects:

  • public static Sort by(String... properties) creates a new Sort for the given properties.
  • public static Sort by(List≶Order> orders) creates a new Sort for the given list Orders
  • public static Sort by(Order... orders) create a new Sort for the given Orders
  • public static Sort by(Direction direction, String... properties)

If we want to both sort and page our data, We can do that by passing the sorting details into our PageRequest object itself:

Pageable sortedBySalary = 
  PageRequest.of(0, 3, Sort.by("salary"));
 
Pageable sortedBySalaryDesc = 
  PageRequest.of(0, 3, Sort.by("salary").descending());
 
Pageable sortedBySalaryDescAgeAndFirstnameAsc = 
  PageRequest.of(0, 5, Sort.by("salary").descending().and(Sort.by("age")).and(Sort.by("firstname")));

How to implement a Pagination Filtering and Sorting function with Spring JPA?

To do the pagination and sorting and filering combination, we can use the Sort object combining with keyword Query Creation, examples:


Pageable requestedPage = PageRequest.of(page, size, Sort.by("salary")
														.descending()
														.and(Sort.by("age"))
														.and(Sort.by("firstname")));

Page<Customer> customers  = customerRepository.findAllByAgeGreaterThan(age, requestedPage);

Development – How to code it?

Create a SpringBoot project

We create a SpringBoot project with 3 dependencies:

  • Spring Data JPA
  • Spring Web
  • MySQL driver or PostgreSQL driver

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>		

Create Spring JPA Data Model

We create a Customer model class with 7 attributes: id, firstname, lastname, address, age, salary, copyrightBy.

Customer Model class
Customer Model class
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="customer")
public class Customer {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private long id;
	
	@Column
	private String firstname;
	
	@Column
	private String lastname;

	@Column
	private String address;
	
	@Column
	private int age;
	
	@Column
	private double salary;
	
	@Column(columnDefinition = "varchar(255) default '@ https://loizenai.com'")
	private String copyrightBy;

@Column specifies the mapped column for a persistent property or field. If no Column annotation is specified, the default values apply.
javax.persistence.Id specifies the primary key of an entity.
javax.persistence.Entity specifies that the class is an entity. This annotation is applied to the entity class.

Database Configuration

Configuration database in application.properties file:

spring.datasource.url=jdbc:mysql://localhost:3306/loizenaidb
spring.datasource.username=root
spring.datasource.password=12345
spring.jpa.generate-ddl=true

#drop & create table again, good for testing, comment this in production
spring.jpa.hibernate.ddl-auto=create

Implement CustomerRepository interface

Create interface CustomerRepository extends PagingAndSortingRepository:

package com.loizenai.springboot.pagingansorting.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.loizenai.springboot.pagingansorting.model.Customer;

@Repository
public interface CustomerRepository extends PagingAndSortingRepository<Customer, Long>{	
	Slice<Customer> findAllBySalary (double salary, Pageable pageable);
	Page<Customer> findAllByAgeGreaterThan(int age, Pageable pageable);
}

Implement RestAPIs Controller

– Create a RestController class:

@org.springframework.web.bind.annotation.RestController
@RequestMapping("/api/customers")
@CrossOrigin(origins = "http://localhost:4200")
public class RestController {
        @Autowired
	CustomerRepository customerRepository;
...
}

RestAPIs for Requesting Customer List with Pagination

We define 3 APIs:

  • Url API /api/customers/pageable returns a Page object
  • Url API /api/customers/pageable/list returns a List of Customer object
  • Url API /api/customers/custom/pageable returns a custom user-defined Response object
@GetMapping("/pageable")
public Page<Customer> retrieveCustomerWithPaging(@Param(value = "page") int page, 
											@Param(value = "size") int size){
	Pageable requestedPage = PageRequest.of(page, size);
	Page<Customer> customers  = customerRepository.findAll(requestedPage);
	return customers;
}

@GetMapping("/custom/pageable")
public Response retrieveCustomer(@Param(value = "page") int page, 
											@Param(value = "size") int size){
	Pageable requestedPage = PageRequest.of(page, size);
	Page<Customer> customers  = customerRepository.findAll(requestedPage);
	Response res = new Response(customers.getContent(), customers.getTotalPages(),
									customers.getNumber(), customers.getSize());
	
	return res;
}

@GetMapping("/pageable/list")
public List<Customer> retrieveCustomerListWithPaging(@Param(value = "page") int page, 
												@Param(value = "size") int size){
	Pageable requestedPage = PageRequest.of(page, size);
	Page<Customer> customers  = customerRepository.findAll(requestedPage);
	return customers.toList();
}

RestAPIs for Customer Pagination with Paging and Filtering

We define 2 URLs for filtering and paging:

  • /api/customers/pageablebysalary is used to retrieve a Customer page with a salary criteria for filtering
  • /api/customers/pageable/byagegreaterthan is used to retrieve a Customer page with a age criteria for filtering
@GetMapping("/pageablebysalary")
public Slice<Customer> retrieveCustomerBySalaryWithPaging(@Param(value = "salary") double salary,
															@Param(value = "page") int page, 
															@Param(value = "size") int size){
	Pageable requestedPage = PageRequest.of(page, size);
	Slice<Customer> customers  = customerRepository.findAllBySalary(salary, requestedPage);
	return customers;
}

@GetMapping("/pageable/byagegreaterthan")
public Slice<Customer> retrieveCustomerByAgeGreaterThan(@Param(value = "age") int age,
															@Param(value = "page") int page, 
															@Param(value = "size") int size){
	Pageable requestedPage = PageRequest.of(page, size);
	Page<Customer> customers  = customerRepository.findAllByAgeGreaterThan(age, requestedPage);
	return customers;
}

RestAPIs to retrieve Customer List with Pagination and Sorting

We implement a Url API /api/customers/pagingandsorting for getting a customer list with pagination and sorting:


@GetMapping("/pagingandsorting")
public Page<Customer> pagingAndSortingCustomers(@Param(value = "page") int page, 
											@Param(value = "size") int size){
	Pageable requestedPage = PageRequest.of(page, size, Sort.by("salary")
															.descending()
															.and(Sort.by("age"))
															.and(Sort.by("firstname")));
	Page<Customer> customers  = customerRepository.findAll(requestedPage);
	return customers;		
}

SpringBoot RestAPIs to Pagination Filtering and Sorting

We implement a RestAPIs at URL /api/customers/pagingfilteringandsorting to retrive Customer page with a age criteria for filtering and also using age field and firstname field for sorting.

@GetMapping("/pagingfilteringandsorting")
public Page<Customer> pagingFilteringAndSortingCustomersByAge(@Param(value = "salary") int age@Param(value = "age") int age, 
						                                    @Param(value = "page") int page, 
															@Param(value = "size") int size){
	Pageable requestedPage = PageRequest.of(page, size, Sort.by("salary")
															.descending()
															.and(Sort.by("age"))
															.and(Sort.by("firstname")));
	
	Page<Customer> customers  = customerRepository.findAllByAgeGreaterThan(age, requestedPage);
	return customers;		
}

Testing – Spring Boot Pagination and Sorting Example

Below is a suite of testcases for “Spring Boot Pagination and Sorting Example” project:

Testcase 1 – SpringBoot Pagination request

– Request 1 at URL http://localhost:8080/api/customers/pageable?page=0&size=5
Return a Page object

SpringBoot Pagination and Sorting Example - Make a pagination request 1
Make a pagination request 1

– Request 2 at URL http://localhost:8080/api/customers/pageable/list?page=0&size=5
Just return a List of Customer

Make a pagination request 2
Make a pagination request 2

– Request 3 at URL http://localhost:8080/api/customers/custom/pageable?page=0&size=5
Return a custom user-defined Response object

Make a pagination request 3
Make a pagination request 3

Testcase 2 – SpringBoot Pagination and Filtering RestAPIs

– Request 1 at URL http://localhost:8080/api/customers/pageablebysalary?salary=4000&page=0&size=5
Do a pagination with salary=4000 condition for paging

Make a pagination request 4
Make a pagination request 4

– Request 2 at URL http://localhost:8080/api/customers/pageable/byagegreaterthan?age=40&page=0&size=5
Do a pagination and filtering with age > 40:

Pagination and Filtering with age greater than 40
Pagination and Filtering with age greater than 40

Testcase 3 – SpringBoot Paging and Sorting request

– Request at URL for Paging and sorting:
http://localhost:8080/api/customers/pagingandsorting?page=0&size=5

SpringBoot Paging and Sorting request
Paging and Sorting request

Testcase 4 – SpringBoot Pagination, Filtering and Sorting request

– Request at URL for Paging and sorting:
http://localhost:8080/api/customers/pagingfilteringandsorting?salary=3000&age=25&page=0&size=5

Pagination Filtering and Sorting request
Pagination Filtering and Sorting request

Bootstrap View

We use Bootstrap framework to develop a view for the tutorial “Spring Boot Pagination and Sorting Example”:

Bootstrap View for Pagination and Sorting
Bootstrap View for Pagination and Sorting

Sourcecode

Below is a clearly & running sourcecode for “Spring Boot Pagination and Sorting Example”:

SpringBootPagingAndSorting

– Github Sourcecode for SpringBoot Pagination and Sorting Example:

SpringBoot Pagination Filtering Sorting – GitHub Sourcecode

Further Reading for SpringBoot Pagination and Sorting Example

Related posts:


One thought on “Spring Boot Pagination and Sorting Example | Spring RestAPIs + Spring JPA using Pageable & Sort object requests”

Leave a Reply

Your email address will not be published. Required fields are marked *