Do want to get a better understanding of Spring web application architecture? If so, get started right now!

Spring Data JPA Tutorial Part Seven: Pagination

Yellow Pages

The previous part of my Spring Data JPA tutorial described how you can sort query results with Spring Data JPA. This blog entry will describe how you can paginate the query results by using Spring Data JPA. In order to demonstrate the pagination support of Spring Data JPA, I will add two new requirements for my example application:

  • The person search results should be paginated.
  • The maximum number of persons shown on a single search result page is five.

I will explain the pagination support of Spring Data JPA and my example implementation in following Sections.

Pagination with Spring Data JPA

The key of component of the Spring Data JPA pagination support is the Pageable interface which is implemented by PageRequest class. You can get a specific page of your query results by following these steps:

  • Create an instance of the PageRequest class.
  • Pass the created object as a parameter to the correct repository method.

The available repository methods are described in following:

After you have obtained the requested page, you can get a list of entities by calling the getContent() method of the Page<T> interface.

Enough with the theory, lets take a look how the given requirements can be implemented with JPA criteria API.

Adding Pagination to Person Search Results

The given requirements can be implemented with JPA criteria API by following these steps:

  • Obtain the wanted page number.
  • Create the needed Specification instance.
  • Create the instance of a PageRequest class.
  • Pass the created Specification and PageRequest objects to the person repository.

First, I modified the search() method of the PersonService interface. In order to obtain the wanted page from the database, the repository needs to know what page it should be looking for. Thus, I had to add a new parameter to the search() method. The name of this parameter is pageIndex and it specifies the index of the wanted page. The declaration of the new search() methods is given in following:

public interface PersonService {

    /**
     * Searches persons for a given page by using the given search term.
     * @param searchTerm
     * @param pageIndex
     * @return  A list of persons whose last name begins with the given search term and who are belonging to the given page.
     *          If no persons is found, this method returns an empty list. This search is case insensitive.
     */
    public List<Person> search(String searchTerm, int pageIndex);
}

Second, since I am using the JPA criteria API for building the actual query, the RepositoryPersonService will obtain the needed specification by calling the static lastNameIsLike() method of PersonSpecifications class. The source code of the PersonSpecifications class is given in following:

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

public class PersonSpecifications {

    public static Specification<Person> lastNameIsLike(final String searchTerm) {
        
        return new Specification<Person>() {
            @Override
            public Predicate toPredicate(Root<Person> personRoot, CriteriaQuery<?> query, CriteriaBuilder cb) {
                String likePattern = getLikePattern(searchTerm);
                return cb.like(cb.lower(personRoot.<String>get(Person_.lastName)), likePattern);
            }
            
            private String getLikePattern(final String searchTerm) {
                StringBuilder pattern = new StringBuilder();
                pattern.append(searchTerm.toLowerCase());
                pattern.append("%");
                return pattern.toString();
            }
        };
    }
}

Third, I need to create an instance of a PageRequest class and pass this instance to the PersonRepository. I created a private method called constructPageSpecification() to the RepositoryPersonService. This method simply creates a new instance of the PageRequest object and returns the created instance. The search method obtains the PageRequest instance by calling the constructPageSpecification() method.

The last step is to pass the created objects forward to the PersonRepository. The source code of the relevant parts of the RepositoryPersonService is given in following:

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

@Service
public class RepositoryPersonService implements PersonService {
    
    private static final Logger LOGGER = LoggerFactory.getLogger(RepositoryPersonService.class);

    protected static final int NUMBER_OF_PERSONS_PER_PAGE = 5;

    @Resource
    private PersonRepository personRepository;

    @Transactional(readOnly = true)
    @Override
    public List<Person> search(String searchTerm, int pageIndex) {
        LOGGER.debug("Searching persons with search term: " + searchTerm);

        //Passes the specification created by PersonSpecifications class and the page specification to the repository.
        Page requestedPage = personRepository.findAll(lastNameIsLike(searchTerm), constructPageSpecification(pageIndex));

        return requestedPage.getContent();
    }

    /**
     * Returns a new object which specifies the the wanted result page.
     * @param pageIndex The index of the wanted result page
     * @return
     */
    private Pageable constructPageSpecification(int pageIndex) {
        Pageable pageSpecification = new PageRequest(pageIndex, NUMBER_OF_PERSONS_PER_PAGE, sortByLastNameAsc());
        return pageSpecification;
    }

    /**
     * Returns a Sort object which sorts persons in ascending order by using the last name.
     * @return
     */
    private Sort sortByLastNameAsc() {
        return new Sort(Sort.Direction.ASC, "lastName");
    }
}

I also had to modify the unit tests of the RepositoryPersonService class. The source code of the modified unit test is given in following:

import org.junit.Before;
import org.junit.Test;
import org.mockito.ArgumentCaptor;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;

import static junit.framework.Assert.assertEquals;
import static org.mockito.Mockito.*;

public class RepositoryPersonServiceTest {

    private static final long PERSON_COUNT = 4;
    private static final int PAGE_INDEX = 1;
    private static final Long PERSON_ID = Long.valueOf(5);
    private static final String FIRST_NAME = "Foo";
    private static final String FIRST_NAME_UPDATED = "FooUpdated";
    private static final String LAST_NAME = "Bar";
    private static final String LAST_NAME_UPDATED = "BarUpdated";
    private static final String SEARCH_TERM = "foo";
    
    private RepositoryPersonService personService;

    private PersonRepository personRepositoryMock;

    @Before
    public void setUp() {
        personService = new RepositoryPersonService();

        personRepositoryMock = mock(PersonRepository.class);
        personService.setPersonRepository(personRepositoryMock);
    }
    
    @Test
    public void search() {
        List<Person> expected = new ArrayList<Person>();
        Page expectedPage = new PageImpl(expected);
        when(personRepositoryMock.findAll(any(Specification.class), any(Pageable.class))).thenReturn(expectedPage);
        
        List<Person> actual = personService.search(SEARCH_TERM, PAGE_INDEX);

        ArgumentCaptor<Pageable> pageArgument = ArgumentCaptor.forClass(Pageable.class);
        verify(personRepositoryMock, times(1)).findAll(any(Specification.class), pageArgument.capture());
        verifyNoMoreInteractions(personRepositoryMock);

        Pageable pageSpecification = pageArgument.getValue();

        assertEquals(PAGE_INDEX, pageSpecification.getPageNumber());
        assertEquals(RepositoryPersonService.NUMBER_OF_PERSONS_PER_PAGE, pageSpecification.getPageSize());
        assertEquals(Sort.Direction.ASC, pageSpecification.getSort().getOrderFor("lastName").getDirection());
        
        assertEquals(expected, actual);
    }
}

I have now described the parts of the source code which are using Spring Data JPA for implementing the new requirements. However, my example application has a lot of web application specific “plumbing” code in it. I recommend that you take a look of the source code because it can help you to get a better view of the big picture.

What is Next?

I have now demonstrated to you how you can paginate your query results with Spring Data JPA. If you are interested of seeing my example application in action, you can get it from Github. The next part of my Spring Data JPA tutorial will describe how you can add custom functionality to your repository.

If you want to learn more about Spring Data JPA, you should read all parts of 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 →

40 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

Leave a Comment