In the last article, I wrote about how to sort the query results in Spring Data JPA using static and dynamic sorting techniques.

In the modern web, the response time of your website is a critical factor for higher search engine ranking. The visitors expect pages to load quickly and only show the relevant information. For example, if you own an e-commerce website with tens of thousands of products, this means only displaying a small number of products at once, and not all of them.

To help you deal with such situations, Spring Data JPA provides the concepts of pagination. It makes it easy to deal with a large amount of data in the most efficient way.

In this article, you will learn how to paginate the query results in Spring Data JPA. I shall show you how pagination works with derived and custom queries.

Create an Application

Let us create a simple Spring Boot application with the Spring Data JPA and H2 in-memory database. It only contains a single entity called Person:

Person.java

package com.attacomsian.jpa.domains;

import javax.persistence.*;

@Entity
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String firstName;
    private String lastName;
    private int age;

    public Person() {
    }

    public Person(String firstName, String lastName, int age) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.age = age;
    }

    // getters and setters, equals(), toString(), ... (omitted for brevity)
}

Here is what our PersonRespository looks like:

PersonRepository.java

package com.attacomsian.jpa.repositories;

import com.attacomsian.jpa.domains.Person;
import org.springframework.data.repository.PagingAndSortingRepository;

public interface PersonRepository extends PagingAndSortingRepository<Person, Long> {

    // TODO: add paging methods
}

Spring Data's Pageable Interface

Just like the special Sort parameter, we used for the dynamic sorting, Spring Data JPA supports another special parameter called Pageable for paginating the query results. These special parameters are automatically recognized by the Spring Data infrastructure to apply pagination and sorting to database queries dynamically.

The Pageable interface contains information about the requested page such as the size and the number of the page. It provides the following methods, among others, to add paging to statically define queries:

public interface Pageable {

    // returns the current page number (zero-based)
    int getPageNumber();

     // returns the size of the page
    int getPageSize();

     // returns the sorting parameters
    Sort getSort();

   // ... other methods
}

Whenever you want to apply pagination to query results, all you need to do is just add Pageable to the query method definition as a parameter and set the return by Page<T>:

Page<Person> findByLastName(String lastName, Pageable pageable);

While calling the above method, you need to create an object of Pageable and pass it to the invoked repository method. The simplest way to create an instance of Pageable is to use the PageRequest class, which provides the implementation of Pageable methods:

Pageable pageable = PageRequest.of(0, 10);

This will create a page request for the first page (page index is zero-based) with 10 as the size of the page to be returned.

You can even apply dynamic sorting by using the Pageable instance as shown below:

// pageable instance with dynamic sorting
Pageable pageable = PageRequest.of(0, 10, Sort.by("age").descending());

// multiple sort parameters
Pageable pageable = PageRequest.of(0, 10, Sort.by("age").descending()
        .and(Sort.by("lastName").ascending()));

Spring Data's Slice and Page

The example, we have defined above, returns a Page object. A Page contains information about the total number of elements and pages available in the database. This is because the Spring Data JPA triggers a count query to calculate the number of elements.

Depending on the database you are using in your application, it might become expensive as the number of items increases. To avoid this costly count query, you should instead return a Slice. Unlike a Page, a Slice only knows about whether the next slice is available or not. This information is sufficient to walk through a large result set.

Both Slice and Page are part of Spring Data JPA, where Page is just a sub-interface of Slice with additional methods. You should use Slice if you don't need the total number of items and pages. An example of such a scenario is when you only need the Next Page and Previous Page buttons. Here is an example query that returns a Slice:

Slice<Person> findByAgeGreaterThan(int age, Pageable pageable);

Paginating Query Results with Pageable

Now we know the Pageable interface and the differences between a Page and a Slice, let us create database queries that accept a Pageable object as a parameter to paginate their results.

The PagingAndSortingRepository interface, which we are extending above, provides us the findAll(Pageable pageable) method to paginate all Person entities currently available in the database. All you need to do is just create an instance of Pageable and pass it to this method to get a Page.

