Paging in Spring Data

Intro

Presenting a larger set of data can be challenging. Spring Data can ease some of the pains by providing various paging capabilities.

Basics

Suppose you would like to display such paging component:

First of all, you need to compute the total number of elements so that you can estimate the number of pages.

Spring Data provides a special abstraction for that purpose:

public interface Page<T> extends Slice<T> {
  // returns the number of all pages
  int getTotalPages();

  // returns the number of all elements
  long getTotalElements();

  // returns element on this page
  List<T> getContent();

  ...
}

The Page interface is most often accompanied with Pageable which is used for selecting a specific page containing up to a certain number of elements:

public interface Pageable {
  int getPageNumber();
  int getPageSize();
  ...
}

Both can be easily used within your @Repository interfaces:

@Entity
public class Employee {

  @Id
  private Long id;
  private String name;
  private BigDecimal salary;
}

@Repository
public interface EmployeeRepository
  extends CrudRepository<Employee, Long> {

  Page<Employee> findAll(Pageable pageable);
}

Then getting a certain page can be accomplished like this:

private EmployeeRepository repository;

...
int page = 1;
int pageSize = 3;
Pageable pageable = PageRequest.of(page, pageSize);
Page<Employee> secondPageWithUpTo3Elements =
  repository.findAll(pageable);

You do not need to explicitly add this method to your repository interface if it already extends JpaRepository or PagingAndSortingRepository:

public interface JpaRepository<T, ID>
  extends PagingAndSortingRepository<T, ID> { ... }

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

Dynamic queries with paging

Now imagine you need to not only split the results into pages but also add filtering based on various criteria (e.g. salary range):

Pageable stores only information about the page request so it cannot be used for passing criteria, however, it can be used together with other APIs.

Paging with Specification

You could use Specifications based on the JPA criteria API by extending the JpaSpecificationExecutor interface:

public interface JpaSpecificationExecutor<T> {
  Page<T> findAll(Specification<T> spec, Pageable pageable);
  ...
}

@Repository
public interface EmployeeRepository
  extends JpaSpecificationExecutor<Employee> { ... }

Here you can find a snippet showing how to pass a Specification:

public static Specification<Employee> hasSalaryBetween(
    BigDecimal from, BigDecimal to) {
  return (Specification<Employee>) (employee, query, builder) ->
      builder.between(employee.get("salary"), from, to);
}

...

PageRequest pageRequest = PageRequest.of(0, 5);
Specification<Employee> spec = hasSalaryBetween(
  new BigDecimal("4000"), new BigDecimal("11000"));
Page<Employee> result = repository.findAll(spec, pageRequest);

Paging with Querydsl

Alternatively, you could use Querydsl which can make your code more type-safe and it also accepts Pageable as a parameter:

public interface QuerydslPredicateExecutor<T> {
  Page<T> findAll(Predicate predicate, Pageable pageable);
  ...
}

In order to achieve the same result as before you can write code like this:

@Repository
public interface EmployeeRepository extends
  QuerydslPredicateExecutor<Employee> { ... }

...

QEmployee employee = QEmployee.employee;
Predicate predicate = employee.salary.between(
    new BigDecimal("4000"), new BigDecimal("11000"));
PageRequest pageRequest = PageRequest.of(0, 5);
Page<Employee> result = repository.findAll(predicate, pageRequest);

Avoiding paging

If your database grows large enough you could get into a situation where counting matching elements is rather expensive (especially if you apply dynamic filtering on top of basic pagination). And you should be aware that calculating the number of pages requires knowing the total number of rows. In a nutshell, creating a Page object entails executing two SQL queries.

For example, to retrieve the third page these queries will need to be run:

-- fetch the third page (pageSize=5)
SELECT *
FROM employee
WHERE ... -- some criteria
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

-- count all matching elements
-- (could become your bottleneck)
SELECT COUNT(*)
FROM employee
WHERE ...; -- the same criteria as above

First SELECT should finish rather quickly since it expects a small number of results. However, the second query, assuming you have a huge dataset, could significantly slow down the retrieval of the current page. The problem is that it needs to count all the rows that match to get a precise total value.

Your users are unlikely to appreciate the value of knowing that there are exactly 1771571534 elements to browse:

Slice as an alternative

Fortunately, there is another useful abstraction called Slice that can be used in this scenario:

public interface Slice<T> {
  int getNumberOfElements(); // on this Slice
  List<T> getContent();
  boolean hasNext();
  Pageable nextPageable(); // get next Slice
  ...
}

Actually, you may have noticed that Page derives from Slice but please note that their implementations can vary quite a lot. Most importantly, Slice does not involve the expensive COUNT operation we have just discussed.

Loading of the next Slice can be invoked after clicking a “Load more” button:

…or when user reaches the end of the page (commonly called infinite scroll):

To add a method returning a Slice you can just write:

@Repository
public interface EmployeeRepository extends ... {
  Slice<Employee> findAll(Pageable pageable);
}

In case you would like to keep the method returning Pages here is how you can workaround the method name conflict:

// you would like to preserve this
Page<Employee> findAll(Pageable pageable);

// ...so you need to give this method a different name
@Query("SELECT e FROM Employee e")
Slice<Employee> findAllSliced(Pageable pageable);

The code calling this method is almost the same as previously. The only difference is that it returns a Slice instead of a Page.

The end