Pagination and Sorting using JPA – Springboot

Pagination and Sorting using JPA – Springboot

In this article let us see how can we use pagination and sorting mechanism using Springboot JPA.

What is Pagination?

Pagination is a process to split the list of records into multiple sub-lists based on page number and size of records for specific page.

To achieve this Springboot jpa provides us with in-build paging repository which is integrated with JpaRepository.

We can also do Sorting along with pagination when displaying the records.

Now let us see an example,

Project Structure:

Pom.xml

<?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.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.javainfinite</groupId>
    <artifactId>PagingAndSorting</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>PagingAndSorting</name>
    <description>Paging and Sorting Repository</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>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
    </dependencies>

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

</project>

Engineer.java

package com.javainfinite.PagingAndSorting.model;

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

@Entity
public class Engineer {

    @Id
    @GeneratedValue
    private int id;

    @Column(name = "Name")
    private String engineerName;

    @Column(name = "Department")
    private String engineerDepartment;

    public int getId() {
        return id;
    }

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

    public String getEngineerName() {
        return engineerName;
    }

    public void setEngineerName(String engineerName) {
        this.engineerName = engineerName;
    }

    public String getEngineerDepartment() {
        return engineerDepartment;
    }

    public void setEngineerDepartment(String engineerDepartment) {
        this.engineerDepartment = engineerDepartment;
    }
}

Repository:

EngineerRepository.java

package com.javainfinite.PagingAndSorting.repository;

import com.javainfinite.PagingAndSorting.model.Engineer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface EngineerRepository extends JpaRepository<Engineer, Long> {
}

EngineerDAO.java

package com.javainfinite.PagingAndSorting.repository;

import com.javainfinite.PagingAndSorting.model.Engineer;
import org.springframework.data.domain.Page;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EngineerDAO  {

    Page<Engineer> getPagination(int records, int page);

    Page<Engineer> getPaginationAndSort(int records, int page, String sortField);

    Integer saveEngineers(List<Engineer> engineerList);

}

EngineerService.java

package com.javainfinite.PagingAndSorting.service;

import com.javainfinite.PagingAndSorting.model.Engineer;
import com.javainfinite.PagingAndSorting.repository.EngineerDAO;
import com.javainfinite.PagingAndSorting.repository.EngineerRepository;
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.stereotype.Service;

import java.util.List;

@Service
public class EngineerService implements EngineerDAO {

    @Autowired
    private EngineerRepository repository;

    @Override
    public Page<Engineer> getPagination(int records, int page) {

        Pageable pageable = PageRequest.of(page, records);
        return repository.findAll(pageable);
    }

    @Override
    public Page<Engineer> getPaginationAndSort(int records, int page, String sortField) {
        Pageable pageable = PageRequest.of(page, records, Sort.by(sortField));
        return repository.findAll(pageable);
    }

    @Override
    public Integer saveEngineers(List<Engineer> engineerList) {

        return repository.saveAll(engineerList).size();
    }
}

So what have we done here?

Pageable is an interface for pagination information and it is been implemented by pageRequest.

Page is mentioning a single page of records.

So here,

We are forming a PageRequest with total number of records that has to be fetched for a page.

It will be much clear when we see the output.

Sort.by is an method used for sorting with PageRequest.

Multiple sorting also posible,

Sort.by(sortField).and(Sort.by("Field 2")) //multiple field sorting

EngineerController.java

package com.javainfinite.PagingAndSorting.controller;

import com.javainfinite.PagingAndSorting.model.Engineer;
import com.javainfinite.PagingAndSorting.repository.EngineerDAO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
public class EngineerController {

    @Autowired
    private EngineerDAO eDao;

    @PostMapping("/saveEngineers")
    public Integer saveEngineers(@RequestBody List<Engineer> engineerList) {
        return eDao.saveEngineers(engineerList);
    }

    @GetMapping("/getPaginationEngineers")
    public Page<Engineer> getEngineers(@RequestParam("records") int records, @RequestParam("page") int page) {
        return eDao.getPagination(records, page);
    }

    @GetMapping("/getPaginationAndSortEngineers")
    public Page<Engineer> getEngineersAndSort(@RequestParam("records") int records, @RequestParam("page") int page, @RequestParam("sortField") String sortField) {
        return eDao.getPaginationAndSort(records, page, sortField);
    }
}

We have defined the end points,

/saveEngineers –> To save the records

/getPaginationEngineers –> Records fetched per page

/getPaginationAndSortEngineers –> Sorting the Records based on field.

application.properties:

spring.datasource.url={{URL}}
spring.datasource.username={{USERNAME}}
spring.datasource.password={{PASSWORD}}
spring.jpa.hibernate.ddl-auto=create
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
server.port=9090

Sample Request for Saving Engineers:

[
    {
        "engineerName": "Zelan",
        "engieerDepartment": "Chemicals"
    },
    {
        "engineerName": "Belan",
        "engieerDepartment": "Computers"
    },
    {
        "engineerName": "Delan",
        "engieerDepartment": "Civil"
    },
    {
        "engineerName": "Eelan",
        "engieerDepartment": "Electronics"
    },
    {
        "engineerName": "Feelan",
        "engieerDepartment": "Communications"
    },
    {
        "engineerName": "Helan",
        "engieerDepartment": "FireMechanism"
    },
    {
        "engineerName": "Kelan",
        "engieerDepartment": "Chemicals"
    },
    {
        "engineerName": "Lelan",
        "engieerDepartment": "Laboratory"
    },
    {
        "engineerName": "Jelan",
        "engieerDepartment": "Literature"
    },
    {
        "engineerName": "Nelan",
        "engieerDepartment": "Mechanics"
    },
    {
        "engineerName": "Oelan",
        "engieerDepartment": "Science"
    },
    {
        "engineerName": "Melan",
        "engieerDepartment": "NA"
    },
    {
        "engineerName": "Qelan",
        "engieerDepartment": "Proton"
    },
    {
        "engineerName": "Gelan",
        "engieerDepartment": "Neutron"
    },
    {
        "engineerName": "Jelan",
        "engieerDepartment": "Physics"
    }
]

From our code, once we save the engineers we are returning the count of number of records

Lets Save the records to our db

Now we have saved total of 15 records in database.

Now let us try our first Rest end Point.

Let us try to fetch 4 records for each page starting from page 0,

Now let us try to navigate to next page, page 1 with same 4 records,

From the output, we can see that for the second page the records starts from id-5 (i.e. next 4 records in second page)

Now lets us try to do the same thing with sorting by name,

From the output we can see that the records are arranged based on Name sorted order.

Please post your comments for queries.

Code can be downloaded here