Pagination and Sorting with Spring Boot, ThymeLeaf, Spring Data JPA, Hibernate, MySQL

In the previous tutorial, we have developed a CRUD web application with Spring Boot and Thymeleaf.

In this tutorial, we will extend spring boot CRUD web application and implement pagination and sorting operations using spring boot, thymeleaf, spring data JPA, Hibernate, and MySQL database.

As you know, pagination allows the users to see a small portion of data at a time (a page), and sorting allows the users to view the data in a more organized way. Both paging and sorting help the users consume information more easily and conveniently.

Let's start with the Employee Management System project which can be downloaded from this tutorial which is based on Spring Boot, Spring Data JPA, Hibernate, Thymeleaf, and MySQL database.

Prerequisite

Spring Boot CRUD Web Application with Thymeleaf, Spring MVC, Spring Data JPA, Hibernate, MySQL

Project Requirements

Here is a high-level project requirement to implement pagination and sorting for Employees List page (Employee Management System).

Users should able to:

• perform pagination on employees list page
• perform sorting on employees list page

What we will build?

We will extend spring boot CRUD web application and implement pagination and sorting using spring boot, thymeleaf, spring data JPA, Hibernate, and MySQL database.

The below screenshots summaries the pagination and sorting operations that we are going to develop in this tutorial.

Pagination:

Sorting:

First, we will implement a pagination operation step by step and then we will complete the sorting operation.

1. Pagination Implementation

Understand Spring Data JPA’s Pagination APIs

To use paging and sorting APIs provided by Spring Data JPA, your repository interface must extend the PagingAndSortingRepository interface which defines the following couple of methods (T refers to an entity class):

@NoRepositoryBean
public interface PagingAndSortingRepository < T, ID > extends CrudRepository < T, ID > {

    /**
     * Returns all entities sorted by the given options.
     *
     * @param sort
     * @return all entities sorted by the given options
     */
    Iterable < T > findAll(Sort sort);

    /**
     * Returns a {@link Page} of entities meeting the paging restriction provided in the {@code Pageable} object.
     *
     * @param pageable
     * @return a page of entities
     */
    Page < T > findAll(Pageable pageable);
}
            

JpaRepository interface extends PagingAndSortingRepository interface so if your repository interface is of type JpaRepository, you don’t have to make a change to it.

For example, use the following to get the first page from the database, with 5 items per page:

int pageNumber = 1;
int pageSize = 5;
Pageable pageable = PageRequest.of(pageNumber, pageSize);
 
Page<Product> page = repository.findAll(pageable);
            

Then you can get the actual content as follows:

List<Employee> listEmployees = page.getContent();

With a Page object you can know the total rows in the database and the total pages according to the given page size:

long totalItems = page.getTotalElements();
int totalPages = page.getTotalPages();
            

This information is useful for implementing pagination in the view with the Thymeleaf template.

Back-end changes for pagination

EmployeeService.java interface changes

Add below method to this interface:

Page<Employee> findPaginated(int pageNo, int pageSize);

The complete code:

package net.javaguides.springboot.service;

import java.util.List;

import org.springframework.data.domain.Page;

import net.javaguides.springboot.model.Employee;

public interface EmployeeService {
    List < Employee > getAllEmployees();
    void saveEmployee(Employee employee);
    Employee getEmployeeById(long id);
    void deleteEmployeeById(long id);
    Page < Employee > findPaginated(int pageNo, int pageSize);
}
                

EmployeeServiceImpl.java class changes

Add below method to EmployeeServiceImpl class:

@Override
public Page<Employee> findPaginated(int pageNo, int pageSize) {
 Pageable pageable = PageRequest.of(pageNo - 1, pageSize);
 return this.employeeRepository.findAll(pageable);
}
                    

EmployeeController.java class changes

Add below handler method to EmployeeController class to perform pagination:

