Spring Data JPA Paging and Sorting example

By mkyong | Updated: September 30, 2023

Viewed: 3,450 (+128 pv/w)

This article shows how to do Spring Data JPA paging and sorting using the PagingAndSortingRepository interface.

Technologies used:

Spring Boot 3.1.2
Spring Data JPA
H2 in-memory database
Java 17
Maven 3
JUnit 5
Spring Integration Tests with TestRestTemplate
Unit Tests with Mocking (Mockito)

Table of contents:

1. Extends PagingAndSortingRepository
2. Extends JpaRepository
3. Pageable and Sort examples
4. Spring Data JPA Paging and Sorting example
5. Spring Data JPA Paging and Sorting example (Demo)
6. Tests for Paging and Sorting
7. Download Source Code
8. References

1. Extends PagingAndSortingRepository

Review the PagingAndSortingRepository interface, the two methods, findAll(Sort sort) and findAll(Pageable pageable), provide the core functionalities for sorting and pagination.

PagingAndSortingRepository.java


public interface PagingAndSortingRepository<T, ID> extends Repository<T, ID> {

  Iterable<T> findAll(Sort sort);

  Page<T> findAll(Pageable pageable);
}

In simple, make the repository bean extend the PagingAndSortingRepository interface, and Spring Data will automatically provide the paging and sorting features.

2. Extends JpaRepository

2.1 The ListPagingAndSortingRepository extends PagingAndSortingRepository to make the method findAll(Sort sort); return a List instead of the Iterable.

ListPagingAndSortingRepository.java


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

2.2 Review the JpaRepository interface, it extends ListCrudRepository, ListPagingAndSortingRepository and QueryByExampleExecutor.

JpaRepository.java


  public interface JpaRepository<T, ID>
    extends ListCrudRepository<T, ID>, ListPagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
      //...
    }

2.3 This means we just need to make our repository bean extend the JpaRepository interface, and the repository bean will be able to perform CRUD, paging, sorting, and execution queries.

BookRepository.java


package com.mkyong.book;

import org.springframework.data.jpa.repository.JpaRepository;

public interface BookRepository extends JpaRepository<Book, Long> {
  //...
}

3. Pageable and Sort examples

Below is the usage for the Pageable and Sort.


  Sort sort = Sort.by(Sort.Direction.DESC, "properties")
  Pageable pageable = PageRequest.of(pageNo, pageSize, sort);

Assume we have 6 books


  Book b1 = new Book(1L, "Book A"); // properties = id and title
  Book b2 = new Book(2L, "Book B");
  Book b3 = new Book(3L, "Book C");
  Book b4 = new Book(4L, "Book D");
  Book b5 = new Book(5L, "Book E");
  Book b6 = new Book(6L, "Book F");

3.1 Sort by title in the desc direction.

Page 0 (page starts with 0) and size of 4.


  Sort sort = Sort.by(Sort.Direction.DESC, "title")
  Pageable pageable = PageRequest.of(0, 4, sort);

  return bookRepository.findAll(pageable);

Output


  Book b6 = new Book(6L, "Book F");
  Book b5 = new Book(5L, "Book E");
  Book b4 = new Book(4L, "Book D");
  Book b3 = new Book(3L, "Book C");

Page 1 and size of 4.


  Sort sort = Sort.by(Sort.Direction.DESC, "title")
  Pageable pageable = PageRequest.of(1, 4, sort);

Output


  Book b2 = new Book(2L, "Book B");
  Book b1 = new Book(1L, "Book A");

3.2 Sort by title in the asc direction.

Page 0 and size of 4.


  Sort sort = Sort.by(Sort.Direction.ASC, "title")
  Pageable pageable = PageRequest.of(0, 4, sort);

Output


  Book b1 = new Book(1L, "Book A");
  Book b2 = new Book(2L, "Book B");
  Book b3 = new Book(3L, "Book C");
  Book b4 = new Book(4L, "Book D");

Page 1 and size of 4.


  Sort sort = Sort.by(Sort.Direction.ASC, "title")
  Pageable pageable = PageRequest.of(1, 4, sort);

