Pagination in Spring Boot

Let’s explore how to send data from the database in portions—known as pages—in Spring Boot. This approach is called page pagination.

The purpose of pagination is to avoid fetching large amounts of data at once since user interfaces have limited space to display data. There’s no point in returning 100 items if the user only views 10. The front end needs meta information about pages, such as the current page number, total number of pages, total number of elements, and more. This information helps build the page navigation element.

For example, we’ll use a demo project that contains one controller and one JPA repository. We’ll use H2 as an in-memory database, which Hibernate will recreate every time the application runs. The GeneratorPost class is responsible for populating the database with test data when the Spring context is initialized.

In Spring, pagination can be easily implemented using the Page<T> class, which is returned by the findAll(Pageable) method of the PagingAndSortingRepository interface. This interface extends JpaRepository. The Page<T> class already contains all the necessary meta information.

@RestController
@RequestMapping("api/post")
@RequiredArgsConstructor
public class PostController {

    private final PostJpaRepository repository;

    @GetMapping
    public Page<Post> getAll(
        @RequestParam("offset") Integer offset,
        @RequestParam("limit") Integer limit
    ) {
        return repository.findAll(PageRequest.of(offset, limit));
    }
}        

Using @RequestParam, we accept the page number (offset) and the number of elements on the page (limit). We pass this data to the static method PageRequest.of(offset, limit), which creates a Pageable pagination object, then passed to the findAll(Pageable) method. Note that PostJpaRepository extends JpaRepository.

In the @RequestParam annotation, you can use the defaultValue field to set default pagination values.

@RequestParam(value = "offset", defaultValue = "0") Integer offset,
@RequestParam(value = "limit", defaultValue = "20") Integer limit        

It also makes sense to limit the pagination parameters.

@RequestParam(value = "offset", defaultValue = "0") @Min(0) Integer offset,
@RequestParam(value = "limit", defaultValue = "20") @Min(1) @Max(100) Integer limit        

The minimum value for offset is 0; the user cannot request page -1. For the number of elements on a page, the minimum value is 1—there’s no point in requesting 0 elements. Additionally, you don’t want to serve too many elements on one page; otherwise, pagination becomes pointless if the user can request one page with 1,000,000 elements.

Example: Retrieving the First Page with 3 Elements

https://localhost:8080/api/post?offset=0&limit=3

HTTP/1.1 200 
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sun, 21 Aug 2024 09:45:08 GMT
Keep-Alive: timeout=60
Connection: keep-alive

{
  "content": [ 
    {
      "id": "15b48d7d-4bf2-4730-a196-cb927d5b6153",
      "title": "Post 0",
      "createOn": "2024-08-21T09:42:29.662314"
    },
    {
      "id": "7d94c880-8da3-4c2a-9a88-c5ddce64b6df",
      "title": "Post 1",
      "createOn": "2024-08-21T09:42:29.662358"
    },
    {
      "id": "05b56e0e-c223-481c-88ca-bc38901ab543",
      "title": "Post 2",
      "createOn": "2024-08-21T09:42:29.662376"
    }
  ],
  "pageable": { 
    "sort": { 
      "empty": true,
      "unsorted": true,
      "sorted": false
    },
    "offset": 0, 
    "pageNumber": 0, 
    "pageSize": 3, 
    "paged": true,
    "unpaged": false
  },
  "last": false, 
  "totalPages": 3334, 
  "totalElements": 10000, 
  "first": true, 
  "size": 3, 
  "number": 0, 
  "sort": { 
    "empty": true,
    "unsorted": true,
    "sorted": false
  },
  "numberOfElements": 3,
  "empty": false 
}        

This data provides more than enough information for the front end, but what does it cost us? Let’s enable SQL query logging to see what query Hibernate generated:

Hibernate: 
    select
        post0_.id as id1_0_,
        post0_.title as title2_0_ 
    from
        post post0_ limit ? offset ?
        
Hibernate: 
    select
        count(post0_.id) as col_0_0_ 
    from
        post post0_        

The first query uses the LIMIT and OFFSET operators to retrieve a limited set of records. The second query retrieves the total number of elements in the table, enough information to calculate all other meta-information.

Passing a Page and Pageable object through all layers of an application can be a questionable design decision. It might be better to create custom Page and Pageable analogs and transfer data to these objects at the repository level. Though uncommon in practice, it’s worth considering.

Slice Instead of Page

