Lesson 4: Pagination

1. Goals

In this lesson, we'll explore different ways that we can paginate query results with Spring Data JPA.

We'll demonstrate this for derived methods, as well as custom methods. Finally, we'll discuss what factors we should consider when returning results from paginated methods.

2. Lesson Notes

The relevant module you need to import when you're starting with this lesson is: pagination-lesson-start

If you want to have a look at the fully implemented lesson, as a reference, feel free to import: pagination-lesson-end

2.1. Introduction

Spring Data JPA allows us to implement pagination using various techniques.

In this lesson, we'll also inspect the SQL queries generated using these techniques. So before we start, let’s enable our application to log these queries by setting the corresponding parameter in our application.properties file:

Now let’s take a look at how we can manage the results of paginated methods.

2.2. The Page Interface

In previous lessons, we learned how to use the PagingAndSortingRepository. Let’s revisit one of the default methods it provides and its return type:

Page is the most common representation of paginated entities. It represents a chunk of data or a sublist of a list of data.

The Page interface defines the contract that its implementations should follow:

As we can see, the Page interface extends the Slice interface, which we’ll analyze shortly.

Additionally, it contains the total number of elements in the list and the total number of pages. However, this information comes at a cost, as an extra count query is generated to retrieve the total number of elements.

Paginating With Derived Methods Using Page

Now let's demonstrate how we can use the Page interface with derived methods and focus on the extra information it provides. Remember that derived methods use keywords in the method signature to indicate which query Spring should generate.

Let's add a method to find tasks by the status that also has a Pageable parameter to support pagination:

In the main class, we're going to make use of this method by creating a criterion that allows us to retrieve only two entities, and then passing it to the findByStatus() method:

We added additional log information in order to explicitly see the total number of elements and pages. As a reminder, our database gets initialized upon application startup, and contains four Task entities.

Let's start the application, and have a look at the output:

As expected, the query has returned the first two tasks. It has also returned additional information about the total number of tasks and the total number of pages.

Now let's look at the generated queries:

We can see that in addition to the standard SELECT queries, Spring Data generates a count query so that the Page objects have the total number of elements.

2.3. The Slice Interface

Apart from using the Page interface, Spring JPA provides us with the ability to return a Slice from any method that we use to paginate entities.

Similar to Page, a slice represents a chunk of data, or more formally, a sublist of a list of data. Let’s have a look at the Slice interface:

This interface defines a few important methods:

  • hasNext() - it indicates whether there is another slice after the current one
  • hasPrevious() - it indicates whether there is a slice before the current one
  • getPageable() - it returns a Pageable object that defines the current entities in the slice
  • nextPageable() and previousPageable() - they return Pageable objects defining the next and previous entities respectively

We can use the Pageable objects returned by the above methods to retrieve pages containing respective entities.

Paginating With Derived Methods Using Slice

Now let's see how we can use the Slice interface with a custom query method.

To this end, let's create a method in the TaskRepository that filters tasks by name and also has a Pageable parameter to support pagination:

As before, let’s call it from our main class by adding the following code to the run() method:

We reused the same pagination criteria we created earlier, and passed it to the method we just defined.

We also added a few more logs that make use of the other Slice methods. The aim is to check whether the current slice has a previous one or next one.

When we run the application, we'll see the following output:

In this output, we see two tasks. Since we're retrieving the first page of tasks, there can be no slices prior, as clearly demonstrated in the output message. Then the log message indicates that there are slices that follow the current slice, which is consistent with our initial dataset.

Spring JPA uses the combination of the offset and fetch first ? rows only keywords to retrieve the paginated tasks. To check this in the console output, we can find the query generated by Spring JPA (remember that we configured our application to show the SQL queries):

2.4. Paginating With Custom Queries

To demonstrate how to paginate entities with custom queries, let’s recreate the derived method we looked at earlier (retrieving all tasks matching a name) using a custom query.

To this end, in the TaskRepository, we add the following code:

Notice that the query contains no pagination criteria at all. Since we're using JPQL, Spring Data can modify and add the relevant pagination clause to the actual SQL query generated based on the Pageable parameter we’re passing in.

Now let's call this method from our main class. This time, let’s request page two of Tasks, then pass the Pageable object to our repository method:

All we do here is pass in the task name we want to filter by, along with our pagination information. When we run the application, we'll notice the following output in the console log:

As expected, we see the last two tasks.

Now let's check how Spring Data has translated our Pageable object into pagination clauses in the generated SQL:

Note that Spring Data has cleverly added the offset and fetch first ? rows only keywords to the first query that hits the tasks table. 

Paginating With Native Queries

We can paginate entities with native queries as well, but Spring won’t add any pagination-related clauses because there’s no reliable way to manipulate native SQL queries.

Therefore, we have to explicitly pass in the SQL offset and fetch first ? rows only values as parameters in our query methods. We've explored native queries and parameters in other lessons, so we won’t be covering it again here.

2.5. When To Use Page And Slice

We've learned how to use Slice and Page, but let's consider what factors we have to keep in mind when using them.

We know now that using the Page interface generates an additional count query to find out the total number of elements with every call to a paginated method.

Although this is an extra call to the database, it comes in handy when we design user interfaces, as we could indicate the total number of results in the very first query itself. Subsequently, since we know the total number of pages, we can construct page numbers accordingly and allow a user to jump to a particular page.

In contrast, we can use the Slice interface when we don't need that extra information. This way we can avoid any extra calls to the database, which might be costly depending on the database used. Since the slices have information about the existence of the next ones and previous ones, we could use this approach to deal with large datasets where we might not require information about the total number of results.

For example, we could use slices in order to represent news feeds or social media feeds where we don’t necessarily require a total item count or even a page count.

One caveat to remember here is that the default findAll(..) method we inherit from the PagingAndSortingRepository can return a Slice instead of a Page. Returning a Slice in this particular instance won’t avoid the extra query that a Page would otherwise generate. It has nothing to do with Spring Data JPA, it’s simply a type conversion performed by Java. Essentially, Spring Data will return a Page, and Java will just convert it to a Slice.

3. Resources

- Pagination and Sorting using Spring Data JPA

- Paging and Sorting

- Derived Query Methods in Spring Data JPA Repositories

- Paging, Iterating Large Results, Sorting & Limiting

- Query Creation From Method Names

Complete and Continue