Let’s learn how to use Process a large number of records effectively using Pagination and Sorting in Spring Data JPA.
What is Pagination and Sorting?
Most often, the result set from a database can be overwhelming. It can be overwhelming to a point where systems crash because they can’t handle that much data on a single pass. So it is always a good idea to break these large records into manageable chunks. That is exactly what pagination is about.
Pagination is the process of splitting a large content into smaller numbered chunks called “pages” to make the entire data easier to process. And Sorting doesn’t require any introduction or definition. It is a means to order a set of similar items. In our case, we will stick to ordering records from a database.
You may have seen Paging and Sorting already. Take Google for example. The results for your query are in 10 links per page. And the results are sorted based on the relevance of your search query (More relevance at the top).
Why use Pagination and Sorting?
Imagine if Google didn’t use Pagination or Sorting on the results page. An average google search results in millions of HITs. So, The obvious problem would be that the page will take ages to load. Because we are not sorting the results, the users won’t find the relevant results. And even if they wanted to find the relevant results by scrolling through, they are going to scroll a lot because all results are in single a single page.
From what we learned, we should use the pagination and sorting techniques for the following reasons.
- Small results are easier to process by both server and the client.
- Large results would require more resources (CPU, MEMORY, NETWORK etc)
- Sorted results bring order to the system
- Sorting makes it possible to prioritize the items
Pagination and sorting in Spring Data JPA
Spring Data JPA offers pagination and Sorting through the PagingAndSortingRepository. This repository takes Pageable requests and provides a Page in a Zero-based index system. Here are some of the important classes and interfaces for your to remember before implementing pagination and sorting.
- Pageable – An interface defining a request for a specific page.
- PageRequest – An implementation of Pageable. Contains which page to request, how many records per page, sorting order etc.
- Page – A page with a chunk of results and meta info about the results like previous page, next page, size, total size etc
To write a Pageable query, you just need to add the Pageable interface as an argument to any JpaRepository method.
@Repository
public interface AccountRepository extends JpaRepository<Account, Integer> {
@Query("select a from Account a")
Page<Account> findAllAccounts(Pageable pageable);
}Code language: Java (java)To use this repository method to get results, you need to pass a Pageable object as a parameter. Here are some examples of how to do just that.
Pageable with page number and size.
Pageable pageable = PageRequest.of(2, 40);
Page<Account> thirdPage = accountRepository.findAllAccounts(pageable);
System.out.println(thirdPage.getContent());Code language: Java (java)PageRequest with sort based on JPA entity fields.
Pageable pageableWithSort = PageRequest.of(0, 15, Sort.by("balance"));
Page<Account> first15AccountsWithLowBalance = accountRepository.findAllAccounts(pageableWithSort);
System.out.println(first15AccountsWithLowBalance.getContent());Code language: Java (java)Pagination with sorting with sort direction on Spring data JPA entity fields.
Pageable pageableWithSortDirection = PageRequest.of(0, 15, Sort.by("balance").descending());
Page<Account> largest15AccountsByBalance = accountRepository.findAllAccounts(pageableWithSortDirection);
System.out.println(largest15AccountsByBalance);Code language: Java (java)Also, You can access the metadata of the current page.
System.out.println("Total results : " + thirdPage.getTotalElements());
System.out.println("Total Pages : " + thirdPage.getTotalPages());
System.out.println("Current Page number : " + thirdPage.getNumber());
System.out.println("Size per page : " + thirdPage.getSize());
System.out.println("Elements in this page : " + thirdPage.getNumberOfElements());Code language: Java (java)Looping through Pagination in Spring JPA
So far we saw, how to get a specific page from a large set of results. But if you want to loop through all of these results you need to query the repository many times. But fortunately, spring data jpa has provisions to iterate through pages.
Pageable pageRequest = PageRequest.of(0, 20);
Page<Account> accountsPage;
do {
accountsPage = accountRepository.findAllAccounts(pageRequest);
System.out.println(accountsPage.getContent());
pageRequest = pageRequest.next();
} while (!accountsPage.isLast());Code language: Java (java)The code here is self-explanatory. But if you need the details, just note the methods. These methods help progress and break the loop respectively. This way, if you had a large number of records, you can process them 20 records at a time.
Previous and Next pages
Just by adding or subtracting ‘1’ to the current page number, you can create a new pageable object. However, It is easier to navigate through previous and next pages with the help of the following methods.
Pageable previousPageable = page.previousPageable();
Pageable previousOrFirstPageable = page.previousOrFirstPageable();
Pageable nextPageable = page.nextPageable();
Pageable nextOrLastPageable = page.nextOrLastPageable();
// or
Pageable previousOrFirst = pageable.previousOrFirst();
Pageable next = pageable.next();Code language: Java (java)As you see, some methods even take care of the edge cases here.
Getting all results at once in a paged query method
If you want to get all records in a single go, you can set the page size to a bigger number. But there is no guarantee that the results are lesser than your given size. This is why Spring Data JPA provides an unpaged implementation through the “Pageable.unpaged()” method.
The method returns a Singleton object of Unpaged implementation. This class deliberately overrides the isPaged() method of Pageable to return false, completely bypassing the pagination logic.
So all you have to do is to call the repository method in the following way.
Pageable unpaged = Pageable.unpaged();
Page<Account> allAccounts = accountRepository.findAllAccounts(unpaged);
System.out.println("All Results in a Single page" + allAccounts.getContent());Code language: Java (java)Even though this defeats the whole purpose of pagination, you should know that this approach is possible.
Summary
To summarize, We learned how to use pagination and sorting properly in Spring JPA using a spring boot application as an example. You can find all the examples above in this GitHub Repository.