How to handle pagination and sorting in Spring Data?

Photo by Pixabay on Pexels.com

Let’s say you want to handle pagination and sorting for your application and you don’t want to do the hard work of writing SQL queries or setting criteria using an ORM tool.

Spring Data provides a very simple and efficient way to handle pagination and sorting.

All you have to do is create a repository interface which extends PaginationAndSortingRepository interface provided by Spring Data!

Here are the steps (using Spring Boot):

STEP 1: Add dependencies:

Add spring data dependency:

<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

In addition I have added spring web dependency to test my changes through a REST service and in memory database H2 dependency to perform the database operations.

Here is the entire pom :

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.6.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.sprinbboot</groupId>
	<artifactId>pagination</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>pagination</name>
	<description>Demo project for Spring Boot Pagination</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

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

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

STEP 2: Create the entity class

package com.springboot.pagination;

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

@Entity
@Table(name="order_table")
public class Order {

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private Long id;

	private String name;

	private float price;

	private int quantity;

	private String category;

	/**
	 * @return the id
	 */
	public Long getId() {
		return id;
	}

	/**
	 * @param id the id to set
	 */
	public void setId(Long id) {
		this.id = id;
	}

	/**
	 * @return the name
	 */
	public String getName() {
		return name;
	}

	/**
	 * @param name the name to set
	 */
	public void setName(String name) {
		this.name = name;
	}

	/**
	 * @return the price
	 */
	public float getPrice() {
		return price;
	}

	/**
	 * @param price the price to set
	 */
	public void setPrice(float price) {
		this.price = price;
	}

	/**
	 * @return the quantity
	 */
	public int getQuantity() {
		return quantity;
	}

	/**
	 * @param quantity the quantity to set
	 */
	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}

	/**
	 * @return the category
	 */
	public String getCategory() {
		return category;
	}

	/**
	 * @param category the category to set
	 */
	public void setCategory(String category) {
		this.category = category;
	}

}

I have created an Order entity class with an id , name , price , quantity and category fields.

Note that I have mentioned the table name as order_table . ‘Order’ is a restricted word in SQL so you cannot use it to create a table . So I manually entered the table name instead of skipping it else Spring Data automatically tries to generate the table using the entity name.

STEP 3 – Create the repository

Here is where the prime action takes place.

Create a repository which extends PaginationAndSortingRepository provided by Spring Data.

package com.springboot.pagination;

import org.springframework.data.repository.PagingAndSortingRepository;

public interface OrderRepository extends PagingAndSortingRepository<Order, Long> {

}

The entity class name ‘Order’ and the data type of the identifier ‘Long’ are passed as generic parameters to the interface.

Just creating this interface lets you enjoy the pagination and sorting features of Spring Data.

Now let’s make use of it.

STEP 4 – Create the client code

I have created a REST controller client which will invoke the repository.

I have made the page number , page size and the field on which to sort by , all customizable.

Here is the code :

package com.springboot.pagination;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class OrderController {

	@Autowired
	private OrderRepository repo;

	@GetMapping("/getOrders")
	public List<Order> getOrders(@RequestBody Map<String, String> input) {

		int pageNo = Integer.parseInt(input.get("pageNo"));

		int pageSize = Integer.parseInt(input.get("pageSize"));

		Sort sortBy = Sort.by(input.get("sortBy"));

		PageRequest pageRequest= PageRequest.of(pageNo, pageSize, sortBy);

		Page<Order> pages = repo.findAll(pageRequest);

		return pages.getContent();

	}

}

PaginationAndSortingRepository provides a method findAll() which takes an instance of ‘PageRequest’ as a parameter.

It’s in this pageRequest instance that we set our pagination and sorting parameters.

Let’s say you want the first page among pages of size 5.

You can create a page request instance this way:

PageRequest pageRequest = PageRequest.of(0,5);

The values starts from 0 , so the first page is denoted by 0 . The second parameter takes the page size.

In the controller code , I have fetched this from the input passed by the client .

To sort based on a field just add Sort.by(fieldName) to the page request. This can be dropped if sorting is not required.

By default sorting is in ascending order. You can change it to descending by doing this :

Sort.by(fieldName).descending()

STEP 5 – TEST

Now let’s test the changes.

I have inserted few records through data.sql file kept under resources folder.

I have used in memory database by adding a dependency in the pom.xml

Here is the data.sql:

insert into order_table(id,name,price,quantity,category) values(1,'Almonds - 100 gm pack',300.0,200,'Nuts');
insert into order_table(id,name,price,quantity,category) values(2,'Raisins - 100 gm pack',200.0,100,'Nuts');
insert into order_table(id,name,price,quantity,category) values(3,'Onion 1 kg pack',150,50,'Vegetables');
insert into order_table(id,name,price,quantity,category) values(4,'Banana 10 kg back',2000,40,'Vegetables');
insert into order_table(id,name,price,quantity,category) values(5,'Apple 200 gm pack',500,80,'Vegetables');
insert into order_table(id,name,price,quantity,category) values(6,'Iphone',30000,100,'Electronics');
insert into order_table(id,name,price,quantity,category) values(7,'Boss Headphone',8000,180,'Electronics');
insert into order_table(id,name,price,quantity,category) values(8,'Iphone Charger',1000,200,'Electronics');
insert into order_table(id,name,price,quantity,category) values(9,'Macbook',300000,100,'Electronics');
insert into order_table(id,name,price,quantity,category) values(10,'Trimmer',3000,700,'Electronics');

I have not added anything in application.properties file as Spring Boot automatically creates a database named ‘testdb’ on seeing the H2 database dependency in pom file.

Here is a sample request through postman rest client:

And here is the output:

[
    {
        "id": 4,
        "name": "Banana 10 kg back",
        "price": 2000.0,
        "quantity": 40,
        "category": "Vegetables"
    },
    {
        "id": 10,
        "name": "Trimmer",
        "price": 3000.0,
        "quantity": 700,
        "category": "Electronics"
    },
    {
        "id": 7,
        "name": "Boss Headphone",
        "price": 8000.0,
        "quantity": 180,
        "category": "Electronics"
    },
    {
        "id": 6,
        "name": "Iphone",
        "price": 30000.0,
        "quantity": 100,
        "category": "Electronics"
    },
    {
        "id": 9,
        "name": "Macbook",
        "price": 300000.0,
        "quantity": 100,
        "category": "Electronics"
    }
]

Below is the algorithm executed by Spring Data:

First it sorts all the items by their price.

And then it divides the records into pages of size 5.

And then it fetches the second page .

And so I have got the costliest five items among the records added .

And they are sorted in ascending order.

2 thoughts

  1. How does spring boot handle keyset pagination? Offset pagination offers poor performance for large scale tables

    Like

  2. Spring Boot doesn’t provide a choice to select the pagination strategy. We can go only with their default implementation which is offset pagination.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s