Spring Boot Pagination and Sorting example

We’ve known how to build Spring Boot Rest CRUD Apis with Spring Data JPA. In this tutorial, I will continue to make Pagination and Sorting example using Spring JPA and Pageable.

Related Post:
– Spring Boot Thymeleaf Pagination and Sorting example
– Spring Boot, Spring Data JPA – Rest CRUD API example
– Spring Boot Sort/Order by multiple Columns | Spring Data JPA
– Spring Boot @ControllerAdvice & @ExceptionHandler example
– Spring Boot Unit Test for JPA Repository
– Spring Boot Unit Test for Rest Controller
– Documentation: Spring Boot Swagger 3 example

More Practice:
– Spring Boot Token based Authentication with Spring Security & JWT
– Deploy Spring Boot App on AWS – Elastic Beanstalk
– Docker Compose: Spring Boot and MySQL example

Clients for this Server:
– React with Material-UI / React with react-table v7
– Angular 8 / Angular 10 / Angular 11 / Angular 12 / Angular 13 / Angular 14
– Vue with Bootstrap / Vuetify

Spring Boot Pagination & Sorting example overview

Assume that we have tutorials table in database like this:

Our Spring Boot Application will provide APIs for Pagination and Sorting by Ascending or Descending as following example:

• /api/tutorials
  sort by [id, descending] (default) & pagination [page=0, size=3] (default)
• /api/tutorials?sort=title,asc
  sort by [title, ascending] & pagination [page=0, size=3] (default)
• /api/tutorials?sort=published,desc&sort=title,asc
  order by column [published, descending], then order by column [title, ascending] & pagination [page=0, size=3] (default)
• /api/tutorials?page=1&size=5&sort=published,desc&sort=title,asc
  order by column [published, descending], then order by column [title, ascending] & pagination [page=1, size=5]

The response data structure will look like this:

{
    "totalItems": 12,
    "tutorials": [
        ...
    ],
    "totalPages": 3,
    "currentPage": 1
}

– Sort Tutorials with default order [id, descending], default paging index (0), size (3):

– Sort Tutorials by one column, default paging index (0), size (3):

– Sort Tutorials by multiple columns, default paging index (0), size (3):

– Sort Tutorials by multiple columns with paging:

Spring Data PagingAndSortingRepository

To help us deal with this situation, Spring Data JPA provides way to implement pagination with PagingAndSortingRepository.

PagingAndSortingRepository extends CrudRepository to provide additional methods to retrieve entities using the sorting abstraction. So you can add a special Sort parameter to your query method.

public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {
  Iterable<T> findAll(Sort sort);
}

findAll(Sort sort): returns a Iterable of entities meeting the sorting condition provided by Sort object.

You can also define more derived and custom query methods with additional Sort parameter. For example, the following method returns List of Tutorials which title contains a given string:

List<Tutorial> findByTitleContaining(String title, Sort sort);

You can find more supported keywords inside method names here.

Let’s continue to explore Sort class.

Spring Data Sort and Order

The Sort class provides sorting options for database queries with more flexibility in choosing single/multiple sort columns and directions (ascending/descending).

For example, we use by(), descending(), and() methods to create Sort object and pass it to Repository.findAll():

// order by 'published' column - ascending
List<Tutorial> tutorials =
     tutorialRepository.findAll(Sort.by("published"));

// order by 'published' column, descending
List<Tutorial> tutorials =
     tutorialRepository.findAll(Sort.by("published").descending());

// order by 'published' column - descending, then order by 'title' - ascending
List<Tutorial> tutorials =
     tutorialRepository.findAll(Sort.by("published").descending().and(Sort.by("title")));

We can also create a new Sort object with List of Order objects.

List<Order> orders = new ArrayList<Order>();

Order order1 = new Order(Sort.Direction.DESC, "published");
orders.add(order1);

Order order2 = new Order(Sort.Direction.ASC, "title");
orders.add(order2);

List<Tutorial> tutorials = tutorialRepository.findAll(Sort.by(orders));

Bring Pagination and Sorting together

What if we want to do both sorting and paging the data?

CrudRepository also provides additional methods to retrieve entities using the pagination abstraction.

public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID> {
  Page<T> findAll(Pageable pageable);
}

findAll(Pageable pageable): returns a Page of entities meeting the paging condition provided by Pageable object.

Spring Data also supports many useful Query Creation from method names that we’re gonna use to filter result in this example such as:

