Pagination and Sorting in Spring Boot Data JPA

In this post we will learn how to implement pagination and sorting in Spring Boot Application.

Why Pagination?

As you build the application, you might come into issue where there is whole bunch of data’s that is being queried from database. This data can be overwhelming for a single request and will also take up lot of bandwidth finally degrading the performance of application.

To deal with such a case, we need pagination. Pagination display only the chunk of data from database. For example, if database dumps 100 records to the api then pagination will filter only 10 if the page size is 10.

PaginationAndSortingRepository or JpaRepository

PagingAndSortingRepository is an extension of CRUDRepository to facilitates additional method that retrieves chunks of data using pagination abstraction. There are couple of methods that comes out of the box with pagination.

Page findAll(Pageable pageable): This method takes pageable object as argument and returns Page object. Pageable interface contains different attributes like pagesize, number of page etc. Pageable object can be created using PageRequest class. A Page object provides lots of extra useful information other than just list of entities in current page.
Itterable findAll(Sort sort) – This method accepts Sort object which is sorting options and return Itterable entities.

Pageable paging = PageRequest.of(pageNum, pageSize) creates Pageable object from PageRequest with two arguments as page number and page size.

In this example, we will be extending JpaRepository which comes with PaginationAndSorting Repository out of the box.

You might also want to read:

Spring Data JPA also supports pageable query creation by method names like

Page<Post> findByName(String name, Pageable pageable);

Pagination without Sorting

Pageable paging = PageRequest.of(pageNo, pageSize);

Page<Post> result = repository.findByName(paging);