@GetMapping("/page/{pageNo}")
public String findPaginated(@PathVariable(value = "pageNo") int pageNo, Model model) {
    int pageSize = 5;

    Page < Employee > page = employeeService.findPaginated(pageNo, pageSize);
    List < Employee > listEmployees = page.getContent();

    model.addAttribute("currentPage", pageNo);
    model.addAttribute("totalPages", page.getTotalPages());
    model.addAttribute("totalItems", page.getTotalElements());
    model.addAttribute("listEmployees", listEmployees);
    return "index";
}
                    

Also, we need to make a change to an existing method as below:

// display list of employees
@GetMapping("/")
public String viewHomePage(Model model) {
 return findPaginated(1, model);  
}
                    

Front-end changes for pagination

Add below pagination code to index.html:

<div th:if="${totalPages > 1}">
    <div class="row col-sm-10">
        <div class="col-sm-2">
            Total Rows: [[${totalItems}]]
        </div>
        <div class="col-sm-1">
            <span th:each="i: ${#numbers.sequence(1, totalPages)}">
      <a th:if="${currentPage != i}" th:href="@{'/page/' + ${i}}">[[${i}]]</a>
      <span th:unless="${currentPage != i}">[[${i}]]</span> &nbsp; &nbsp;
            </span>
        </div>
        <div class="col-sm-1">
            <a th:if="${currentPage < totalPages}" th:href="@{'/page/' + ${currentPage + 1}}">Next</a>
            <span th:unless="${currentPage < totalPages}">Next</span>
        </div>

        <div class="col-sm-1">
            <a th:if="${currentPage < totalPages}" th:href="@{'/page/' + ${totalPages}}">Last</a>
            <span th:unless="${currentPage < totalPages}">Last</span>
        </div>
    </div>
</div>

                    

The complete code:

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>
    <meta charset="ISO-8859-1">
    <title>Employee Management System</title>

    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">

</head>

<body>

    <div class="container my-2">
        <h1>Employees List</h1>

        <a th:href="@{/showNewEmployeeForm}" class="btn btn-primary btn-sm mb-3"> Add Employee </a>

        <table border="1" class="table table-striped table-responsive-md">
            <thead>
                <tr>
                    <th>Employee First Name</th>
                    <th>Employee Last Name</th>
                    <th>Employee Email</th>
                    <th> Actions </th>
                </tr>
            </thead>
            <tbody>
                <tr th:each="employee : ${listEmployees}">
                    <td th:text="${employee.firstName}"></td>
                    <td th:text="${employee.lastName}"></td>
                    <td th:text="${employee.email}"></td>
                    <td> <a th:href="@{/showFormForUpdate/{id}(id=${employee.id})}" class="btn btn-primary">Update</a>
                        <a th:href="@{/deleteEmployee/{id}(id=${employee.id})}" class="btn btn-danger">Delete</a>
                    </td>
                </tr>
            </tbody>
        </table>

        <div th:if="${totalPages > 1}">
            <div class="row col-sm-10">
                <div class="col-sm-2">
                    Total Rows: [[${totalItems}]]
                </div>
                <div class="col-sm-1">
                    <span th:each="i: ${#numbers.sequence(1, totalPages)}">
      <a th:if="${currentPage != i}" th:href="@{'/page/' + ${i}}">[[${i}]]</a>
      <span th:unless="${currentPage != i}">[[${i}]]</span>    
                    </span>
                </div>
                <div class="col-sm-1">
                    <a th:if="${currentPage < totalPages}" th:href="@{'/page/' + ${currentPage + 1}}">Next</a>
                    <span th:unless="${currentPage < totalPages}">Next</span>
                </div>

                <div class="col-sm-1">
                    <a th:if="${currentPage < totalPages}" th:href="@{'/page/' + ${totalPages}}">Last</a>
                    <span th:unless="${currentPage < totalPages}">Last</span>
                </div>
            </div>
        </div>
    </div>
</body>

</html>

        

2. Sorting Implementation

