One common challenge in application development is handling large datasets while maintaining responsiveness. With databases often containing vast amounts of data, it’s crucial to employ techniques that optimize query performance and enhance user experience. This is where pagination and sorting come into play. Pagination allows us to retrieve data in manageable chunks while sorting enables us to organize data based on specified criteria. In this article, we’ll explore how to implement pagination and sorting using Spring Data JPA’s PagingAndSortingRepository interface with a practical example.
1. Why use PagingAndSortingRepository?
Traditional approaches to retrieving data often involve large lists. This can cause performance issues, especially on client-side rendering. Pagination and sorting come to the rescue, bringing several benefits:
- Improved Performance: By fetching data in smaller chunks (pages), you reduce bandwidth usage and improve loading times.
- Enhanced User Experience: Users can navigate through data efficiently using previous/next buttons or page numbers.
- Flexible Sorting: Allow users to sort data based on their preferences, leading to a more personalized experience.
2. Setting Up the Project
You can use your preferred IDE or the Spring Initializr (https://start.spring.io/) to generate a new project with the following dependencies:
- Spring Web
- Spring Data JPA
- H2 Database (for simplicity)
pom.xml
<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>3.1.8</version>
<relativePath/>
</parent>
<groupId>com.jcg</groupId>
<artifactId>paginationsortingexample</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>paginationsortingexample</name>
<description>Demo project for Spring Boot Pagination and Sorting</description>
<properties>
<java.version>17</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>
</project>
Database Configuration (application.properties):
spring.datasource.url=jdbc:h2:mem:testdb spring.datasource.driverClassName=org.h2.Driver spring.jpa.hibernate.ddl-auto=update spring.datasource.username=sa spring.datasource.password=password spring.jpa.database-platform=org.hibernate.dialect.H2Dialect spring.h2.console.enabled=true
With these updates, the project is configured to use the H2 in-memory database. We can access the H2 console at http://localhost:8080/h2-console when the application is running.
3. Creating Entity and Repository
This example demonstrates pagination and sorting on a simple Book entity using Spring Data JPA and H2 database.
3.1 JPA Entity
First, let’s define an entity class representing the data we want to work with. For this example, let’s consider a simple Book entity with id, author, title and yearPublished fields.
@Entity
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String title;
private String author;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
}
3.2 Create a Repository Interface
Next, we’ll create a repository interface by extending the PagingAndSortingRepository interface provided by Spring Data JPA. The PagingAndSortingRepository interface provides two key methods:
findAll(Sort sort): Returns all entities sorted according to the givenSortobject.findAll(Pageable pageable): Returns aPageobject containing a specific page of data as defined by thePageableobject.
We can also create custom methods that take Pageable or Sort objects as arguments to suit our specific needs.
@Repository
public interface BookRepository extends PagingAndSortingRepository<Book, Long> {
List<Book> findByTitleContaining(String title, Pageable pageable);
}
In the above code listing, we have added a custom method named findByTitleContaining(String title, Pageable pageable) for finding books by title with pagination support. Pageable pageable allows for pagination configuration, such as specifying page number, size, current page, sorting, etc.
Note that starting from Spring Data 3.0, PagingAndSortingRepository no longer extends the CrudRepository, so if we want to add the CRUD capabilities to the repository class we need to make our repository bean extend the JpaRepository interface. Alternatively, we can explicitly extend from CrudRepository or ListCrudRepository interfaces, and we will be able to perform CRUD together with paging and sorting.
@Repository
public interface BookRepository extends JpaRepository<Book, Long> {
}
@Repository
public interface BookRepository<Book, Long> extends PagingAndSortingRepository<Book, Long>, ListCrudRepository<Book, Long> {
}
3.3 Service Layer
Next, let’s implement a service layer to interact with the repository and demonstrate paging and sorting. Here, we inject the BookRepository and define a method to retrieve paginated and sorted books.
@Service
public class BookService {
@Autowired
BookRepository bookRepository;
public Page<Book> getAllBooks(Pageable pageable) {
return bookRepository.findAll(pageable);
}
public List<Book> searchBooksByTitle(String title, Pageable pageable) {
return bookRepository.findByTitleContaining(title, pageable);
}
public Page<Book> findAllPaginatedAndSorted(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);
}
}
Here is a breakdown of the code listing above:
public List<Book> searchBooksByTitle(String title, Pageable pageable)method searches for books by their titles containing a specific substring, paginated according to the providedPageableconfiguration. It delegates the call to thefindByTitleContaining()method of the injectedBookRepository.public Page<Book> findAllPaginatedAndSorted(int pageNo, int pageSize, String sortBy, String sortDirection) method retrieves all books from the database, paginated, and sorted according to the provided parameters. It constructs aSortobject based on the sort direction and sort by fields, creates aPageableobject using the provided pagination parameters, and then delegates the call to thefindAll()method of the injectedBookRepository.
3.4 Using the Service in Controller
Finally, let’s create a REST controller to expose our service and test the paging and sorting functionality.
@RestController
@RequestMapping("/api/books")
public class BookController {
@Autowired
BookService bookService;
@GetMapping
public List<Book> findAllPaginatedAndSorted(
@RequestParam(defaultValue = "0") int pageNo,
@RequestParam(defaultValue = "5") int pageSize,
@RequestParam(defaultValue = "id") String sortBy,
@RequestParam(defaultValue = "DESC") String sortDirection) {
Page result = bookService.findAllPaginatedAndSorted(pageNo, pageSize, sortBy, sortDirection);
return result.toList();
}
@GetMapping("/search")
public List<Book> searchBooksByTitle(
@RequestParam String title,
@RequestParam(defaultValue = "0") int pageNo,
@RequestParam(defaultValue = "5") int pageSize,
@RequestParam(defaultValue = "title") String sortBy) {
Pageable pageable = PageRequest.of(pageNo, pageSize, Sort.by(sortBy));
return bookService.searchBooksByTitle(title, pageable);
}
}
The BookController provides two endpoints for interacting with books: one for retrieving a paginated and sorted list of all books, and another for searching books by title with pagination and optional sorting.
public List<Book> findAllPaginatedAndSorted(...)method retrieves a paginated and sorted list of books. It takes parameters for pagination (pageNo,pageSize) and sorting (sortBy,sortDirection) from the query string of the URL. It delegates the call to thefindAllPaginatedAndSortedmethod of the injectedBookService.public List<Book> searchBooksByTitle(...)method searches for books by their titles containing a specific substring. It takes parameters for the title to search for (title), pagination (pageNo,pageSize), and sorting (sortBy) from the query string of the URL. It constructs aPageableobject based on the pagination and sorting parameters, then delegates the call to thesearchBooksByTitlemethod of the injectedBookService.
3.5 Testing the Application
To create an SQL table in the H2 database and load some sample values, we can programmatically execute SQL scripts during the initialization of the application, we can utilize the schema.sql and data.sql files:
Create a schema.sql file inside src/main/resources folder. This file contains the SQL commands to create the table.
schema.sql
CREATE TABLE IF NOT EXISTS Book (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
author VARCHAR(255)
);
Create a data.sql file inside src/main/resources folder. This file contains the SQL commands to insert sample data into the table.
data.sql
INSERT INTO Book (title, author) VALUES ('Core HTML5 Canvas', 'Geary');
INSERT INTO Book (title, author) VALUES ('Java EE 6 Platform with Glassfish 3', 'Goncalves');
INSERT INTO Book (title, author) VALUES ('Core Java Fundamentals', 'Horstmann Cornell');
INSERT INTO Book (title, author) VALUES ('JavaScript and JQuery', 'McFarland');
INSERT INTO Book (title, author) VALUES ('Real World Java EE Patterns', 'A.Bien');
INSERT INTO Book (title, author) VALUES ('Age of Reason', 'T.Paine');
INSERT INTO Book (title, author) VALUES ('Smashing CSS', 'Meyer');
INSERT INTO Book (title, author) VALUES ('The Essential Blender', 'Hess');
INSERT INTO Book (title, author) VALUES ('Pro JavaScript for Web Apps', 'Freeman');
INSERT INTO Book (title, author) VALUES ('Java EE 7 Essentials', 'A.Gupta');
INSERT INTO Book (title, author) VALUES ('JavaScript Enlightenment', 'Lindley');
Update application.properties file. In our application.properties file, we need to specify the following properties to tell Spring Boot to execute these scripts during application startup:
application.properties
spring.datasource.initialization-mode=always spring.datasource.schema=classpath:schema.sql spring.datasource.data=classpath:data.sql
With these configurations, Spring Boot will automatically execute the schema.sql and data.sql scripts during the application startup, ensuring that the table is created and populated with the sample data.
The books table in the database looks like this:
We can now run the Spring Boot application and make HTTP requests to the /api/books endpoint with query parameters for paging and sorting.
For example, we can send HTTP requests with the following request parameters: PageNo = 0, PageSize = 5, SortBy = author, SortDirection = DESC from a web browser and observe the output:
http://localhost:8080/api/books?pageNo=0&pageSize=5&sortBy=author&sortDirection=desc
- Output: Sort Books by column
author, with sort directiondescending, paging index0, and page size5:
Test again on a web browser with the following request parameters: PageNo = 1, PageSize = 4, SortBy = title, SortDirection = ASC
http://localhost:8080/api/books?pageNo=1&pageSize=4&sortBy=title&sortDirection=asc
Output: Sort Books by column title, with sort direction ascending, paging index 1, and page size 4:
4. Testing the Repository with @DataJpaTest
To test the repository with @DataJpaTest, you’ll need to create a test class and annotate it with @DataJpaTest. Here’s an example:
@DataJpaTest
public class BookRepositoryTest {
@Autowired
private BookRepository bookRepository;
@Test
public void testRepository() {
// page 1 (First Page), size 5, sort by title, asc
Sort sort = Sort.by(Sort.Direction.ASC, "title");
Pageable pageable = PageRequest.of(0, 5, sort);
Page<Book> result = bookRepository.findAll(pageable);
List<Book> books = result.getContent();
assertEquals(5, books.size());
assertThat(result).extracting(Book::getTitle)
.containsExactlyInAnyOrder(
"Age of Reason", "Core HTML5 Canvas", "Core Java Fundamentals", "Java EE 6 Platform with Glassfish 3", "Java EE 7 Essentials");
// page 2 (Second Page), size 5, sort by title, asc
Pageable secondPageable = PageRequest.of(1, 5, sort);
Page<Book> result2 = bookRepository.findAll(secondPageable);
List<Book> books2 = result2.getContent();
assertEquals(5, books2.size());
assertThat(result2).extracting(Book::getTitle)
.containsExactlyInAnyOrder(
"JavaScript Enlightenment", "JavaScript and JQuery", "Pro JavaScript for Web Apps",
"Real World Java EE Patterns", "Smashing CSS");
}
}
The BookRepositoryTest test class verifies that the BookRepository can correctly retrieve books from the database with pagination and sorting applied. The testRepository() method contains the following steps:
- It sets up pagination and sorting parameters for the first page of results, sorting books by title in ascending order.
- It calls the
findAll()method of thebookRepositorywith the specified pagination and sorting parameters to retrieve the first page of books. - It asserts that the number of books returned on the first page is 5.
- It uses AssertJ’s
assertThat()method to verify that the titles of the books on the first page match the expected titles. - It repeats similar steps for the second page of results, again asserting that the number of books returned is 5 and that the titles match the expected titles.
Out of running the test:
5. Conclusion
In this article, we have explored how to implement paging and sorting in a Spring Boot application using Spring Data JPA’s PagingAndSortingRepository interface. By leveraging these features, we can efficiently manage large datasets, improve query performance, and enhance the overall user experience of our applications.
6. Download the Source Code
This was an article on Spring Data JPA pagination and sorting examples.
You can download the full source code of this example here: spring data jpa pagination and sorting example