Output


  Book b5 = new Book(5L, "Book E");
  Book b6 = new Book(6L, "Book F");

4. Spring Data JPA Paging and Sorting example

We will create a REST endpoint to provide a find method to return a list of books; the find method can support paging and sorting.

4.1 Project Directory

Review a project directory

4.2 Project Dependencies

Declares the spring-boot-starter-data-jpa dependency, the PagingAndSortingRepository is inside the Spring Data JPA.

pom.xml


  <!-- Spring Web MVC -->
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
  </dependency>

  <!-- Spring Data JPA -->
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>

  <!-- H2 in-memory database  -->
  <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
  </dependency>

  <!-- Spring Test -->
  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
  </dependency>

  <!-- Support Java 8 date/time type -->
  <dependency>
      <groupId>com.fasterxml.jackson.datatype</groupId>
      <artifactId>jackson-datatype-jsr310</artifactId>
  </dependency>

4.3 JPA Entity

A Book entity class for JPA.

Book.java


package com.mkyong.book;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

import java.math.BigDecimal;
import java.time.LocalDate;

@Entity
public class Book {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;
  private String title;
  private BigDecimal price;
  private LocalDate publishDate;

  // for JPA only, no use
  public Book() {
  }

  //getters, setters, constructors
}

4.4 Repository

Make the repository extend the JpaRepository, and we have CRUD and paging and sorting features.

BookRepository.java


package com.mkyong.book;

import org.springframework.data.jpa.repository.JpaRepository;

// JpaRepository extends ListPagingAndSortingRepository
// ListPagingAndSortingRepository extends PagingAndSortingRepository
public interface BookRepository extends JpaRepository<Book, Long> {

}

4.5 Service

BookService.java


package com.mkyong.book;

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;

@Service
public class BookService {

  @Autowired
  private BookRepository bookRepository;

  public Page<Book> findAll(int pageNo, int pageSize, String sortBy, String sortDirection) {
      Sort sort = Sort.by(Sort.Direction.fromString(sortDirection), sortBy);
      Pageable pageable = PageRequest.of(pageNo, pageSize, sort);

      return bookRepository.findAll(pageable);
  }

}

4.6 Controller

For the /books endpoint, we provide default values for each of the @RequestParam. In this example, by default, it returns a list of books with a size of 10, at page 0, sorted by id in the asc direction.

BookController.java


package com.mkyong.book;

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

import java.util.List;

@RestController
public class BookController {

  @Autowired
  private BookService bookService;

  @GetMapping("/books")
  public List<Book> findAll(
          @RequestParam(defaultValue = "0") int pageNo,
          @RequestParam(defaultValue = "10") int pageSize,
          @RequestParam(defaultValue = "id") String sortBy,
          @RequestParam(defaultValue = "ASC") String sortDirection) {
      Page<Book> result = bookService.findAll(pageNo, pageSize, sortBy, sortDirection);
      return result.getContent();

  }

}

4.7 Start Spring Boot Application

Create a CommandLineRunner bean and insert 6 books during the Spring Boot startup, prepare for demonstration later.

StartApplication.java


package com.mkyong;

import com.mkyong.book.Book;
import com.mkyong.book.BookRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

@SpringBootApplication
public class StartApplication {

    private static final Logger log = LoggerFactory.getLogger(StartApplication.class);

    public static void main(String[] args) {
        SpringApplication.run(StartApplication.class, args);
    }

    @Autowired
    BookRepository bookRepository;