Understand Spring Data JPA’s Sorting API

The Spring Data JPA provides PagingAndSortingRepository interface which supports sorting and pagination with the following APIs:

@NoRepositoryBean
public interface PagingAndSortingRepository < T, ID > extends CrudRepository < T, ID > {

    /**
     * Returns all entities sorted by the given options.
     *
     * @param sort
     * @return all entities sorted by the given options
     */
    Iterable < T > findAll(Sort sort);

    /**
     * Returns a {@link Page} of entities meeting the paging restriction provided in the {@code Pageable} object.
     *
     * @param pageable
     * @return a page of entities
     */
    Page < T > findAll(Pageable pageable);
}
            

The users will be able to sort the employee's list by clicking on the column header of the table.

First, create a Sort object like this:

Sort sort = Sort.by(“fieldName”).ascending();

This will sort the result by fieldName in ascending order. fieldName must match a field name declared in the entity class.

We can also sort by more than one field, for example:

Sort sort = Sort.by("fieldName1").ascending().and(Sort.by("fieldName2").descending());

Then we pass the Sort object to create a Pageable as follows:

Pageable pageable = PageRequest.of(pageNum - 1, pageSize, sort);

Let's understand more in the example section.

Back-end changes for sorting

EmployeeService.java interface changes

Let's add two fields to the existing method:

Page<Employee> findPaginated(int pageNo, int pageSize, String sortField, String sortDirection);

The complete code:

package net.javaguides.springboot.service;

import java.util.List;

import org.springframework.data.domain.Page;

import net.javaguides.springboot.model.Employee;

public interface EmployeeService {
    List < Employee > getAllEmployees();
    void saveEmployee(Employee employee);
    Employee getEmployeeById(long id);
    void deleteEmployeeById(long id);
    Page < Employee > findPaginated(int pageNo, int pageSize, String sortField, String sortDirection);
}

EmployeeServiceImpl.java class changes

The sorting logic implemented in the below method:

@Override
public Page<Employee> findPaginated(int pageNo, int pageSize, String sortField, String sortDirection) {
    Sort sort = sortDirection.equalsIgnoreCase(Sort.Direction.ASC.name()) ? Sort.by(sortField).ascending() :
     Sort.by(sortField).descending();
 
    Pageable pageable = PageRequest.of(pageNo - 1, pageSize, sort);
    return this.employeeRepository.findAll(pageable);
}

The complete code:

package net.javaguides.springboot.service;

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

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 net.javaguides.springboot.model.Employee;
import net.javaguides.springboot.repository.EmployeeRepository;

@Service
public class EmployeeServiceImpl implements EmployeeService {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Override
    public List < Employee > getAllEmployees() {
        return employeeRepository.findAll();
    }

    @Override
    public void saveEmployee(Employee employee) {
        this.employeeRepository.save(employee);
    }

    @Override
    public Employee getEmployeeById(long id) {
        Optional < Employee > optional = employeeRepository.findById(id);
        Employee employee = null;
        if (optional.isPresent()) {
            employee = optional.get();
        } else {
            throw new RuntimeException(" Employee not found for id :: " + id);
        }
        return employee;
    }

    @Override
    public void deleteEmployeeById(long id) {
        this.employeeRepository.deleteById(id);
    }

    @Override
    public Page < Employee > findPaginated(int pageNo, int pageSize, String sortField, String sortDirection) {
        Sort sort = sortDirection.equalsIgnoreCase(Sort.Direction.ASC.name()) ? Sort.by(sortField).ascending() :
            Sort.by(sortField).descending();

        Pageable pageable = PageRequest.of(pageNo - 1, pageSize, sort);
        return this.employeeRepository.findAll(pageable);
    }
}

EmployeeController.java changes

Let's change the existing method to provide a support for sorting:

@GetMapping("/page/{pageNo}")
public String findPaginated(@PathVariable(value = "pageNo") int pageNo,
    @RequestParam("sortField") String sortField,
    @RequestParam("sortDir") String sortDir,
    Model model) {
    int pageSize = 5;

    Page < Employee > page = employeeService.findPaginated(pageNo, pageSize, sortField, sortDir);
    List < Employee > listEmployees = page.getContent();

    model.addAttribute("currentPage", pageNo);
    model.addAttribute("totalPages", page.getTotalPages());
    model.addAttribute("totalItems", page.getTotalElements());

    model.addAttribute("sortField", sortField);
    model.addAttribute("sortDir", sortDir);
    model.addAttribute("reverseSortDir", sortDir.equals("asc") ? "desc" : "asc");

    model.addAttribute("listEmployees", listEmployees);
    return "index";
}

Also provide default sorting field and sorting direction for home page:

    // display list of employees
    @GetMapping("/")
    public String viewHomePage(Model model) {
        return findPaginated(1, "firstName", "asc", model);
    }

The complete code:

package net.javaguides.springboot.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;

import net.javaguides.springboot.model.Employee;
import net.javaguides.springboot.service.EmployeeService;

@Controller
public class EmployeeController {

    @Autowired
    private EmployeeService employeeService;

    // display list of employees
    @GetMapping("/")
    public String viewHomePage(Model model) {
        return findPaginated(1, "firstName", "asc", model);
    }

    @GetMapping("/showNewEmployeeForm")
    public String showNewEmployeeForm(Model model) {
        // create model attribute to bind form data
        Employee employee = new Employee();
        model.addAttribute("employee", employee);
        return "new_employee";
    }

    @PostMapping("/saveEmployee")
    public String saveEmployee(@ModelAttribute("employee") Employee employee) {
        // save employee to database
        employeeService.saveEmployee(employee);
        return "redirect:/";
    }

    @GetMapping("/showFormForUpdate/{id}")
    public String showFormForUpdate(@PathVariable(value = "id") long id, Model model) {

        // get employee from the service
        Employee employee = employeeService.getEmployeeById(id);

        // set employee as a model attribute to pre-populate the form
        model.addAttribute("employee", employee);
        return "update_employee";
    }

    @GetMapping("/deleteEmployee/{id}")
    public String deleteEmployee(@PathVariable(value = "id") long id) {

        // call delete employee method 
        this.employeeService.deleteEmployeeById(id);
        return "redirect:/";
    }


    @GetMapping("/page/{pageNo}")
    public String findPaginated(@PathVariable(value = "pageNo") int pageNo,
        @RequestParam("sortField") String sortField,
        @RequestParam("sortDir") String sortDir,
        Model model) {
        int pageSize = 5;

        Page < Employee > page = employeeService.findPaginated(pageNo, pageSize, sortField, sortDir);
        List < Employee > listEmployees = page.getContent();

        model.addAttribute("currentPage", pageNo);
        model.addAttribute("totalPages", page.getTotalPages());
        model.addAttribute("totalItems", page.getTotalElements());

        model.addAttribute("sortField", sortField);
        model.addAttribute("sortDir", sortDir);
        model.addAttribute("reverseSortDir", sortDir.equals("asc") ? "desc" : "asc");

        model.addAttribute("listEmployees", listEmployees);
        return "index";
    }
}

Front-end changes for sorting

index.html

We make the header columns of the table sortable by adding hyperlinks with the following code:

<th>
      <a th:href="@{'/page/' + ${currentPage} + '?sortField=firstName&sortDir=' + ${reverseSortDir}}">
       Employee First Name</a>
</th>
<th>
     <a th:href="@{'/page/' + ${currentPage} + '?sortField=lastName&sortDir=' + ${reverseSortDir}}">
       Employee Last Name</a>
</th>
<th>
     <a th:href="@{'/page/' + ${currentPage} + '?sortField=email&sortDir=' + ${reverseSortDir}}">
       Employee Email</a>
</th>
<th> Actions </th>
    

