Spring Data JPA Tutorial: Pagination

My Spring Data JPA tutorial has taught us how we can create database queries and sort our query results with Spring Data JPA.

We have also implemented a search function that ignores case and returns todo entries whose title or description contains the given search term. This search function sorts the returned todo entries in ascending order by using the title of the returned todo entry.

However, we are not done yet. Our example application has one serious flaw:

It returns all todo entries that are found from the database, and this is a performance problem.

This blog post helps us to eliminate this flaw. Let's get started.

Paginating the Query Results of Our Database Queries

We can paginate the query results of our database queries by following these steps:

1. Obtain the Pageable object that specifies the information of the requested page.
2. Pass the Pageable object forward to the correct repository method as a method parameter.

Let's start by finding out how we can obtain the Pageable object.

Obtaining the Pageable Object

We can obtain the Pageable object by using these two methods:

• We can create it manually.
• We can use Spring Data web support.

Let's start by creating the Pageable object manually.

Creating the Pageable Object Manually

If we want create the Pageable object manually, the service class (or other component) that wants to paginate the query results, which are returned by a Spring Data JPA repository, must create the Pageable object and pass it forward to the invoked repository method.

The source code of the RepositoryTodoSearchService class, which uses this method, looks as follows:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
 
import java.util.List;
 
@Service
final class RepositoryTodoSearchService implements TodoSearchService {
 
    private final TodoRepository repository;
 
    @Autowired
    public RepositoryTodoSearchService(TodoRepository repository) {
        this.repository = repository;
    }
 
    @Transactional(readOnly = true)
    @Override
    public Page<TodoDTO> findBySearchTerm(String searchTerm) {
        Pageable pageRequest = createPageRequest()
         
        //Obtain search results by invoking the preferred repository method.
        Page<Todo> searchResultPage = ...
         
        return TodoMapper.mapEntityPageIntoDTOPage(pageRequest, searchResultPage);
    }
     
    private Pageable createPageRequest() {
        //Create a new Pageable object here.
    }
}

The following examples demonstrate how we can implement the private createPageRequest() method:

Example 1:
If we want to get the first page by using page size 10, we have to create the Pageable object by using the following code:

private Pageable createPageRequest() {
	return new PageRequest(0, 10);
}

Example 2:
We have to sort the query results in ascending order by using the values of the title and description fields. If we want to get the second page by using page size 10, we have to create the Pageable object by using the following code:

private Pageable createPageRequest() {
	return new PageRequest(1, 10, Sort.Direction.ASC, "title", "description");
}

Example 3:
We have to sort the query results in descending order by using the value of the description field and in ascending order by using the value of the title field. If we want to get the second page by using page size 10, we have to create the Pageable object by using the following code:

private Pageable createPageRequest() {
	return new PageRequest(1, 
			10, 
			new Sort(Sort.Direction.DESC, "description")
					.and(new Sort(Sort.Direction.ASC, "title"));
	);
}

Let’s find out how we can obtain Pageable objects by using Spring Data web support.

Using Spring Data Web Support

We can enable Spring Data web support by annotating our application context configuration class with the @EnableSpringDataWebSupport annotation. The relevant part of the PersistenceContext class, which configures the persistence layer of our example application, looks as follows:

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.data.web.config.EnableSpringDataWebSupport;
import org.springframework.transaction.annotation.EnableTransactionManagement;


@Configuration
@EnableJpaAuditing(dateTimeProviderRef = "dateTimeProvider")
@EnableJpaRepositories(basePackages = {
        "net.petrikainulainen.springdata.jpa.todo"
})
@EnableTransactionManagement
@EnableSpringDataWebSupport
class PersistenceContext {
}

This registers two HandlerMethodArgumentResolver objects that are described in the following:

• The SortHandlerMethodArgumentResolver can extract sorting information from the request or from the @SortDefault annotation.
• The PageableHandlerMethodArgumentResolver extracts the information of the requested page from the request.