In Spring Data JPA, Slice is a return type used in a repository to fetch data page-by-page. Unlike Page, Slice only loads the data for the requested page without additional database load to count the total number of results, making it more efficient for large datasets. However, Slice still provides information about the existence of next/previous pages, so pagination remains simple and is easier to implement than Keyset pagination.

Page Sorting

Users often want to sort search results. The PageRequest.of() method has an overload that allows you to pass sorting data as well:

@GetMapping("exampleSort")
public Page<Post> getAllAndSort(
        @RequestParam("offset") Integer offset,
        @RequestParam("limit") Integer limit,
        @RequestParam("sort") String sortField
) {
    return repository.findAll(
            PageRequest.of(offset, limit, Sort.by(Sort.Direction.ASC, sortField))
    );
}        

In addition to offset and limit, we pass the sortField parameter, which is the name of the field in the Entity, not the column name by which we will sort. You can also specify the sorting direction.

Example: Retrieving the Second Page with 3 Elements and Sorting

GET https://localhost:8080/api/post/exampleSort?offset=1&limit=3&sort=createOn
Content-Type: application/json        

Now, the ORDER BY directive has been added to the SQL query:

Hibernate: 
    select
        post0_.id as id1_0_,
        post0_.create_on as create_o2_0_,
        post0_.title as title3_0_ 
    from
        post post0_ 
    order by
        post0_.create_on asc limit ? offset ?        

Passing the Sort Parameter

I prefer to create an enum that lists available sorting methods. This limits the API’s capabilities to valid options.

@Getter
@RequiredArgsConstructor
public enum PostSort {

    ID_ASC(Sort.by(Sort.Direction.ASC, "id")),
    ID_DESC(Sort.by(Sort.Direction.DESC, "id")),
    DATE_ASC(Sort.by(Sort.Direction.ASC, "createOn"));

    private final Sort sortValue;

}        

@GetMapping("exampleEnumSort")
public Page<Post> getAllAndEnumSort(
        @RequestParam("offset") Integer offset,
        @RequestParam("limit") Integer limit,
        @RequestParam("sort") PostSort sort
) {
    return repository.findAll(
            PageRequest.of(offset, limit, sort.getSortValue())
    );
}        

Additionally, you can sort by multiple fields simultaneously using the Sort.by() method:

Sort.by(
        Sort.Order.asc("title"), 
        Sort.Order.desc("createOn")
)        

Custom JPA Methods

The JpaRepository interface includes several methods that return a Page, but what about custom methods? It’s also possible to use pagination for them.

Generated Methods

Spring Data JPA allows you to define a method signature in an interface that extends JpaRepository, and Spring will automatically create the implementation. This method can also use pagination. Simply declare the return type as Page and add Pageable to the parameters:

public interface PostRepository extends JpaRepository<Post, UUID> {

    Page<Post> findAllByTitleLikeIgnoreCase(String titleLike, Pageable pageable);

}         

Spring will handle the rest.

JPQL

JPQL is a Hibernate abstraction over SQL, where queries are described using your own objects instead of tables and fields. Pagination can also be added to JPQL queries:

public interface PostRepository extends JpaRepository<Post, UUID> {

    @Query("SELECT p FROM Post p WHERE p.title like %:title%")
    Page<Post> findAllByTitleJpql(@Param("title") String title, Pageable pageable);

}        

Native Queries

Even for native SQL queries, you can add pagination. Additionally, you can specify the countQuery parameter in the @Query annotation. This is used to specify the count query, which is used to find the total number of elements on a page. If this parameter is not specified, then the count() query will be executed on the original query, or the countProjection() query, if there is one.

@Query(
        nativeQuery = true,
        value = "SELECT * FROM POST WHERE TITLE LIKE %?1%",
        countQuery = "SELECT count(*) FROM POST WHERE TITLE LIKE %?1%"
)
Page<Post> findAllByTitleNative(String title, Pageable pageable);        

Transforming an Object

The Page object contains Entity objects, but as a matter of good practice, you should not expose the Entity directly from the controller. Instead, you should transfer the data to a DTO class and return that. This can be done using the Page.map(Function) method. This method is similar to the Stream.map(Function) method, where you pass an implementation of the Function interface, describing how to transform data from one object to another.

Let Me Summarize

Now you know how to implement pagination in Spring Boot. However, remember that OFFSET pagination has its drawbacks. You can avoid these issues by using KeySet Pagination.