We also need to change pagination part to provide sorting support like:

  <div th:if = "${totalPages > 1}">
   <div class = "row col-sm-10">
    <div class = "col-sm-2">
     Total Rows: [[${totalItems}]] 
    </div>
    <div class = "col-sm-1">
     <span th:each="i: ${#numbers.sequence(1, totalPages)}">
      <a th:if="${currentPage != i}" th:href="@{'/page/' + ${i}+ '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir}}">[[${i}]]</a>
      <span th:unless="${currentPage != i}">[[${i}]]</span>  &nbsp; &nbsp;
     </span>
    </div>
    <div class = "col-sm-1">
     <a th:if="${currentPage < totalPages}" th:href="@{'/page/' + ${currentPage + 1}+ '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir}}">Next</a>
     <span th:unless="${currentPage < totalPages}">Next</span>
    </div>
    
     <div class="col-sm-1">
        <a th:if="${currentPage < totalPages}" th:href="@{'/page/' + ${totalPages}+ '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir}}">Last</a>
     <span th:unless="${currentPage < totalPages}">Last</span>
        </div>
   </div>
  </div>
    

The complete code:

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="ISO-8859-1">
<title>Employee Management System</title>

<link rel="stylesheet"
 href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css"
 integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO"
 crossorigin="anonymous">
 
</head>
<body>

 <div class="container my-2">
 <h1>Employees List</h1>
 
 <a th:href = "@{/showNewEmployeeForm}" class="btn btn-primary btn-sm mb-3"> Add Employee </a>
 
  <table border="1" class = "table table-striped table-responsive-md">
   <thead>
    <tr>
     <th>
      <a th:href="@{'/page/' + ${currentPage} + '?sortField=firstName&sortDir=' + ${reverseSortDir}}">
       Employee First Name</a>
     </th>
     <th>
     <a th:href="@{'/page/' + ${currentPage} + '?sortField=lastName&sortDir=' + ${reverseSortDir}}">
       Employee Last Name</a>
     </th>
     <th>
     <a th:href="@{'/page/' + ${currentPage} + '?sortField=email&sortDir=' + ${reverseSortDir}}">
       Employee Email</a>
     </th>
     <th> Actions </th>
    </tr>
   </thead>
   <tbody>
    <tr th:each="employee : ${listEmployees}">
     <td th:text="${employee.firstName}"></td>
     <td th:text="${employee.lastName}"></td>
     <td th:text="${employee.email}"></td>
     <td> <a th:href="@{/showFormForUpdate/{id}(id=${employee.id})}" class="btn btn-primary">Update</a>
         <a th:href="@{/deleteEmployee/{id}(id=${employee.id})}" class="btn btn-danger">Delete</a>
     </td>
    </tr>
   </tbody>
  </table>
  
  <div th:if = "${totalPages > 1}">
   <div class = "row col-sm-10">
    <div class = "col-sm-2">
     Total Rows: [[${totalItems}]] 
    </div>
    <div class = "col-sm-1">
     <span th:each="i: ${#numbers.sequence(1, totalPages)}">
      <a th:if="${currentPage != i}" th:href="@{'/page/' + ${i}+ '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir}}">[[${i}]]</a>
      <span th:unless="${currentPage != i}">[[${i}]]</span>  &nbsp; &nbsp;
     </span>
    </div>
    <div class = "col-sm-1">
     <a th:if="${currentPage < totalPages}" th:href="@{'/page/' + ${currentPage + 1}+ '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir}}">Next</a>
     <span th:unless="${currentPage < totalPages}">Next</span>
    </div>
    
     <div class="col-sm-1">
        <a th:if="${currentPage < totalPages}" th:href="@{'/page/' + ${totalPages}+ '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir}}">Last</a>
     <span th:unless="${currentPage < totalPages}">Last</span>
        </div>
   </div>
  </div>
 </div>
</body>
</html>