We can now specify the information of the requested page and configure the sorting options of the invoked database query by setting the values of the following request parameters:

• The page request parameter specifies the page number of the requested page. The number of the first page is 0 and the default value of this request parameter is 0 as well.
• The size request parameter specifies the size of the requested page. The default value of this request parameter is 20.
• The sort request parameter specifies the sorting options of the invoked query. The reference documentation of Spring Data JPA describes the content of this request parameter as follows: "Properties that should be sorted by in the format property,property(,ASC|DESC). Default sort direction is ascending. Use multiple sort parameters if you want to switch directions, e.g. ?sort=firstname&sort=lastname,asc."

After we have enabled Spring Data web support, we can inject Pageable objects into controller handler methods. The source code of the TodoSearchController class, which utilizes Spring Data web support, looks as follows:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
 
import java.util.List;
 
@RestController
final class TodoSearchController {
 
    private final TodoSearchService searchService;
 
    @Autowired
    public TodoSearchController(TodoSearchService searchService) {
        this.searchService = searchService;
    }
 
    @RequestMapping(value = "/api/todo/search", method = RequestMethod.GET)
    public Page<TodoDTO> findBySearchTerm(@RequestParam("searchTerm") String searchTerm, 
                                          Pageable pageRequest) {
        return searchService.findBySearchTerm(searchTerm, pageRequest);
    }
}

The TodoSearchController gets the information of the returned todo entries from the TodoSearchService object. The RepositoryTodoSearchService class implements the TodoSearchService interface, and its findBySearchTerm() method simply passes the search term and the Pageable object forward to the invoked repository method.

The source code of the RepositoryTodoSearchService class looks as follows:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
  
import java.util.List;
  
@Service
final class RepositoryTodoSearchService implements TodoSearchService {
  
    private final TodoRepository repository;
  
    @Autowired
    public RepositoryTodoSearchService(TodoRepository repository) {
        this.repository = repository;
    }
  
    @Transactional(readOnly = true)
    @Override
    public Page<TodoDTO> findBySearchTerm(String searchTerm, Pageable pageRequest) {
        //Obtain search results by invoking the preferred repository method.
        Page<Todo> searchResultPage = ...
          
        return TodoMapper.mapEntityPageIntoDTOPage(pageRequest, searchResultPage);
    }
}

Let's move on and find out how we can paginate our query results by using Pageable objects.

Paginating Query Results With the Pageable Object

After we have created the Pageable object manually or obtained it by using Spring Data web support, we have to create the database query that paginates its query results by using the Pageable object.

Let’s start by finding out how we can paginate all entities found from the database.

Paginating All Entities

If we want to paginate all entities found from the database, we can use one of the following methods:

First, if we created our repository interface by extending the CrudRepository interface, we have to modify it to extend only the PagingAndSortingRepository interface.

The relevant part of our repository interface looks as follows:

import org.springframework.data.repository.PagingAndSortingRepository;
  
interface TodoRepository extends PagingAndSortingRepository<Todo, Long> {
  
}

The PagingAndSortingRepository interface declares one method which we can use when we want to paginate the query results of a query that fetches all entities from the database:

• The Page<T> findAll(Pageable pageRequest) method returns a page of entities that fulfill the restrictions specified by the Pageable object.

In other words, if we want to paginate the query results of a database query that fetches all entities from the database, we have to use the Page<T> findAll(Pageable pageRequest) method instead of the Iterable<T> findAll() method.

Second, if we created our repository interface by extending the Repository interface, we can declare the Page<T> findAll(Pageable pageRequest) method in our repository interface.

The relevant part of our repository interface looks as follows:

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.Repository;
 
import java.util.List;
import java.util.Optional;
 
interface TodoRepository extends Repository<Todo, Long> {
 
    void delete(Todo deleted);
 
    Page<Todo> findAll(Pageable pageRequest);
 
    Optional<Todo> findOne(Long id);
 