However, if you want to get a Slice or a List, you need to define your own queries in the repository interface.

Derived Queries

Applying pagination to derived queries is a straightforward task. You just need to pass the Pageable interface as a parameter to any derived query and set the desired return type as shown below:

Page<Person> findByFirstName(String firstName, Pageable pageable);

Slice<Person> findByAgeBetween(int start, int end, Pageable pageable);

List<Person> findByLastNameIsNotNull(Pageable pageable);

Custom Queries with @Query Annotation

To apply pagination to JPQL queries defined using the @Query annotation, Spring Data JPA allows you to pass the Pageable interface as a parameter:

@Query("SELECT p FROM Person p WHERE p.lastName = ?1")
Page<Person> findByLastNameJPQL(String lastName, Pageable pageable);

@Query("SELECT p FROM Person p WHERE p.age < :age")
Page<Person> findByAgeLessThanJPQL(@Param("age") int page, Pageable pageable);

To use pagination with native SQL queries declared using the @Query annotation, you need to define the count query by yourself, as shown in the following example:

@Query(value = "SELECT * FROM Person p WHERE p.firstName = :firstName",
        countQuery = "SELECT count(*) Person p WHERE p.firstName = :firstName",
        nativeQuery = true)
Page<Person> findByFirstNameNativeSQL(@Param("firstName") String firstName, Pageable pageable);

Named Queries

You can also paginate the query results of named queries that use JPQL to define the query statements.

Let us first declare a JPQL named query by using the @NamedQuery annotation:

@NamedQuery(name = "Person.findByFirstNameNamed",
        query = "SELECT p FROM Person p WHERE p.firstName = ?1")
public class Person {
    // ...
}

Now you can reference the named query above in the PersonRepository interface and pass Pageable as a parameter:

Page<Person> findByFirstNameNamed(String firstName, Pageable pageable);

Similar to native SQL custom queries, native named queries also require a separate count query to use the Pageable interface for paging the query results.

Let us define a native SQL named query:

@NamedNativeQuery(name = "Person.findByLastNameNativeNamed",
        query = "SELECT * FROM Person p WHERE p.firstName = :firstName")
public class Person {
    // ...
}

To reference the above native SQL named query in your repository interface, you must add a @Query annotation on the repository method and set the nativeQuery attribute to true along with the count query as shown below:

@Query(countQuery = "SELECT count(*) Person p WHERE p.lastName = :lastName",
        nativeQuery = true)
Page<Person> findByLastNameNativeNamed(@Param("lastName") String lastName, Pageable pageable);

Note: Dynamic sorting is not supported by named queries. So while calling the above repository methods, your Pageable instance should not contain a Sort object.

Using the Pageable Interface

The following examples show how to create an instance of Pageable and then call different query methods that paginate their results:

// get all persons by the last name
Pageable pageable = PageRequest.of(0, 3);
Page<Person> personPage = personRepository.findByLastName("Doe", pageable);

// get all persons sorted by their age in the descending order
Pageable pageable2 = PageRequest.of(0, 5, Sort.by("age").descending());
Page<Person> personPage2 = personRepository.findAll(pageable2);

If you want to skip the pagination for query methods that require an instance of Pageable, just use the Pageable.unpaged() method:

Slice<Person> personSlice = personRepository.findByAgeBetween(20, 60, Pageable.unpaged());

Source Code: Download the complete source code from GitHub available under MIT license.

Conclusion

In this article, you have learned how to apply pagination to query results for different queries in Spring Data JPA.

We also discussed the Pageable interface and the Slice and Page differences in detail. Read the Sorting Query Results in Spring Data JPA if you want to learn more about the sorting functionality in Spring Data JPA.

Further Reading

To learn about Spring Data JPA, check out the following articles:

✌️ Like this article? Follow me on Twitter and LinkedIn. You can also subscribe to RSS Feed.