Spring Boot- Pagination and Sorting using Spring Data JPA

By Atul Rai | Last Updated: November 10, 2019 Previous       Next

This page will walk through Spring Boot Pagination and Sorting using Spring Data JPA + MySQL database. Pagination will improve the performance of the application when we have a large dataset. It presents the smaller chunks of a dataset to the user.

Spring Data JPA provides the interface PagingAndSortingRepository which extends the CrudRepository to implement the pagination and sorting in Spring Boot application. There are two methods declared in the PagingAndSortingRepository interface:

1. Iterable<T> findAll(Sort sort) – returns all entities sorted by the given options.
2. Page<T> findAll(Pageable pageable) – returns a Page of entities meeting the paging restriction provided in the Pageable object.

Technology Used

Find the list of all technologies used in this application.

1. Spring Tool Suite 4
2. JDK 8
3. Spring Boot 2.1.9.RELEASE
4. Spring Data 2.1.9.RELEASE
5. MySQL Database
6. Maven 3

Dependency Required

To enable pagination and soring in the application, the following dependencies must in the build path. Add them to pom.xml.

<dependencies>
	<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>
</dependencies>

Project Structure

The final project structure of our application in STS 4 IDE will look like as follows:

1. Initial Setup

We can’t do anything without dataset. Pagination and sorting will apply to the data, so the first step is to create some dummy dataset. Here we have provided the MySQL database schema.

CREATE DATABASE /*!32312 IF NOT EXISTS*/`websparrow` /*!40100 DEFAULT CHARACTER SET latin1 */;