    void flush();
 
    Todo save(Todo persisted);
}

We can now get a specific page by invoking the Page<T> findAll(Pageable pageRequest) method and passing the Pageable object as a method parameter.

Let’s find out how we can paginate the query results of database queries that use the query generation from the method name strategy.

Paginating the Query Results of Queries That Use the Query Generation From the Method Name Strategy

If we create our database queries from the method name of our query method, we can paginate the query results by following these steps:

1. Remove the sorting logic from the method name.
2. Add a new method parameter (Pageable object) to the query method.
3. Decide the returned type. We can return List<T>, Slice<T>, or Page<T> objects.

Because the search function of our example application is case-insensitive and it returns todo entries whose title or description contains the given search term, the source code of our repository interface looks as follows:

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.repository.Repository;
 
import java.util.List;
 
interface TodoRepository extends Repository<Todo, Long> {
 
 	List<Todo> findByDescriptionContainsOrTitleContainsAllIgnoreCase(String descriptionPart,
                                                                     String titlePart,
                                                                     Pageable pageRequest);
 
    Page<Todo> findByDescriptionContainsOrTitleContainsAllIgnoreCase(String descriptionPart,
                                                                     String titlePart,
                                                                     Pageable pageReguest);
	
 	Slice<Todo> findByDescriptionContainsOrTitleContainsAllIgnoreCase(String descriptionPart,
                                                                      String titlePart,
                                                                      Pageable pageRequest);
}

Let’s move on and find out how we can paginate the query results of named queries that use JPQL.

Paginating the Query Results of Named Queries That Use JPQL

We can paginate the query results of named queries that use JPQL by following these steps:

1. Specify the sorting logic in the JPQL query.
2. Add a new method parameter (Pageable object) to the query method.
3. Decide the returned type. We can return List<T>, Slice<T>, or Page<T> objects.

If we want to paginate the query results of the named query called: Todo.findBySearchTermNamed, the source code of our repository interface looks as follows:

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.repository.Repository;
 
import java.util.List;
 
interface TodoRepository extends Repository<Todo, Long> {

	List<Todo> findBySearchTermNamed(@Param("searchTerm") String searchTerm, 
									 Pageable pageRequest);

	Page<Todo> findBySearchTermNamed(@Param("searchTerm") String searchTerm, 
									 Pageable pageRequest);
	
	Slice<Todo> findBySearchTermNamed(@Param("searchTerm") String searchTerm, 
									  Pageable pageRequest);
}

Let’s move on and find out how we can paginate the query results of JPQL queries that are created by using the @Query annotation.

Paginating the Query Results of JPQL Queries That Use the @Query Annotation

If we create our JPQL queries by using the @Query annotation, we can paginate the query results by following these steps:

1. Remove the sorting logic from the JPQL query.
2. Add a new method parameter (Pageable object) to the query method.
3. Decide the returned type. We can return List<T>, Slice<T>, or Page<T> objects.

Because the search function of our example application is case-insensitive and it returns todo entries whose title or description contains the given search term, the source code of our repository interface looks as follows:

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
 
import java.util.List;
 
interface TodoRepository extends Repository<Todo, Long> {

    @Query("SELECT t FROM Todo t WHERE " +
            "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
            "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))")
    List<Todo> findBySearchTerm(@Param("searchTerm") String searchTerm, 
								Pageable pageRequest);

    @Query("SELECT t FROM Todo t WHERE " +
            "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
            "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))")
    Page<Todo> findBySearchTerm(@Param("searchTerm") String searchTerm, 
								Pageable pageRequest);
								
    @Query("SELECT t FROM Todo t WHERE " +
            "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
            "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))")
    Slice<Todo> findBySearchTerm(@Param("searchTerm") String searchTerm, 
								 Pageable pageRequest);
}

Let's find out how we can paginate the query results of JPA criteria queries.

Paginating the Query Results of JPA Criteria Queries

