title: Filtering database rows with spring-data-jpa and spring-mvc
body: <p>I have a spring-mvc project that is using spring-data-jpa for data access. I have a domain object called <code>Travel</code> which I want to allow the end-user to apply a number of filters to it.</p>
  
  <p>For that, I've implemented the following controller:</p>
  
  <pre><code>@Autowired
  private TravelRepository travelRep;
  
  @RequestMapping("/search")  
  public ModelAndView search(
          @RequestParam(required= false, defaultValue="") String lastName, 
          Pageable pageable) {  
      ModelAndView mav = new ModelAndView("travels/list");  
      Page&lt;Travel&gt; travels  = travelRep.findByLastNameLike("%"+lastName+"%", pageable);
      PageWrapper&lt;Travel&gt; page = new PageWrapper&lt;Travel&gt;(travels, "/search");
      mav.addObject("page", page);
      mav.addObject("lastName", lastName);
      return mav;
  }
  </code></pre>
  
  <p>This works fine: The user has a form with a <code>lastName</code> input box which can be used to filter the Travels. </p>
  
  <p>Beyond lastName, my <code>Travel</code> domain object has a lot more attributes by which I'd like to filter. I think that if these attributes were all strings then I could add them as <code>@RequestParam</code>s and add a spring-data-jpa method to query by these. For instance I'd add a method <code>findByLastNameLikeAndFirstNameLikeAndShipNameLike</code>.</p>
  
  <p>However, I don't know how should I do it when I need to filter for foreign keys. So my <code>Travel</code> has a <code>period</code> attribute that is a foreign key to the <code>Period</code> domain object, which I need to have it as a dropdown for the user to select the <code>Period</code>. </p>
  
  <p>What I want to do is when the period is null I want to retrieve all travels filtered by the lastName and when the period is not null I want to retrieve all travels for this period filtered by the lastName. </p>
  
  <p>I know that this can be done if I implement two methods in my repository and use an <code>if</code> to my controller:</p>
  
  <pre><code>public ModelAndView search(
         @RequestParam(required= false, defaultValue="") String lastName,
         @RequestParam(required= false, defaultValue=null) Period period, 
         Pageable pageable) {  
    ModelAndView mav = new ModelAndView("travels/list");  
    Page travels = null;
    if(period==null) {
      travels  = travelRep.findByLastNameLike("%"+lastName+"%", pageable);
    } else {
      travels  = travelRep.findByPeriodAndLastNameLike(period,"%"+lastName+"%", pageable);
    }
    mav.addObject("page", page);
    mav.addObject("period", period);
    mav.addObject("lastName", lastName);
    return mav;
  }
  </code></pre>
  
  <p>Is there a way to do this <em>without</em> using the <code>if</code> ? My Travel has not only the period but also other attributes that need to be filtered using dropdowns !! As you can understand, the complexity would be exponentially increased when I need to use more dropdowns because all the combinations'd need to be considered :(</p>
  
  <p><b>Update 03/12/13</b>: Continuing from M. Deinum's excelent answer, and after actually implementing it, I'd like to provide some comments for completeness of the question/asnwer:</p>
  
  <ol>
  <li><p>Instead of implementing <code>JpaSpecificationExecutor</code> you should implement <code>JpaSpecificationExecutor&lt;Travel&gt;</code> to avoid type check warnings.</p></li>
  <li><p>Please take a look at kostja's excellent answer to this question 
  <a href="https://stackoverflow.com/questions/11138118/really-dynamic-jpa-criteriabuilder">Really dynamic JPA CriteriaBuilder</a>
  since you <em>will</em> need to implement this if you want to have correct filters.</p></li>
  <li><p>The best documentation I was able to find for the Criteria API was <a href="http://www.ibm.com/developerworks/library/j-typesafejpa/" rel="noreferrer">http://www.ibm.com/developerworks/library/j-typesafejpa/</a>. This is a rather long read but I totally recommend it - after reading it most of my questions for Root and CriteriaBuilder were answered :)</p></li>
  <li><p>Reusing the <code>Travel</code> object was not possible because it contained various other objects (who also contained other objects) which I needed to search for using <code>Like</code> - instead I used a <code>TravelSearch</code> object that contained the fields I needed to search for.</p></li>
  </ol>
  
  <p><b>Update 10/05/15</b>: As per @priyank's request, here's how I implemented the TravelSearch object:</p>
  
  <pre><code>public class TravelSearch {
      private String lastName;
      private School school;
      private Period period;
      private String companyName;
      private TravelTypeEnum travelType;
      private TravelStatusEnum travelStatus;
      // Setters + Getters
  }
  </code></pre>
  
  <p>This object was used by TravelSpecification (most of the code is domain specific but I'm leaving it there as an example):</p>
  
  <pre><code>public class TravelSpecification implements Specification&lt;Travel&gt; {
      private TravelSearch criteria;
  
  
      public TravelSpecification(TravelSearch ts) {
          criteria= ts;
      }
  
      @Override
      public Predicate toPredicate(Root&lt;Travel&gt; root, CriteriaQuery&lt;?&gt; query, 
              CriteriaBuilder cb) {
          Join&lt;Travel, Candidacy&gt; o = root.join(Travel_.candidacy);
  
          Path&lt;Candidacy&gt; candidacy = root.get(Travel_.candidacy);
          Path&lt;Student&gt; student = candidacy.get(Candidacy_.student);
          Path&lt;String&gt; lastName = student.get(Student_.lastName);
          Path&lt;School&gt; school = student.get(Student_.school);
  
          Path&lt;Period&gt; period = candidacy.get(Candidacy_.period);
          Path&lt;TravelStatusEnum&gt; travelStatus = root.get(Travel_.travelStatus);
          Path&lt;TravelTypeEnum&gt; travelType = root.get(Travel_.travelType);
  
          Path&lt;Company&gt; company = root.get(Travel_.company);
          Path&lt;String&gt; companyName = company.get(Company_.name);
  
          final List&lt;Predicate&gt; predicates = new ArrayList&lt;Predicate&gt;();
          if(criteria.getSchool()!=null) {
              predicates.add(cb.equal(school, criteria.getSchool()));
          }
          if(criteria.getCompanyName()!=null) {
              predicates.add(cb.like(companyName, "%"+criteria.getCompanyName()+"%"));
          }
          if(criteria.getPeriod()!=null) {
              predicates.add(cb.equal(period, criteria.getPeriod()));
          }
          if(criteria.getTravelStatus()!=null) {
              predicates.add(cb.equal(travelStatus, criteria.getTravelStatus()));
          }
          if(criteria.getTravelType()!=null) {
              predicates.add(cb.equal(travelType, criteria.getTravelType()));
          }
          if(criteria.getLastName()!=null ) {
              predicates.add(cb.like(lastName, "%"+criteria.getLastName()+"%"));
          }
          return cb.and(predicates.toArray(new Predicate[predicates.size()]));
  
      }
  }
  </code></pre>
  
  <p>Finally, here's my search method:</p>
  
  <pre><code>@RequestMapping("/search")  
  public ModelAndView search(
          @ModelAttribute TravelSearch travelSearch,
          Pageable pageable) {  
      ModelAndView mav = new ModelAndView("travels/list");  
  
      TravelSpecification tspec = new TravelSpecification(travelSearch);
  
      Page&lt;Travel&gt; travels  = travelRep.findAll(tspec, pageable);
  
      PageWrapper&lt;Travel&gt; page = new PageWrapper&lt;Travel&gt;(travels, "/search");
  
      mav.addObject(travelSearch);
  
      mav.addObject("page", page);
      mav.addObject("schools", schoolRep.findAll() );
      mav.addObject("periods", periodRep.findAll() );
      mav.addObject("travelTypes", TravelTypeEnum.values());
      mav.addObject("travelStatuses", TravelStatusEnum.values());
      return mav;
  }
  </code></pre>
  
  <p>Hope I helped!</p>