Pagination with Sorting

Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by("name));

Page<Post> result = repository.findByName(paging);

PageNo is the page number in pagination, pageSize is the number of results to be retrieved in each page and inside Sort class attributes is passed as argument to sort the result.

Setup Spring Boot JPA Project

Let’s use Spring Starter tool to generate a project with one click. You can add the three dependency we need while generating the project: Spring Web, Spring Data JPA.

Once you download the project, your pom should look like this:

<?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.6.7</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.fullstackcoder</groupId>
	<artifactId>pagination</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>pagination</name>
	<description>Demo project for Spring Boot Pagination and Sorting</description>
	<properties>
		<java.version>11</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>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

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

</project>

Configure Spring Boot JPA with H2 Database, MySQL or PostgreSQL

We need one more dependency for datasource. As i have mentioned above, you can use any datasource you like and for this part we need to add one more dependency depending on the datasource.

Configure MySQL

Configure PostgreSQL

Configure H2

To add H2 dependency in pom.xml.

<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<scope>runtime</scope>
</dependency>

long with it, we also need to update application.properties file.

spring.datasource.url=jdbc:h2:mem:your_database_name //i will be using jpadb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto= update
spring.h2.console.enabled=true
# h2-console is default path for H2

spring.datasource.url is used to define database url. “jdbc:h2:mem:[your_database_name]” is how you define the url for H2

spring.datasource.username and spring.datasource.password are the credentials to connect to database.

spring.jpa.hibernate.ddl-auto is used to initialize database. It will create database tables automatically based on our defined model. You can also set this property to following values: none, validate, update, create-drop. For production, it is better to use validate as property.

spring.h2.console.enabled=true will tell Spring to start H2 Database administration tool. We can access this on http://localhost:8080/h2-console

spring.jpa.show-sql=true is to log database transaction on the console.

spring.jpa.properties.hibernate.dialect is to configure Hibernate for JPA implementation. It’s different for MySQL and PostgreSQL.

Define Data Model class for Spring JPA Pagination

Since our main objective is to understand Pagination, we will be defining Post class inside package named model.

Post

package com.fullstackcoder.pagination.model;

import javax.persistence.*;

@Entity
@Table(name = "post")
public class Post {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "name")
    private String name;
    @Column(name = "description")
    private String description;
    @Column(name = "author")
    private String author;


    public Post() {
    }

    public Post(String name, String description, String author) {
        this.name = name;
        this.description = description;
        this.author = author;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String toString(){//overriding the toString() method
        return id+" "+name+" "+description+ " "+ author;
    }
    
}

@Entity

This annotation tells Spring JPA that the class is a persistent Java class and the table has to be mapped on to the database.

@Column

Table is supposed to have different columns and this column is mapped to attributes name in Java persistent class. @Column annotation tells Spring JPA that the attribute is a column of the associated table. With name property column name can be given too.

@Table

The annotation maps the class name to a table name in datasource. “name” property defines table name.

@Id

A table needs to have a unique key as an attribute based on which you can perform query for uniqueness. @Id annotation tells that the column is a primary key. The primary key can be generated by different ways. @GeneratedValue defines how the primary key will be generated.

Create Repository Interface for Spring JPA Pagination

Inside the repository package, let’s create one interface that extends JpaRepository; PostRepository inside repository package.

It provides some Generic Crud operations like save(), findById(), deleteById(), findAll() and so on. There are many such method which comes out of the box and can use without even implementing.

PostRepository

package com.fullstackcoder.pagination.repository;


import com.fullstackcoder.pagination.model.Post;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface PostRepository extends JpaRepository<Post, Integer> {

    Page<Post> findByName(String name, Pageable pageable);
    Page<Post> findAll(Pageable pageable);
}

Repository class extends JpaRepository; JpaRepository provides methods and functionalities to integrate pagination in the service. For this example, we have used two method name. findByName is the query creating by method name. Similarly pagination can implemented on any method name or custom query as well.

Define Rest Controller for Pagination

package com.fullstackcoder.pagination.controller;


import com.fullstackcoder.pagination.model.Post;
import com.fullstackcoder.pagination.repository.PostRepository;
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.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Optional;


@RestController
public class PostController {

    @Autowired
    PostRepository postRepository;


    @GetMapping("/post/{id}")
    public ResponseEntity<Post> getPostById(@PathVariable("id") int id) {
        Optional<Post> post = postRepository.findById(id);
        if(post.isEmpty()) {
            return new ResponseEntity<>(HttpStatus.NOT_FOUND);
        }
        return new ResponseEntity<>(post.get(), HttpStatus.OK);
    }

    @PostMapping("/post")
    public ResponseEntity<Post> createPost(@RequestBody Post post) {
        Post resPost = postRepository.save(new Post(post.getName(), post.getDescription(), post.getAuthor()));
        return new ResponseEntity<>(resPost, HttpStatus.CREATED);
    }

    @GetMapping(value = "/post/page/{pageNum}")
    public ResponseEntity<Page> pageSizePageable(@PathVariable Integer pageNum) {
        Pageable paging = PageRequest.of(pageNum, 2);
        return new ResponseEntity<>(postRepository.findAll(paging), HttpStatus.OK);
    }

    @GetMapping(value = "/post/page/{pageNum}/sort")
    public ResponseEntity<List<Post>> sortPageable(@PathVariable Integer pageNum) {
        Pageable paging = PageRequest.of(pageNum, 2, Sort.by("name"));
        return new ResponseEntity<>(postRepository.findByName("Post", paging).toList(), HttpStatus.OK);
    }

    @GetMapping(value = "/post/findall/pageable")
    public ResponseEntity<Page> findAllPageable(Pageable pageable) {

        return new ResponseEntity<>(postRepository.findAll(pageable), HttpStatus.OK);
    }

    @DeleteMapping("/post/{id}")
    public ResponseEntity<HttpStatus> deletePostById(@PathVariable("id") int id) {
        postRepository.deleteById(id);
        return new ResponseEntity(HttpStatus.NO_CONTENT);
    }

}

Page return type retrieves more information other than just list of Post like:

{
    "content": [
        {
            "id": 1,
            "name": "Post",
            "description": "this is the test",
            "author": "author"
        }
    ],
    "pageable": {
        "sort": {
            "empty": true,
            "sorted": false,
            "unsorted": true
        },
        "offset": 0,
        "pageNumber": 0,
        "pageSize": 20,
        "paged": true,
        "unpaged": false
    },
    "totalPages": 1,
    "totalElements": 5,
    "last": true,
    "size": 20,
    "number": 0,
    "sort": {
        "empty": true,
        "sorted": false,
        "unsorted": true
    },
    "numberOfElements": 5,
    "first": true,
    "empty": false
}

Testing of Spring Data JPA Pagination

Project structure

Postman testing

Once you run the application, it will be exposed on 8080 port and will be using postman to test the functionalities.

Postman: Get all pageable

Postman: Get post by page number

Conclusion

In this article, we discussed how can we achieve pagination in Spring Boot application. Along with pagination, how to sort the result and enhance performance of our application.