/*Table structure for table `employee` */

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_fopic1oh5oln2khj8eat6ino0` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;

/*Data for the table `employee` */

insert  into `employee`(`id`,`email`,`first_name`,`last_name`,`city`) values (1,'[email protected]','Sandeep','Jaiswal','New Delhi'),(2,'[email protected]','Prince','Kumar','Mumbai'),(3,'[email protected]','Abhinav','Rathi','New Delhi'),(17,'[email protected]','Gaurav','Aggrawal','Mumbai'),(18,'[email protected]','Sandeep','Sharma','Pune'),(19,'[email protected]','Shani','Tripathi','Banglore'),(20,'[email protected]','Ritesh','Singh','Hyderabad'),(21,'[email protected]','Piyush','Kumar','Banglore'),(22,'[email protected]','Sunandana','Tiwari','Kolkata'),(23,'[email protected]','Kumkum','Singh','New Delhi'),(24,'[email protected]','Pooja','Kumari','Pune'),(25,'[email protected]','Naga','Reddy','Chennai'),(26,'[email protected]','Jitendra','Upadhyay','Mumbai'),(27,'[email protected]','Ashish','Singh','Pune'),(28,'[email protected]','Ashutosh','Pandey','Mumbai'),
(29,'[email protected]','Vipin','Kumar','Hyderabad');

2. Entity

Let’s say we have an Employee entity and every employee uniquely identified by id.

package org.websparrow.entity;

@Entity
@Table(name = "employee")
public class Employee implements Serializable {

	private static final long serialVersionUID = 5009021772979651472L;
	@Id
	private Integer id;
	private String firstName;
	private String lastName;
	private String email;
	private String city;

	// constructor, getters, and setters
}

2. Creating a Repository

To access the Employee, we will need EmployeeRepository which extends the PagingAndSortingRepository provided by Spring Data and it enables pagination and sorting functionality in the application. You can add your derived query methods.

package org.websparrow.repository;

import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
import org.websparrow.entity.Employee;

@Repository
public interface EmployeeRepository
		extends PagingAndSortingRepository<Employee, Integer> {

}

3. Controller

EmployeeController class autowired the EmployeeRepository to get data from the database and exposes the REST endpoint.

3.1 Pagination

Create a PageRequest object, which is an implementation of the Pageable interface, pass the PageRequest object as an argument to the repository method.

We can create a PageRequest object by passing in the requested page number and the page size.

Note: The default page counts start with zero.

@GetMapping(value = "/employee/page/{pageNo}")
public Page<Employee> getEmployeeWithPaination(@PathVariable int pageNo) {

	/**
	 * Return the Page object containing list of 4 employees of requested
	 * page no.
	 */
	Pageable pageable = PageRequest.of(pageNo, PAGE_SIZE);
	return employeeRepository.findAll(pageable);
}

3.2 Pagination and Sorting

Similarly, we can sort the paginated result by passing the instance of Sort class.

@GetMapping(value = "/employee/page/sort/{pageNo}")
public Page<Employee> getEmployeePaginatedAndSorted(
		@PathVariable int pageNo) {

	/**
	 * Return the Page object containing list of 3 employees of requested
	 * page and sorted by the first name
	 */
	Pageable pageableSortedByFirstName = PageRequest.of(pageNo, PAGE_SIZE,
			Sort.by("firstName"));

	/**
	 * Return the Page object containing list of 3 employees of requested
	 * page and sorted by the city in descending order
	 */
	Pageable pageableSortedByCityDesc = PageRequest.of(pageNo, PAGE_SIZE,
			Sort.by("city").descending());

	/**
	 * Return the Page object containing list of 3 employees of page 0 and
	 * sorted by the city in descending order and first name in ascending
	 * order
	 */
	Pageable pageableSortedByCityDescFirstNameAsc = PageRequest.of(pageNo,
			PAGE_SIZE, Sort.by("city").descending().and(Sort.by("firstName")));

	// return employeeRepository.findAll(pageableSortedByFirstName);
	
	// return employeeRepository.findAll(pageableSortedByCityDesc);
		
	return employeeRepository.findAll(pageableSortedByCityDescFirstNameAsc);
}

3.3 Sorting

However, what if we want all records in the sorted form, just pass the instance of Sort to Iterable<T> findAll(Sort sort).

@GetMapping(value = "/employee/sort")
public Iterable<Employee> getEmployeeSored() {

	/**
	 * Returns all entities sorted by the given options.
	 */
	return employeeRepository.findAll(Sort.by("lastName"));
}

Finally, the complete EmployeeController class looks like:

package org.websparrow.controller;

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.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.websparrow.entity.Employee;
import org.websparrow.repository.EmployeeRepository;

@RestController
@RequestMapping("/api")
public class EmployeeController {

	private static final int PAGE_SIZE = 3;

	@Autowired
	private EmployeeRepository employeeRepository;

	@GetMapping(value = "/employee/page/{pageNo}")
	public Page<Employee> getEmployeePaginated(@PathVariable int pageNo) {

		/**
		 * Return the Page object containing list of 3 employees of requested
		 * page no.
		 */
		Pageable pageable = PageRequest.of(pageNo, PAGE_SIZE);
		return employeeRepository.findAll(pageable);
	}

	@GetMapping(value = "/employee/page/sort/{pageNo}")
	public Page<Employee> getEmployeePaginatedAndSorted(
			@PathVariable int pageNo) {

		/**
		 * Return the Page object containing list of 3 employees of requested
		 * page and sorted by the first name
		 */
		Pageable pageableSortedByFirstName = PageRequest.of(pageNo, PAGE_SIZE,
				Sort.by("firstName"));

		/**
		 * Return the Page object containing list of 3 employees of requested
		 * page and sorted by the city in descending order
		 */
		Pageable pageableSortedByCityDesc = PageRequest.of(pageNo, PAGE_SIZE,
				Sort.by("city").descending());

		/**
		 * Return the Page object containing list of 3 employees of page 0 and
		 * sorted by the city in descending order and first name in ascending
		 * order
		 */
		Pageable pageableSortedByCityDescFirstNameAsc = PageRequest.of(pageNo,
				PAGE_SIZE,
				Sort.by("city").descending().and(Sort.by("firstName")));

		// return employeeRepository.findAll(pageableSortedByFirstName);

		// return employeeRepository.findAll(pageableSortedByCityDesc);

		return employeeRepository.findAll(pageableSortedByCityDescFirstNameAsc);
	}

	@GetMapping(value = "/employee/sort")
	public Iterable<Employee> getEmployeeSored() {

		/**
		 * Returns all entities sorted by the given options.
		 */
		return employeeRepository.findAll(Sort.by("lastName"));
	}
}

4. application.properties

Configure the database connection strings in the application.properties file to established the connection between application and database.

# MySQL database connection strings
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.url=jdbc:mysql://localhost:3306/websparrow

# JPA property settings
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.show_sql=true

Run the application

The SpringBootPagintionApp class contains the main method and responsible to start the application.

package org.websparrow;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootPaginationApp {

	public static void main(String[] args) {
		SpringApplication.run(SpringBootPaginationApp.class, args);
	}
}

Test the application

To test the application, start the Spring Boot application by executing the above class and hit the below URL in your favorite web browser:

Note: The default page counts start with zero.

1. To get paginated records: http://localhost:8080/api/employee/page/0 where zero (0) is requested page no.
2. Paginated and sorted records: http://localhost:8080/api/employee/page/sort/3 where 3 is requested page no.
3. Sorted records: http://localhost:8080/api/employee/sort

References

1. Spring Data derived findBy query methods example
2. Spring Data CrudRepository interface Example
3. Spring Boot RESTful Web Service Example
4. Interface PagingAndSortingRepository