    @Bean
    public CommandLineRunner startup() {

        return args -> {

            Book b1 = new Book("Book A",
                    BigDecimal.valueOf(9.99),
                    LocalDate.of(2023, 8, 31));
            Book b2 = new Book("Book B",
                    BigDecimal.valueOf(19.99),
                    LocalDate.of(2023, 7, 31));
            Book b3 = new Book("Book C",
                    BigDecimal.valueOf(29.99),
                    LocalDate.of(2023, 6, 10));
            Book b4 = new Book("Book D",
                    BigDecimal.valueOf(39.99),
                    LocalDate.of(2023, 5, 5));
            Book b5 = new Book("Book E",
                    BigDecimal.valueOf(49.99),
                    LocalDate.of(2023, 4, 1));
            Book b6 = new Book("Book F",
                    BigDecimal.valueOf(59.99),
                    LocalDate.of(2023, 3, 1));

            bookRepository.saveAll(List.of(b1, b2, b3, b4, b5, b6));

        };

    }
}

5. Spring Data JPA Paging and Sorting example (Demo)

Starts the Spring Boot App.

Terminal


  ./mvnw spring-boot:run

5.1 Testing the paging and sorting using the curl command.

Run with the following request parameters:

PageNo = 0
PageSize = 4
SortBy = title
SortDirection = DESC

Terminal


curl -s "http://localhost:8080/books?pageNo=0&pageSize=4&sortBy=title&sortDirection=desc" | python3 -m json.tool

P.S The | python3 -m json.tool is optional; For pretty print output using python.

Output

Terminal


[
  {
      "id": 6,
      "title": "Book F",
      "price": 59.99,
      "publishDate": "2023-03-01"
  },
  {
      "id": 5,
      "title": "Book E",
      "price": 49.99,
      "publishDate": "2023-04-01"
  },
  {
      "id": 4,
      "title": "Book D",
      "price": 39.99,
      "publishDate": "2023-05-05"
  },
  {
      "id": 3,
      "title": "Book C",
      "price": 29.99,
      "publishDate": "2023-06-10"
  }
]

Run with the following request parameters:

PageNo = 1
PageSize = 4
SortBy = title
SortDirection = DESC

Terminal


curl -s "http://localhost:8080/books?pageNo=1&pageSize=4&sortBy=title&sortDirection=desc" | python3 -m json.tool

Output

Terminal


[
 {
     "id": 2,
     "title": "Book B",
     "price": 19.99,
     "publishDate": "2023-07-31"
 },
 {
     "id": 1,
     "title": "Book A",
     "price": 9.99,
     "publishDate": "2023-08-31"
 }
]

6. Tests for Paging and Sorting

Tests for the Spring controller, service, and repository.

All tests should PASSED.

6.1 Spring Integration Tests with TestRestTemplate

Below is a Spring integration test for the REST service using TestRestTemplate. Default, the tests configure the H2 in-memory database. Read the comments for self-explanatory.

BookControllerIntegrationTest.java


package com.mkyong;

import com.mkyong.book.Book;
import com.mkyong.book.BookRepository;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.web.client.TestRestTemplate;
import org.springframework.boot.test.web.server.LocalServerPort;
import org.springframework.core.ParameterizedTypeReference;
import org.springframework.http.HttpMethod;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.jupiter.api.Assertions.assertEquals;

