Using dynamic query for spring mvc pagination

Question

I am trying to implement pagination in spring mvc.

In my case, I have a Review entity which consists of reviewStatus and the date it was submitted on.

I am trying to use JpaRepository, but I am not sure how to support the following search query using it.

SELECT * from review WHERE review.reviewStatus = 2 AND 
  ( review.submittedOn BETWEEN '2015-09-08' AND '2015-09-09' )

I can see(refer http://docs.spring.io/spring-data/jpa/docs/1.4.3.RELEASE/reference/html/jpa.repositories.html) that we can define methods for particular queries like findbyname.

However, in my case, if the user doesn't provide status then the above query simply becomes:

SELECT * from review WHERE ( review.submittedOn BETWEEN 
  '2015-09-08' AND '2015-09-09' )

and if there is no submitted date provided, it becomes

SELECT * from review WHERE review.reviewStatus = 2

In short, the query depends upon the values entered by the end-user and I wish to use the same parameters to implement pagination.

Please guide and/or share links to implement same.

Thanks in advance.

do you mean a DYNAMIC query based on what the user passed? Is the problem about paging or is it about building your query? the title of your question seems a bit confusing — jmcg, Sep 08 '15 at 02:53
@jmcg Actually it is a bit of both. I am trying out the dynamic query with pagination for first time. I have used a simple "Select All" query without any issues. But not sure how to execute a dynamic query and get results which can be showed in proper pagination — Balwinder Singh, Sep 08 '15 at 02:55

Scary Wombat · Answer 1 · 2015-09-08T02:59:38.787

Have a look at http://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/repository/PagingAndSortingRepository.html

You can pass a Pageable object into the findAll method

http://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Pageable.html

As per http://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-part-seven-pagination/

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;
}

I tried looking into it. But I am afraid I am not able to get the required info. The pageable object can be used to set the order but I am not sure if I can use it for the query itself. It will be great if you could provide come example so that I can look into what I am currently failing to understand. — Balwinder Singh, Sep 08 '15 at 02:48
How can I use the query which I mentioned in my post using this example? I had already gone through this tutorial as it comes up in the first page of google search results — Balwinder Singh, Sep 08 '15 at 04:34

score 0 · Answer 2 · edited May 23 '17 at 12:22

have a look this for spring mvc paging: How to implement pagination in Spring MVC 3

As for you dynamic query, you may use a Stringbuilder to build your query based on user input. Somewhere along the lines of:

    pulic YourDomainObject searchDynamically(String reviewStatus, String date){
      StringBuilder sql = new StringBuilder();
      sql.append("SELECT * FROM review WHERE ");

      if(null != reviewStatus && null != date){
         sql.append(" reviewStatus = ? and date = ? ");

      }elseif(null != reviewStatus){
         sql.append(" reviewStatus = ? ");

      }elseif(null != date){
         sql.append(" date = ? ");
      }
    }

Using dynamic query for spring mvc pagination

2 Answers2