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.

Additional Reading:

If you are not familiar with Spring Data JPA, you should read the following blog posts before you continue reading this blog post:

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:

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.

Additional Reading:

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);
}
We must remember two things when we are paginating the query results of named queries:

  • If we want to paginate and sort the query results of named queries that use JPQL, we must specify the sorting logic in the JPQL query.
  • We cannot paginate the query results of native named queries because there is no reliable way to manipulate SQL queries.

Additional Reading:

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);
}
We cannot paginate the query results of native queries that use the @Query annotation because there is no reliable way to manipulate SQL queries.

Additional Reading:

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.

If you want to learn how to use Spring Data JPA, you should read my Spring Data JPA tutorial.
86 comments… add one
  • Petri Apr 25, 2012 @ 18:11

    I stumbled into this information about Spring MVC controllers and the Pageable interface:

    http://static.springsource.org/spring-data/data-jpa/docs/1.0.3.RELEASE/reference/html/#web-pagination

    It explains how you can automatically resolve the Pageable argument by passing specific request parameters to the request. This seems useful, if you don't mind creating a dependency between your controllers and the Spring Data. At first I thought of updating my example application, but after giving it some thought, I decided to leave it as an exercise for the reader.

  • Rajesh Jul 16, 2012 @ 8:16

    hi petri ,
    i am running you apps in my system but i am using PostgreSQL is the db but you apps is not running .
    please tell what are changes i need to do

    • Petri Jul 16, 2012 @ 10:11

      Hi Rajesh,

      Have you removed H2 database dependency from the pom.xml and added the PostgreSQL JDBC driver dependency to it? If you have done this and are still having problems, please let me know what the exact problem is.

  • Tvan Aug 14, 2012 @ 20:54

    Hi Petri,
    I run your example with Maven. I do not see nowhere the pagination even I created manually 22 persons. Do I understand the term "pagination" ? For me it means I can see the current page the links for next page or/and previous page. If your tutorial application is coded to see 10 persons per page, I have to see a link to go to the second page because i have 22 persons.
    I do not see the configuration of number of unit per page
    Thanks for your explanantion.
    Tvan

    • Petri Aug 14, 2012 @ 21:44

      Hi Tvan,

      thanks for your comment. Your understanding of the term "pagination" is correct. However, I did cut some corners in the example application as I mentioned in beginning of my blog entry. The requirements of my example application are:

      • Only search results are paginated. The list of persons shown in the front page is not.
      • The number of persons shown on the search results page is five. This value is hard coded to the RepositoryPersonService class (Look for a constant called NUMBER_OF_PERSONS_PER_PAGE).

      Naturally this kind of limitation is out of the question in a real world application but since it is not really relevant in the context of this tutorial, I think that is acceptable.

      If you want to add the number of items selection to the search result page, you have to make following changes to the example application:

      1. Add the pageSize variable to SearchDTO class.
      2. Modify the signature of the PersonService class' search() method to take pageSize as a parameter.
      3. Change the implementation of the search() method to use the parameter instead of the NUMBER_OF_ITEMS_PER_PAGE constant.
      4. Modify the search() method of the PersonController class to pass the new parameter forward to the search() method of PersonService.
      5. Implement the selection to the search result page and modify the person.search.js file to take the selection into account when it sends Ajax requests to the backend. You might want to check out the documentation of the jQuery pagination plugin by Gabriel Birke since it is used on the search result page.

      I hope that this was helpful.

  • Tvan Aug 14, 2012 @ 22:53

    Hi Petri,
    I see it. It's useful this tutorial on pagination.
    Thank you!
    Tvan

  • Tvan Aug 14, 2012 @ 23:36

    Dear Petri,

    It seems limited or against the principle of OOP if Spring-data pagination is bound only to JPA!
    Is it possible to use spring-data pagination with Hibernate ?

    Thanks!
    Tvan

    • Petri Aug 15, 2012 @ 8:21

      Hi Tvan,

      My example application uses Hibernate so the answer is yes. However, you have to use it as a JPA provider. This means that you cannot use the Hibernate query language or its criteria implementation (Unless you create a custom repository. This is a lot of hassle, which is not probably worth it). You can use Hibernate specific annotations in your entities if you are ready tie your application with Hibernate.

      The idea behind this is that you can (in theory) switch the JPA provider without making any changes to your code. However, if you make a change like this, I would recommend testing your application because the different JPA providers will behave differently in certain situations.

  • Stephane Apr 24, 2013 @ 11:15

    Hello Petri,
    I'm now coding a Spring Data JPA2 pet project and just found out about your book from this article, and bought it right away from the Packt website. Nice to see a finnish coder ! (I'm french but live in Tallinn). Thanks for the article !
    Kind Regards,

    • Petri Apr 24, 2013 @ 13:46

      Hi Stephane,

      Thank you for your kind words. I am happy to hear that you liked this blog post.

      Also, thank you for buying my book. I hope that you enjoy reading it!

  • Vili Jun 24, 2013 @ 9:34

    I'm trying to use Spring data JPA pagination with custom JPAQL query. When I put the query in JPA entity mapping XML file (not persistence.xml file) pagination does not work (It adds rownum filter but it does not sort).

    When I transfer the same query to Repository interface with an Annotation it works perfectly (both rownum and sort criteria is added to the query).

    I tested with the latest version and with JUnit tests. Only thing I changed was the location of the query.

    • Petri Jun 24, 2013 @ 12:35

      I assume that you are talking about named queries?

      I have to confess that I have not used complicated named queries with Spring Data JPA. I did write a simple named query for this blog post and an another one for my Spring Data book.

      However, if I remember correctly, both of the named queries were added to the entity by using the @NamedQuery annotation. I was able to paginate and sort my query results with this approach but the executed query was very simple. Theoretically though, it should not make any difference how the named query is declared.

      Did you add the used sort options to the created PageRequest object or did you use the ORDER BY clause in your query?

      • Vili Jun 24, 2013 @ 13:04

        Yes, I was talking about "named queries" declared in ORM xml file (instead of annotations).

        I assume I have created PageRequest correctly, since the same instance of it works fine, when I declare the query with @Query annotation (instead of xml file).

        I have not used order by clause in the query.

        My guess is that NamedQueries defined with Annotation might work, but named queries defined in ORM xml file do not work with pagination (Sorting is not added to the query)

  • Vili Jun 24, 2013 @ 10:17

    Is pagination supposed to work with a query with "left join fetch"-statement? Automatically created and executed "count query" fails, when I'm trying to execute my pagination query.

    • Petri Jun 24, 2013 @ 12:21

      I assume that the automatically created count query fails because of the left join fetch statement. This happens quite often if the executed query is a bit more complex than a simple select.

      If the automatically created count query fails, you can specify a custom count query by using one of the following options:

      • If you want to specify your count query in the repository interface, you can use the countQuery attribute of the @Query annotation.
      • If you want to specify your count query as a named query, you can set the name of the query by using the countName attribute of the @Query annotation.

      Check the API document of the @Query annotation for more details.

      • wolandec Jul 17, 2014 @ 15:18

        What can I do with such situation, when I generate query using JPA specifications?

        
        Join<A>	a = r.join("a",JoinType.LEFT);
        r.fetch("a",JoinType.LEFT);
        PageRequest page = new PageRequest(0, 20);
        repository.findAll(spec, page);
        
        
        • Petri Jul 18, 2014 @ 9:54

          Cannot you just create the join in your Specification? After this is done, you can call the findAll(Specification spec, Pageable page) repository method and provide the required method parameters.

          • wolandec Jul 21, 2014 @ 16:27

            In that case I just get ability to search by joined data, but do not fetch it.

            Thus, when I run by collections In view(jsp for example), I have a lot of unwanted queries to my db.

          • Petri Jul 22, 2014 @ 13:12
          • wolandec Jul 23, 2014 @ 12:05

            When I fetch I get an exception:

            Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list ...

          • Petri Jul 23, 2014 @ 12:46

            It seems that are you trying to fetch an association without fetching the parent of that association. For example, if the path of the fetched association (from the root is) is a.b, you have to select the root, fetch a, and fetch b.

            If this answer doesn't help you to solve your problem, let me know. I can give you a better answer if I can see the code of the specification builder class and the created SQL query. Could you add them here?

          • wolandec Jul 24, 2014 @ 11:31

            Have I use static metamodel in that case?

          • Petri Jul 24, 2014 @ 13:46

            The static meta model is just one way to describe your domain model. You can either use it or reference the associations by using their property names (I recommend that you use it though), but this decision will not help you to solve this problem. The only way to solve it is to fetch the parents of the fetched associations.

        • wolandec Jul 24, 2014 @ 15:20

          This is an ANV entity:

          
          @Entity
          public class Anv {
          
          	@Id
          	@Column(name = "id")
          	@GeneratedValue(strategy = GenerationType.IDENTITY)
          	private Integer id;
          
          	@Column(name = "id_place")
          	private Integer idPlace;
          
          	@Column(name = "id_place_version")
          	private Integer idPlaceVersion;
          
          	@OneToOne(fetch = FetchType.LAZY)
          	@JoinColumns({
          			@JoinColumn(name = "id_place", 
          						referencedColumnName = "id", 
          						insertable = false, 
          						updatable = false),
          			@JoinColumn(name = "id_place_version", 
          						referencedColumnName = "id_version", 
          						insertable = false, 
          						updatable = false) })
          	private Place place;
          
          }
          
          

          This is a part of specification definition.

          
          Specification spec = new Specification() {
          	public Predicate toPredicate(Root r, 
          							CriteriaQuery q, 
          							CriteriaBuilder cb) {
          		r.fetch("place",JoinType.LEFT); // This string is a problem
          }
          
          
          • Petri Jul 24, 2014 @ 17:36

            I just realized that the problem is that Spring Data repository invokes two queries (data query and count query) when you ask it to return a Page object. The problem is that count query doesn't select the parent of the place association (it selects count(*)).

            Unfortunately at the moment it is not possible use pagination in this situation (see this Jira issue).

            You can solve this problem by removing the FetchType.LAZY from the @OneToOne annotation.

            If you cannot do this, you have to implement this query by using the @Query annotation. Remember to specify the count query by setting the value of its countQuery attribute.

          • wolandec Jul 25, 2014 @ 9:07

            Oh I'm sorry, i didn't write a letter about pagination :). I see now, what can to do. But @Query annotation is not applicable for me, because the query is dynamically changed while user select one or another fields to search by.

          • Petri Jul 25, 2014 @ 17:25

            No worries. It took me some time to realize this too since you didn't mention anything about it, and I didn't notice that you added this comment to a blog post which talks about pagination.

            Anyway, like you mentioned, removing the FetchType.LAZY is the only way to do this in your case. I suggest that you do some performance testing and ensure that this doesn't cause any negative surprises when this application is deployed to the production environment.

          • wolandec Jul 25, 2014 @ 22:51

            Thanks a lot! I'll think about it.

  • Stephane Aug 13, 2013 @ 13:29

    Hi Petri,

    I wonder if it's possible to bake pagination into a manually built query. I'm trying with a TypedQuery built on a complex statement string.

    I can create the query:
    TypedQuery query = elearningSubscriptionRepository.getEntityManager().createQuery(sqlStatement, ElearningSubscription.class);
    query.setParameter("searchTerm", searchTerm);

    But it returns a list and not a page:
    Page elearningSubscriptions = query.setFirstResult(page.getPageNumber()).setMaxResults(page.getPageSize()).getResultList();

    I'm missing something :-)

  • Stephane Aug 20, 2013 @ 19:50

    Hi Petri,

    Thanks for the pointer. I had a look at the code base, and it led me to think I was going a wrong way. Having to rewrite so many private methods doesn't sound like a good idea. I now wonder if there is another way to go, so as to have a page returned from a custom query. Hopefully, some others would have come up with a solution to that requirement. Thanks again !

    Kind Regards,

  • Stephane Aug 20, 2013 @ 20:08
    • Petri Aug 20, 2013 @ 20:47

      Hi Stephane,

      Because your query is not very complex (and it is "dynamic"), I think that it is better to implement that query by using either the JPA Criteria API or Querydsl. Also, if you do this, it is quite easy to paginate the results of your query.

      Do you have some special reason for using JPQL?

      Also, it is not a good idea to concatenate the search terms to the created query. You should use either named parameters or ordinal parameters.

  • Sanjeev Nov 5, 2013 @ 3:17

    Hi Petri,
    Thanks for sharing these great samples. I have 1 question:

    Is there a way to do "stateful pagination" with spring-data API ? The way your sample application is writen, 2 subsequent requests for search results will use 2 different DB cursors on the underlying database. Since there is no "cursor/conversation id" coming back to the browser.

    Thanks in advance for a response.

    • Petri Nov 5, 2013 @ 21:20

      Hi Sanjeev,

      I have to confess that I have no idea if this is possible. I tried to search answers from Google but I didn't find anything. If you happen find some information about this, I would appreciate if you would leave a comment to this blog post.

  • Sushma Apr 17, 2014 @ 3:56

    Hi Petri,

    Its really a nice article, learned lot from this.
    I am struggling to set up spring context in xml and also I dont want to use hibernate. Please tell me how the spring and dao configuration looks like.
    Thanks in advance
    Sushma

    • Petri Apr 17, 2014 @ 14:38

      You cannot use Spring Data JPA without a JPA provider. Do you plan to use another JPA provider? If so, what JPA provider are you going to use (they have different configurations)? Or do you just want to use JDBC?

      • Sushma Apr 22, 2014 @ 3:56

        I am currently using JDBC Template. Is it possible to use that with Spring Data JPA ? If yes, how should it look like and how to write queries in dao layer. If not, what are other JPA providers which support Spring data JPA. Please don't mind, I am new to ORM concepts, can you pls suggest which is the easiest way.

        Thanks,
        SUshma

        • Petri Apr 22, 2014 @ 22:02

          Check out this Stack Overflow answer by Oliver Gierke. It provides a detailed explanation about the Spring Data project.

          He also explains what Spring Data JPA is:

          Spring Data JPA is actually a thin layer implementing the repository abstraction plus a few other bells and whistles. So we're not replacing persistence providers but actually leverage them through the API, even mitigating some of the quirks and differences between individual JPA providers.

          In other words, if you want to use Spring Data JPA, you need to use a JPA provider. I use Hibernate but there are other JPA providers as well. Here are links to the websites of the most commonly used JPA providers:

          You can of course add custom query methods to your Spring Data JPA repository by using the JdbcTemplate class but you still need to use a JPA provider.

  • shyam Apr 27, 2015 @ 12:09

    Pagination with specification is not working.it is giving count instead of list of objects.

    • Petri Apr 27, 2015 @ 23:34

      Unfortunately it is virtually impossible to figure out what is wrong without seeing the source code of your Specification and the code that invokes your repository method. If you share this information with me, I might be able to figure out what is wrong.

  • serdar May 26, 2015 @ 17:10

    Hi Petri, I have a question about the totalPage count, I have an ackward situation here:

    I have 10 records in the db, when I set the pageSize to 1 or 2 or 5 I get the totalPages as 10,5,2 correspondingly. But when I set the pageSize to 3 or 4, I always get totalPages = 3, it does not return the last page (I expect it to be 4) . Is this the implementation or am I missing something here?

    • Petri May 26, 2015 @ 18:11

      Hi Serdar,

      If the getTotalElements() method of the Page<T> interface returns 10, and your page size is 3, the getTotalPages() method of the Page<T> interface should return 4.

      The best way to solve this problem is to debug your application and put a breakpoint to the getTotalPages() method found from the PageImpl class. This should help you to figure out the root cause (either a bug in your code or in Spring Data).

      Unfortunately I have never faced this problem myself, and that is why I cannot provide you a definitive answer. Anyway, I hope that this answer helps you to find the solution to your problem.

      • Serdar May 27, 2015 @ 11:04

        found the problem :)

        we are using pagedResources$PagedMetadata from spring hateoas, constructor with 3 parameters was calculating the totalPages internally by :

        public PageMetadata(long size, long number, long totalElements) {
        this(size, number, totalElements, size == 0 ? 0 : totalElements / size);
        }

        this was the root cause, I switched to constructor with 4 parameters giving the totalPages with my own.

        Thank you Peter.

  • Anitha Jul 23, 2015 @ 11:33

    Hi Petri,

    First of all thanks a lot for all your articles. I have a problem, hope you can help me out. In my Application, i have onetomany unidrirectional parent child. I am fetching the child list using parent.How do i apply pagination on the list of child records?
    For Ex: Parent P has Child C1,C2,C3..C20
    i do findByParentCode(Code) returns the Parent object with Child list in it. But i need to get the paginated list of the child. Please Help

    • Petri Jul 23, 2015 @ 11:49

      Hi Anitha,

      AFAIK the only way to paginate the list of child records with Spring Data JPA is to fetch the child records instead of the parent record. If you need to get the parent record and its child records, you have to invoke two database queries.

      • Anonymous Jul 23, 2015 @ 12:14
        
        @Query("Select a.childList from Parent a where a.parentcode=?1")
        public Page findByCode(String parentCode, Pageable pageSpecification);
        
        

        will somethink like this work. Thanks for your reply.. i

      • Anitha Jul 23, 2015 @ 12:26

        HI Petri,

        I just want the paginated child list by the parent code. Can u pls explain a bit further as in my Child entity there is no property called parent code (this is one to many in Parent entity). Then how do i get the child list alone.
        Anitha

        • Petri Jul 23, 2015 @ 12:58

          Does your child entity has a reference to its parent entity?

          If a child knows its parent, you have to add this query method to the repository that is used to manage child entities:

          
          @Query("SELECT c FROM Child c WHERE c.parent.id=:parentId")
          public Page<Child> findByParentId(@Param("parentId") Long parentId, Pageable page);
          
          

          If the child entity doesn't know its parent, you cannot create this query by using JPQL. You can use native queries, but you have to implement the pagination logic yourself.

  • Neha Sep 15, 2015 @ 0:21

    I am really struggling to convert List users to Page in spring data mongo? Note Page is an API from org.springframework.data.domain.Page;

    I am using Pagination of Spring Data Mongo, so I need to sent Page and not the List .Please help me.

  • Neha Oct 14, 2015 @ 0:15

    Hi, I am in a worst situation where I wanted to create Pages based on the List (here is list is dynamic, sometimes list size is 5 and sometimes 20 etc)
    and I wanted to return PageImpl(userDetails, ????, userDetails.size());

    It's constant Pageable pageable = new PageRequest(0,10);

    List ud = new ArrayList();
    ud.add(userDetails); // same 20 times
    Now how Can I create 2 pages out of it as there is PageSize=10 and listSize=20.

    Please help me out.,

    • Petri Oct 14, 2015 @ 18:57

      Do you fetch that list from a database? If so, you should paginate the query results in the database because that is the most efficient way to do this. Also, if you are using Spring Data JPA, you can do this by following the instructions given in this blog post.

      If you keep the list in the memory, you just have to select the returned items based on the Pageable object that is given as a method parameter.

      If you any additional questions, don't hesitate to ask them.

      • Rizwana Nov 23, 2017 @ 8:36

        Kindly provide an example for creating n number of pages by using pagination concept.

        • Petri Nov 23, 2017 @ 21:53

          I am sorry but I don't understand what you want to do. Can you provide more details about your use case?

  • Ignacio Larrañaga Jul 20, 2016 @ 18:05

    Hey Petri!, quick question, did you try the recent pagination of native queries ?

    I tried the exact same example that is in the spring doc and seems not to be working:
    http://docs.spring.io/spring-data/jpa/docs/1.10.2.RELEASE/reference/html/#jpa.query-methods.at-query

    Example 50:
    public interface UserRepository extends JpaRepository {

    @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
    Page findByLastname(String lastname, Pageable pageable);
    }

    It still says the usual message: "Cannot use native queries with dynamic sorting and/or pagination in method public abstract..."

    • Ignacio Larrañaga Jul 20, 2016 @ 18:09

      Sorry, missed the version I was trying:

      org.springframework.data
      spring-data-jpa
      1.10.2.RELEASE

      Actually through spring-boot (1.4.0.RC1)

      • Petri Jul 20, 2016 @ 18:20

        Hi,

        I haven't tried the latest Spring Data JPA version (yet) so I cannot verify whether this works or not. Have you tried to register a result set mapping for your count query? The reason why I ask this is that I noticed this text right below the example:

        Be aware that you probably must register a result set mapping for your count query, though.

  • Karuna Apr 11, 2017 @ 9:13

    Hey when I am using Page object, the response doesn't have the reference object's data.
    do we need to add anything to get reference object's data along with the entity when we use page object ?

    • Petri Apr 12, 2017 @ 21:33

      Hi,

      Do you mean that the returned Page object doesn't contain any metadata or that it doesn't contain the entities that should be found from the requested page?

  • test Sep 8, 2017 @ 9:00

    Hi Petri,

    we have to have common component for sorting and pagination which can be used across and should handle any of the queries. Could you please help?

    • Petri Sep 10, 2017 @ 9:49

      Which data access access library / framework are you using?

  • Ivan Nov 5, 2017 @ 8:42

    What is "TodoMapper"? Where can I find its implementation example?

    • Petri Nov 5, 2017 @ 10:35

      Hi,

      It's a component that transforms Page<Todo> objects into Page<TodoDTO> objects. You can get it from Github.

  • Durga Jul 25, 2018 @ 8:21

    Hi Petri,

    Any sample application available for getting data form front-end to controller class.

    • Petri Jul 26, 2018 @ 9:36

      Hi,

      Well, the example applications of this blog post have a simple JS web application that is written with AngularJS 1.2. If I would write this tutorial now, I wouldn't add frontend code to my examples because it's kind of pointless. The problem is that different frontend technologies require different techniques and quite often the example is useless if you aren't using the same technologies.

      In other words, I don't have an example that demonstrates how you can pass data from the frontend to the backend by using modern web technologies.

  • Prasad Jul 25, 2018 @ 10:18

    Hi Petri,

    How can i send request from front-end application to control class.

    • Petri Jul 26, 2018 @ 9:41

      Hi,

      This depends from your programming language and the framework which you are using. So, if you still need my help, let me know which technologies you are using, and I will see what I can do.

  • sai Aug 8, 2018 @ 16:10

    recently upgraded spring boot version from 1.5 to 2.0.3. Pageable object is not working as expected. when page size=10 it is working fine, if i give size =100 or more than 10 then it is failing with the below error. using Postgres as DB.
    org.apache.catalina.connector.ClientAbortException: java.io.IOException: Broken pipe

    • Petri Aug 9, 2018 @ 20:05

      Well, typically when I have got the 'broken page' error message, it means that the server closed the socket before the HTTP response was returned back to the client. In other words, it seems that it takes too long to get 100 items from the database => you have to optimize your database query and/or entity mappings.

  • Rahul Dec 11, 2018 @ 23:01

    Hi Petri,

    I am new to Spring Boot + Data and wondering if you can advice me in right direction.

    I have requirement where we want a query based repository paginated (we maintain the criteria for paging cached per user in memory). When there is any data changes to the data underlying the view by the criteria we wanted to propagate those events to the client via. Websocket so the client have consistent view of the filtering + search criteria + paging view.

    I understand this may not be readily supported by Spring but its an generic requirement for most of data grids. Wondering if you can point me in a direction.

    I tried caching the page once i query the repository, but there is no other way to validate the object against the page to see if that have to propagated.

  • Michael Mar 28, 2019 @ 16:14

    Just wanted to say you're a LEGEND.

    • Petri Mar 30, 2019 @ 16:05

      Thank you for your kind words. I really appreciate them.

  • Govi Jun 20, 2020 @ 3:15

    Nice tutorial. I tried pagination and specification using findAll in jpaSpecificationExecutor interface.
    repo.findAll(spec, pageable). But in my Specification class toPredicate method calling twice by jpa. Is it expected behavior.

    • Petri Jun 26, 2020 @ 10:19

      The toPredicate() method is invoked when the WHERE clause is constructured. Thus, the toPredicate() method should be invoked only once per constructed query. Of course, if your code performs multiple queries, that method is invoked multiple times.

      To summarize, it's hard to give a definite answer without seeing the source code. Can you share your code with me?

  • Alferd Oct 23, 2020 @ 3:05

    Could you please throw some light on how to handle this scenario from this post ?

    https://stackoverflow.com/questions/64491315/spring-jpa-pagination-on-sub-entities-using-dynamic-projection

    • Petri Oct 26, 2020 @ 19:11

      Hi,

      I took a look at your problem and unfortunately I have never encountered the issue described on the StackOverflow question. However, I noticed two possible problems:

      • You pass a Set called dumyIds as a method parameter but the this parameter isn't present in the annotated query. That's why an exception is thrown.
      • You try to pass the type of the parent entity as a method parameter, but based on this SO answer, you should pass the actual parent entity object to your query method.

      So, I would try to remove the dumyIds parameter from the query method and pass the parent entity object as a method parameter.

  • VB Jan 25, 2023 @ 9:12

    Hi Petri,

    Thanks for such a nice blog.
    I have question regarding Mapping. My SQL query is returning the Page of String. I am trying to map the string to DTO, but it is throwing classCastException. Any suggestions on how to handle it?

    • Petri Jan 25, 2023 @ 18:32

      Hi,

      Can you reply to my comment and add the source code of your DTO class, an example string and the mapping code to your comment?

Leave a Reply