// Integration test using TestRestTemplate
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class BookControllerIntegrationTest {

    @LocalServerPort
    private Integer port;

    @Autowired
    private TestRestTemplate restTemplate;

    private String BASEURI;

    @Autowired
    BookRepository bookRepository;

    // pre-populated with books for test
    @BeforeEach
    void testSetUp() {

        BASEURI = "http://localhost:" + port;

        bookRepository.deleteAll();

        Book b1 = new Book("Book A",
                BigDecimal.valueOf(9.99),
                LocalDate.of(2023, 8, 31));
        Book b2 = new Book("Book B",
                BigDecimal.valueOf(19.99),
                LocalDate.of(2023, 7, 31));
        Book b3 = new Book("Book C",
                BigDecimal.valueOf(29.99),
                LocalDate.of(2023, 6, 10));
        Book b4 = new Book("Book D",
                BigDecimal.valueOf(39.99),
                LocalDate.of(2023, 5, 5));
        Book b5 = new Book("Book E",
                BigDecimal.valueOf(49.99),
                LocalDate.of(2023, 4, 1));
        Book b6 = new Book("Book F",
                BigDecimal.valueOf(59.99),
                LocalDate.of(2023, 3, 1));

        bookRepository.saveAll(List.of(b1, b2, b3, b4, b5, b6));
    }

    @Test
    void testFindAllWithPagingAndSorting() {

        ParameterizedTypeReference<List<Book>> typeRef = new ParameterizedTypeReference<>() {
        };

        // sort by price, desc, get page 0 , size = 4
        ResponseEntity<List<Book>> response = restTemplate.exchange(
                BASEURI + "/books?pageNo=0&pageSize=4&sortBy=title&sortDirection=desc",
                HttpMethod.GET,
                null,
                typeRef
        );

        assertEquals(HttpStatus.OK, response.getStatusCode());

        // test list of objects
        List<Book> result = response.getBody();
        assert result != null;

        assertEquals(4, result.size());

        // Get Book C, D, E, F
        assertThat(result).extracting(Book::getTitle)
                .containsExactlyInAnyOrder(
                        "Book C", "Book D", "Book E", "Book F");
        assertThat(result).extracting(Book::getPrice)
                .containsExactlyInAnyOrder(
                        new BigDecimal("59.99"),
                        new BigDecimal("49.99"),
                        new BigDecimal("39.99"),
                        new BigDecimal("29.99")
                );


        // sort by price, desc, get page 1 , size = 4
        ResponseEntity<List<Book>> response2 = restTemplate.exchange(
                BASEURI + "/books?pageNo=1&pageSize=4&sortBy=title&sortDirection=desc",
                HttpMethod.GET,
                null,
                typeRef
        );

        assertEquals(HttpStatus.OK, response2.getStatusCode());

        // test list of objects
        List<Book> result2 = response2.getBody();
        assert result2 != null;

        assertEquals(2, result2.size());

        // Get Book A, B
        assertThat(result2).extracting(Book::getTitle)
                .containsExactlyInAnyOrder(
                        "Book A", "Book B");
        assertThat(result2).extracting(Book::getPrice)
                .containsExactlyInAnyOrder(
                        new BigDecimal("9.99"),
                        new BigDecimal("19.99")
                );

    }

}

6.2 Testing the Repository with @DataJpaTest

BookRepositoryTest.java


package com.mkyong;

import com.mkyong.book.Book;
import com.mkyong.book.BookRepository;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
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 java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.jupiter.api.Assertions.assertEquals;

@DataJpaTest
public class BookRepositoryTest {

  @Autowired
  private BookRepository bookRepository;

  @BeforeEach
  void setUp() {

      bookRepository.deleteAll();

      Book b1 = new Book("Book A",
              BigDecimal.valueOf(9.99),
              LocalDate.of(2023, 8, 31));
      Book b2 = new Book("Book B",
              BigDecimal.valueOf(19.99),
              LocalDate.of(2023, 7, 31));
      Book b3 = new Book("Book C",
              BigDecimal.valueOf(29.99),
              LocalDate.of(2023, 6, 10));
      Book b4 = new Book("Book D",
              BigDecimal.valueOf(39.99),
              LocalDate.of(2023, 5, 5));
      Book b5 = new Book("Book E",
              BigDecimal.valueOf(49.99),
              LocalDate.of(2023, 4, 1));
      Book b6 = new Book("Book F",
              BigDecimal.valueOf(59.99),
              LocalDate.of(2023, 3, 1));

      bookRepository.saveAllAndFlush(List.of(b1, b2, b3, b4, b5, b6));
  }


  @Test
  public void testFindAll_Paging_Sorting() {

      // page 1, size 4, sort by title, desc
      Sort sort = Sort.by(Sort.Direction.DESC, "title");
      Pageable pageable = PageRequest.of(0, 4, sort);

      Page<Book> result = bookRepository.findAll(pageable);

      List<Book> books = result.getContent();

      assertEquals(4, books.size());

      assertThat(result).extracting(Book::getTitle)
              .containsExactlyInAnyOrder(
                      "Book C", "Book D", "Book E", "Book F");

      // page 2, size 4, sort by title, desc
      Pageable pageable2 = PageRequest.of(1, 4, sort);

      Page<Book> result2 = bookRepository.findAll(pageable2);

      List<Book> books2 = result2.getContent();

      assertEquals(2, books2.size());

      assertThat(result2).extracting(Book::getTitle)
              .containsExactlyInAnyOrder(
                      "Book A", "Book B");
  }

}

