If you are struggling to write good automated tests, you are not alone.

Spring Data JPA Tutorial: Pagination

Yellow Pages

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.

I just announced my "Test With Spring" course:

GET THE 30% EARLY-BIRD DISCOUNT >>

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.

I just announced my "Test With Spring" course:

GET THE 30% EARLY-BIRD DISCOUNT >>

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.

About the Author

Petri Kainulainen is passionate about software development and continuous improvement. He is specialized in software development with the Spring Framework and is the author of Spring Data book.

About Petri Kainulainen →

57 comments… add one
  • 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.

    Reply
  • 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

    Reply
    • 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.

      Reply
  • 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

    Reply
    • 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.

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

    Reply
  • 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

    Reply
    • 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.

      Reply
  • 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,

    Reply
    • 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!

      Reply
  • 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.

    Reply
    • 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?

      Reply
      • 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)

        Reply
  • 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.

    Reply
    • 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.

      Reply
      • 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);
        
        
        Reply
        • 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.

          Reply
          • 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.

          • 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 …

          • 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?

          • Have I use static metamodel in that case?

          • 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.

        • 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
          }
          
          
          Reply
          • 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.

          • 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.

          • 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.

          • Thanks a lot! I’ll think about it.

  • 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 :-)

    Reply
  • 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,

    Reply
  • Reply
    • 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.

      Reply
  • 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.

    Reply
    • 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.

      Reply
  • 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

    Reply
    • 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?

      Reply
      • 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

        Reply
        • 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.

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

    Reply
    • 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.

      Reply
  • 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?

    Reply
    • 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.

      Reply
      • 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.

        Reply
  • 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

    Reply
    • 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.

      Reply
      • 
        @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

        Reply
      • 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

        Reply
        • 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.

          Reply
  • 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.

    Reply
  • 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.,

    Reply
    • 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.

      Reply

Leave a Comment