Pagination and Sorting Spring Data JPA

1. What is Pagination and Sorting?

Pagination is the process of splitting the content into small chunks from the large data set.

Sorting is a mechanism to arrange the data either ascending or descending by a given field.

The ascending sorting means basically alphabetically sorting the data from A to Z and descending sorting means alphabetically sorting the data from Z-A.

In this post, we will create a simple GET API using Spring Boot which implements Pagination and Sorting.

2. Create Spring Boot Project

If you don’t know how to create a Spring Boot project then you can refer to my previous post Create Spring Boot project using Spring Initialzer where I have written about it in detail.

3. Maven Dependency

<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>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<scope>runtime</scope>
</dependency>

In the dependency section, We’ve included h2database which is a in-memory database and if you want to use other databases like MySQL you can add mysql-connector dependency. The example we are going to implement will work for other databases as well. The only thing is to add a new dependency and configure its details in the application.properties file accordingly.

4. Create Classes and Interfaces.

4.1 Employee Entity

package com.codersathi.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Employee {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;
	private String name;
	private String address;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

}

4.2 Employee Repository

package com.codersathi.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.codersathi.entity.Employee;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

}

4.3. Employee Response DTO

package com.codersathi.dto;

import java.util.List;
import com.codersathi.entity.Employee;

public class EmployeeResponseDto {

	private long total;
	private List<Employee> employees;

	public long getTotal() {
		return total;
	}

	public void setTotal(long total) {
		this.total = total;
	}

	public List<Employee> getEmployees() {
		return employees;
	}

	public void setEmployees(List<Employee> employees) {
		this.employees = employees;
	}

}

4.4. Employee Service

package com.codersathi.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import com.codersathi.dto.EmployeeResponseDto;
import com.codersathi.entity.Employee;
import com.codersathi.repository.EmployeeRepository;

@Service
public class EmployeeService {

	@Autowired
	private EmployeeRepository employeeRepository;

	public EmployeeResponseDto getEmployees(Pageable pageable) {
		Page<Employee> page = employeeRepository.findAll(pageable);
		EmployeeResponseDto response = new EmployeeResponseDto();
		response.setTotal(page.getTotalElements());
		response.setEmployees(page.getContent());
		return response;
	}
	
}

4.5. Employee Controller

package com.codersathi.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.codersathi.dto.EmployeeResponseDto;
import com.codersathi.service.EmployeeService;

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

	@Autowired
	private EmployeeService employeeService;
	
	@GetMapping
	public EmployeeResponseDto getEmplloyees(Pageable pageable) {
		return employeeService.getEmployees(pageable);
	}
}

4.6 application.properties File

The application.properties file should be created automatically while generating the project using Spring Initializer. The configuration details for h2-database is:


server.port=8080

spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=none
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

spring.h2.console.enabled=true

# This can be optional.
spring.jpa.show-sql=true

4.7 data.sql File

The data.sql file will be loaded automatically by spring boot itself. Hence, create it inside src/main/resources directory and add the following query so that the query will be executed during application startup.

create table employee (id bigint generated by default as identity, address varchar(255), name varchar(255), primary key (id));

insert into employee (name, address) values ('Radha Krishnan', 'Kathmandu'); 
insert into employee (name, address) values ('Pankaj', 'Pokhara');

The above query will first create an employee table and inserts two records.

5. Test

Now, the time is to test our application by starting it. Go and find the main class of your project and start it.

Since we have defined server.port 8080 in application.properties file. Now, we can fetch all data.

5.1 Fetch All Data

http://localhost:8080/employees

Output:

In this output, we can see that there are two employees.

There are two employees in our database. Now, let’s fetch only one data using the following URL:

5.2 Fetch Paginated Data

http://localhost:8080/employees?size=1&page=0

Output

In this output, we can see that there are two employees but it displays only one employee because in the URL we’ve given size=1 and page =0. Which basically means to return 1 data from page 0.

5.3 Fetch Data Using Sorting

http://localhost:8080/employees?size=10&page=0&sort=name,asc