6.3 Testing the Controller with Mocking (Mockito)

BookControllerTest.java


package com.mkyong;

import com.mkyong.book.Book;
import com.mkyong.book.BookController;
import com.mkyong.book.BookRepository;
import com.mkyong.book.BookService;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.WebMvcTest;
import org.springframework.boot.test.mock.mockito.MockBean;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.ResultActions;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

import static org.hamcrest.Matchers.containsInAnyOrder;
import static org.hamcrest.Matchers.hasSize;
import static org.mockito.Mockito.*;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get;
import static org.springframework.test.web.servlet.result.MockMvcResultHandlers.print;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.jsonPath;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;

// Testing BookController with mocking
@WebMvcTest(controllers = BookController.class)
public class BookControllerTest {

  @Autowired
  private MockMvc mockMvc;

  @MockBean
  BookRepository bookRepository;

  @MockBean
  BookService bookService;

  private List<Book> books;

  private Page<Book> bookPage;
  private PageRequest pageRequest;
  private PageRequest pageRequestWithSorting;

  @BeforeEach
  void setUp() {

      Book b1 = new Book(1L, "Book A",
              BigDecimal.valueOf(9.99),
              LocalDate.of(2023, 8, 31));
      Book b2 = new Book(2L, "Book B",
              BigDecimal.valueOf(19.99),
              LocalDate.of(2023, 7, 31));
      Book b3 = new Book(3L, "Book C",
              BigDecimal.valueOf(29.99),
              LocalDate.of(2023, 6, 10));
      Book b4 = new Book(4L, "Book D",
              BigDecimal.valueOf(39.99),
              LocalDate.of(2023, 5, 5));
      Book b5 = new Book(5L, "Book E",
              BigDecimal.valueOf(49.99),
              LocalDate.of(2023, 4, 1));
      Book b6 = new Book(6L, "Book F",
              BigDecimal.valueOf(59.99),
              LocalDate.of(2023, 3, 1));

      books = List.of(b1, b2, b3, b4, b5, b6);

      bookPage = new PageImpl<>(books);
      pageRequest = PageRequest.of(0, 2, Sort.by(Sort.Direction.DESC, "name"));

  }

  @Test
  void testFindAllDefault() throws Exception {

      when(bookService
              .findAll(0, 10, "id", "ASC"))
              .thenReturn(bookPage);

      ResultActions result = mockMvc.perform(get("/books"));

      result.andExpect(status().isOk()).andDo(print());

      verify(bookService, times(1)).findAll(0, 10, "id", "ASC");

  }

  @Test
  void testFindAllDefault2() throws Exception {

      when(bookService
              .findAll(0, 10, "id", "asc"))
              .thenReturn(bookPage);

      ResultActions result = mockMvc
              .perform(get("/books?pageNo=0&pageSize=10&sortBy=id&sortDirection=asc"));

      result.andExpect(status().isOk())
              .andExpect(jsonPath("$", hasSize(6)))
              .andExpect(jsonPath("$.[*].title",
                      containsInAnyOrder("Book A", "Book B", "Book C",
                              "Book D", "Book E", "Book F")))
              .andDo(print());

  }

}

7. Download Source Code

$ git clone https://github.com/mkyong/spring-boot.git
$ cd spring-data-jpa-paging-sorting
$ ./mvnw spring-boot:run
$ curl -s "http://localhost:8080/books"
$ curl -s "http://localhost:8080/books?pageNo=1&pageSize=4&sortBy=title&sortDirection=desc" | python3 -m json.tool

8. References

About Author

mkyong

Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

0 Comments

Inline Feedbacks

View all comments

	This comment is spam
	This comment is irrelevant
	This comment is abusive
	Other