Page<Tutorial> findByPublished(boolean published, Pageable pageable);
Page<Tutorial> findByTitleContaining(String title, Pageable pageable);

Let’s notice the Pageable parameter in Repository methods above. Spring Data infrastructure will recognizes this parameter automatically to apply pagination and sorting to database.

The Pageable interface contains the information about the requested page such as the size, the number of the page, or sort information with Sort object.

public interface Pageable {
  int getPageNumber();
  int getPageSize();
  long getOffset();
  Sort getSort();
  Pageable next();
  Pageable previousOrFirst();
  Pageable first();
  boolean hasPrevious();
  ...
}

So when we want to make paging and sorting (with or without filter) in the results, we just add Pageable to the definition of the method as a parameter.

Page<Tutorial> findAll(Pageable pageable);
Page<Tutorial> findByPublished(boolean published, Pageable pageable);
Page<Tutorial> findByTitleContaining(String title, Pageable pageable);

This is how we create Pageable objects using PageRequest class which implements Pageable interface:

Pageable paging = PageRequest.of(page, size, sort);

• page: zero-based page index, must NOT be negative.
• size: number of items in a page to be returned, must be greater than 0.
• sort: the Sort object.

You can find more details about Pagination and Filter in this post:
Spring Boot Pagination & Filter example | Spring JPA, Pageable

Spring Boot Application

You can follow step by step, or get source code in this post:
Spring Boot, Spring Data JPA – Rest CRUD API example

The Spring Project contains structure that we only need to add some changes to make the pagination and sorting work well.

Or you can get the new Github source code (including paging and sorting) at the end of this tutorial.

Data Model

This is the Tutorial entity that we’re gonna work:

model/Tutorial.java

package com.bezkoder.spring.data.jpa.pagingsorting.model;

import javax.persistence.*;

@Entity
@Table(name = "tutorials")
public class Tutorial {

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

  @Column(name = "title")
  private String title;

  @Column(name = "description")
  private String description;

  @Column(name = "published")
  private boolean published;

  public Tutorial() {

  }

  public Tutorial(String title, String description, boolean published) {
    this.title = title;
    this.description = description;
    this.published = published;
  }

  public long getId() {
    return id;
  }

  public String getTitle() {
    return title;
  }

  public void setTitle(String title) {
    this.title = title;
  }

  public String getDescription() {
    return description;
  }

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

  public boolean isPublished() {
    return published;
  }

  public void setPublished(boolean isPublished) {
    this.published = isPublished;
  }

  @Override
  public String toString() {
    return "Tutorial [id=" + id + ", title=" + title + ", desc=" + description + ", published=" + published + "]";
  }
}

Repository that supports Pagination and Sorting

Early in this tutorial, we know PagingAndSortingRepository, but in this example, for keeping the continuity and taking advantage Spring Data JPA, we continue to use JpaRepository which extends PagingAndSortingRepository interface.

repository/TutorialRepository.java

package com.bezkoder.spring.data.jpa.pagingsorting.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;

import com.bezkoder.spring.data.jpa.pagingsorting.model.Tutorial;

public interface TutorialRepository extends JpaRepository<Tutorial, Long> {
  Page<Tutorial> findByPublished(boolean published, Pageable pageable);

  Page<Tutorial> findByTitleContaining(String title, Pageable pageable);
  
  List<Tutorial> findByTitleContaining(String title, Sort sort);
}

In the code above, we use add pageable parameter with Spring Query Creation to find all Tutorials which title containing input string.

More Derived queries at:
JPA Repository query example in Spring Boot

Custom query with @Query annotation:
Spring JPA @Query example: Custom query in Spring Boot

Controller with Pagination & Sorting

To get multiple sort request parameters, we use @RequestParam String[] sort with defaultValue = "id,desc".

Before writing the Controller method to handle the case, let’s see what we retrieve with the parameters:

• ?sort=column1,direction1: sorting single column
  String[] sort is an array with 2 elements: [“column1”, “direction1”]
• ?sort=column1,direction1&sort=column2,direction2: sorting multiple columns
  String[] sort is also an array with 2 elements: [“column1, direction1”, “column2, direction2”]

That’s why we need to check if the first item in the array contains "," or not.

We also need to convert "asc"/"desc" into Sort.Direction.ASC/Sort.Direction.DES for working with Sort.Order class.

Generally, in the HTTP request URLs, paging parameters are optional. So if our Rest API supports pagination, we should provide default values to make paging work even when Client does not specify these parameters.