If we create our database queries by using the JPA Criteria API, our repository interface must extend the JpaSpecificationExecutor<T> interface. This interface declares one method that we can use when we want to paginate the query results of JPA criteria queries:

• The Page<T> findAll(Specification<T> spec, Pageable pageRequest) method returns a page of entities that match the Specification object and fulfill the restrictions specified by the Pageable object.

In other words, we can paginate the query results of JPA criteria queries by using the Page<T> findAll(Specification<T> spec, Pageable pageRequest) method instead of the List<T> findAll(Specification<T> spec) method.

The source code of the RepositoryTodoSearchService class, which paginates our query results by using the Pageable object, looks as follows:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
  
import java.util.List;
  
import static net.petrikainulainen.springdata.jpa.todo.TodoSpecifications.titleOrDescriptionContainsIgnoreCase;
  
@Service
final class RepositoryTodoSearchService implements TodoSearchService {
  
    private final TodoRepository repository;
  
    @Autowired
    public RepositoryTodoSearchService(TodoRepository repository) {
        this.repository = repository;
    }
  
    @Transactional(readOnly = true)
    @Override
    public Page<TodoDTO> findBySearchTerm(String searchTerm, Pageable pageRequest) {
		Specification<Todo> searchSpec = titleOrDescriptionContainsIgnoreCase(searchTerm);
        Page<Todo> searchResultPage = repository.findAll(searchSpec, pageRequest);
        return TodoMapper.mapEntityPageIntoDTOPage(pageRequest, searchResultPage);
    }
}

Let’s find out how we can paginate the query results of database queries that are created by using Querydsl.

Paginating the Query Results of Querydsl Queries

If we create our database queries by using Querydsl, our repository interface must extend the QueryDslPredicateExecutor<T> interface. This interface declares one method that we can use when we want to paginate the query results of database queries that use Querydsl:

• The Page<T> findAll(Predicate predicate, Pageable pageRequest) method returns a page of entities that match the Predicate object and fulfill the restrictions specified by the Pageable object.

In other words, we can paginate the query results of Querydsl queries by using the Page<T> findAll(Predicate predicate, Pageable pageRequest) method instead of the List<T> findAll(Predicate predicate) method.

The source code of the RepositoryTodoSearchService class, which paginates our query results by using the Pageable object, looks as follows:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import static net.petrikainulainen.springdata.jpa.todo.TodoPredicates.titleOrDescriptionContainsIgnoreCase;

@Service
final class RepositoryTodoSearchService implements TodoSearchService {

    private final TodoRepository repository;

    @Autowired
    public RepositoryTodoSearchService(TodoRepository repository) {
        this.repository = repository;
    }

    @Transactional(readOnly = true)
    @Override
    public Page<TodoDTO> findBySearchTerm(String searchTerm, Pageable pageRequest) {
		Predicate searchPred = titleOrDescriptionContainsIgnoreCase(searchTerm);
        Page<Todo> searchResultPage = repository.findAll(searchPred, pageRequest);
        return TodoMapper.mapEntityPageIntoDTOPage(pageRequest, searchResultPage);
    }
}

Let's move on and summarize what we learned from this blog post.

Summary

This blog post has taught us five things:

• We can create Pageable objects manually or obtain them by using Spring Data web support.
• We can configure Spring Data web support by annotating our application context configuration class with @EnableSpringDataWebSupport annotation.
• We can paginate the query results of query methods, JPA criteria queries, and Querydsl queries by using the Pageable object.
• We cannot paginate the query results of SQL queries by using the Pageable object because there is no reliable way to manipulate existing SQL queries.
• If we want to paginate the query results of a named query that uses JPQL, we have to add the sorting logic into the JPQL query.

The next part of this tutorial describes how we can add the creation and modification time fields into our entities by using the auditing infrastructure of Spring Data JPA.

P.S. You can get the example applications of this blog post from Github: query methods, JPA Criteria API, and Querydsl.