controller/TutorialController.java

package com.bezkoder.spring.data.jpa.pagingsorting.controller;

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.data.domain.Sort.Order;
...
import com.bezkoder.spring.data.jpa.pagingsorting.model.Tutorial;
import com.bezkoder.spring.data.jpa.pagingsorting.repository.TutorialRepository;

@RestController
@RequestMapping("/api")
public class TutorialController {

  @Autowired
  TutorialRepository tutorialRepository;
  ...

  @GetMapping("/tutorials")
  public ResponseEntity<Map<String, Object>> getAllTutorialsPage(
      @RequestParam(required = false) String title,
      @RequestParam(defaultValue = "0") int page,
      @RequestParam(defaultValue = "3") int size,
      @RequestParam(defaultValue = "id,desc") String[] sort) {

    try {
      List<Order> orders = new ArrayList<Order>();

      if (sort[0].contains(",")) {
        // will sort more than 2 fields
        // sortOrder="field, direction"
        for (String sortOrder : sort) {
          String[] _sort = sortOrder.split(",");
          orders.add(new Order(getSortDirection(_sort[1]), _sort[0]));
        }
      } else {
        // sort=[field, direction]
        orders.add(new Order(getSortDirection(sort[1]), sort[0]));
      }

      List<Tutorial> tutorials = new ArrayList<Tutorial>();
      Pageable pagingSort = PageRequest.of(page, size, Sort.by(orders));

      Page<Tutorial> pageTuts;
      if (title == null)
        pageTuts = tutorialRepository.findAll(pagingSort);
      else
        pageTuts = tutorialRepository.findByTitleContaining(title, pagingSort);

      tutorials = pageTuts.getContent();

      if (tutorials.isEmpty()) {
        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
      }

      Map<String, Object> response = new HashMap<>();
      response.put("tutorials", tutorials);
      response.put("currentPage", pageTuts.getNumber());
      response.put("totalItems", pageTuts.getTotalElements());
      response.put("totalPages", pageTuts.getTotalPages());

      return new ResponseEntity<>(response, HttpStatus.OK);
    } catch (Exception e) {
      return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
    }
  }
}

In the code above, we accept paging parameters using @RequestParam annotation for page, size, sort. By default, 3 Tutorials will be fetched from database in page index 0, order by id (descending).

Next, we create a Pageable object with page, size, sort.
Then check if the title parameter exists or not.

• If it is null, we call Repository findAll(pagingSort) with pagingSort is the Pageable object above.
• If Client sends request with title, use findByTitleContaining(title, pagingSort).

Both methods return a Page object. We call:

• getContent() to retrieve the List of items in the page.
• getNumber() for current Page.
• getTotalElements() for total items stored in database.
• getTotalPages() for number of total pages.

Conclusion

In this post, we have learned how to make Pagination and Sorting in Spring Boot application using Spring Data JPA, Sort class and Pageable interface.

We also see that JpaRepository supports a great way to make sorting, paging and filter methods without need of boilerplate code.

Custom query with @Query annotation:
Spring JPA @Query example: Custom query in Spring Boot

You can also know how to:
– handle exception in this post.
– deploy this Spring Boot App on AWS (for free) with this tutorial.
– dockerize: Docker Compose: Spring Boot and MySQL example

Unit Test:
– Spring Boot Unit Test for JPA Repository
– Spring Boot Unit Test for Rest Controller

Happy learning! See you again.

Further Reading

• Spring Data Pageable with custom query in Spring Boot
• Spring Data JPA Reference Documentation
• org.springframework.data.domain.Sort
• Secure Spring Boot App with Spring Security & JWT Authentication

React Pagination Client that works with this Server:
– React Table Pagination using react-table v7
– React Pagination with API using Material-UI

Angular Client working with this server:
– Angular 8 Pagination example | ngx-pagination
– Angular 10 Pagination example | ngx-pagination
– Angular 11 Pagination example | ngx-pagination
– Angular 12 Pagination example | ngx-pagination
– Angular 13 Pagination example | ngx-pagination
– Angular 14 Pagination example | ngx-pagination

Or Vue Client:
– Vue Pagination with Axios and API example
– Vuetify Pagination (Server side) example

Source Code

You can find the complete source code for this tutorial on Github.

With Thymeleaf:
Spring Boot Thymeleaf Pagination and Sorting example

More Derived queries at:
JPA Repository query example in Spring Boot

Documentation: Spring Boot + Swagger 3 example (with OpenAPI 3)