Filtering database rows with spring-data-jpa and spring-mvc

Question

I have a spring-mvc project that is using spring-data-jpa for data access. I have a domain object called Travel which I want to allow the end-user to apply a number of filters to it.

For that, I've implemented the following controller:

@Autowired
private TravelRepository travelRep;

@RequestMapping("/search")  
public ModelAndView search(
        @RequestParam(required= false, defaultValue="") String lastName, 
        Pageable pageable) {  
    ModelAndView mav = new ModelAndView("travels/list");  
    Page<Travel> travels  = travelRep.findByLastNameLike("%"+lastName+"%", pageable);
    PageWrapper<Travel> page = new PageWrapper<Travel>(travels, "/search");
    mav.addObject("page", page);
    mav.addObject("lastName", lastName);
    return mav;
}

This works fine: The user has a form with a lastName input box which can be used to filter the Travels.

Beyond lastName, my Travel 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 @RequestParams and add a spring-data-jpa method to query by these. For instance I'd add a method findByLastNameLikeAndFirstNameLikeAndShipNameLike.

However, I don't know how should I do it when I need to filter for foreign keys. So my Travel has a period attribute that is a foreign key to the Period domain object, which I need to have it as a dropdown for the user to select the Period.

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.

I know that this can be done if I implement two methods in my repository and use an if to my controller:

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

Is there a way to do this without using the if ? 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 :(

Update 03/12/13: 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:

1. Instead of implementing JpaSpecificationExecutor you should implement JpaSpecificationExecutor<Travel> to avoid type check warnings.

2. Please take a look at kostja's excellent answer to this question Really dynamic JPA CriteriaBuilder since you will need to implement this if you want to have correct filters.

3. The best documentation I was able to find for the Criteria API was http://www.ibm.com/developerworks/library/j-typesafejpa/. This is a rather long read but I totally recommend it - after reading it most of my questions for Root and CriteriaBuilder were answered :)

4. Reusing the Travel object was not possible because it contained various other objects (who also contained other objects) which I needed to search for using Like - instead I used a TravelSearch object that contained the fields I needed to search for.

Update 10/05/15: As per @priyank's request, here's how I implemented the TravelSearch object:

public class TravelSearch {
    private String lastName;
    private School school;
    private Period period;
    private String companyName;
    private TravelTypeEnum travelType;
    private TravelStatusEnum travelStatus;
    // Setters + Getters
}

This object was used by TravelSpecification (most of the code is domain specific but I'm leaving it there as an example):

public class TravelSpecification implements Specification<Travel> {
    private TravelSearch criteria;


    public TravelSpecification(TravelSearch ts) {
        criteria= ts;
    }

    @Override
    public Predicate toPredicate(Root<Travel> root, CriteriaQuery<?> query, 
            CriteriaBuilder cb) {
        Join<Travel, Candidacy> o = root.join(Travel_.candidacy);

        Path<Candidacy> candidacy = root.get(Travel_.candidacy);
        Path<Student> student = candidacy.get(Candidacy_.student);
        Path<String> lastName = student.get(Student_.lastName);
        Path<School> school = student.get(Student_.school);

        Path<Period> period = candidacy.get(Candidacy_.period);
        Path<TravelStatusEnum> travelStatus = root.get(Travel_.travelStatus);
        Path<TravelTypeEnum> travelType = root.get(Travel_.travelType);

        Path<Company> company = root.get(Travel_.company);
        Path<String> companyName = company.get(Company_.name);

        final List<Predicate> predicates = new ArrayList<Predicate>();
        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()]));

    }
}

Finally, here's my search method:

@RequestMapping("/search")  
public ModelAndView search(
        @ModelAttribute TravelSearch travelSearch,
        Pageable pageable) {  
    ModelAndView mav = new ModelAndView("travels/list");  

    TravelSpecification tspec = new TravelSpecification(travelSearch);

    Page<Travel> travels  = travelRep.findAll(tspec, pageable);

    PageWrapper<Travel> page = new PageWrapper<Travel>(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;
}

Hope I helped!

Solution

For starters you should stop using @RequestParam and put all your search fields in an object (maybe reuse the Travel object for that). Then you have 2 options which you could use to dynamically build a query

1. Use the JpaSpecificationExecutor and write a Specification
2. Use the QueryDslPredicateExecutor and use QueryDSL to write a predicate.

Using JpaSpecificationExecutor

First add the JpaSpecificationExecutor to your TravelRepository this will give you a findAll(Specification) method and you can remove your custom finder methods.

public interface TravelRepository extends JpaRepository<Travel, Long>, JpaSpecificationExecutor<Travel> {}

Then you can create a method in your repository which uses a Specification which basically builds the query. See the Spring Data JPA documentation for this.

The only thing you need to do is create a class which implements Specification and which builds the query based on the fields which are available. The query is build using the JPA Criteria API link.

public class TravelSpecification implements Specification<Travel> {

    private final Travel criteria;

    public TravelSpecification(Travel criteria) {
        this.criteria=criteria;
    }

    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
        // create query/predicate here.
    }
}

And finally you need to modify your controller to use the new findAll method (I took the liberty to clean it up a little).

@RequestMapping("/search")  
public String search(@ModelAttribute Travel search, Pageable pageable, Model model) {  
Specification<Travel> spec = new TravelSpecification(search);
    Page<Travel> travels  = travelRep.findAll(spec, pageable);
    model.addObject("page", new PageWrapper(travels, "/search"));
    return "travels/list";
}

Using QueryDslPredicateExecutor

First add the QueryDslPredicateExecutor to your TravelRepository this will give you a findAll(Predicate) method and you can remove your custom finder methods.

public interface TravelRepository extends JpaRepository<Travel, Long>, QueryDslPredicateExecutor<Travel> {}

Next you would implement a service method which would use the Travel object to build a predicate using QueryDSL.

@Service
@Transactional
public class TravelService {

    private final TravelRepository travels;

    public TravelService(TravelRepository travels) {
        this.travels=travels;
    }

    public Iterable<Travel> search(Travel criteria) {

        BooleanExpression predicate = QTravel.travel...
        return travels.findAll(predicate);
    }
}

See also this bog post.

Comments

[Serafeim]

Another question: Do you know where should I find some good info (or the API) on how to implement the "toPredicate" method of TravelSpecification ? Thanks !

[M. Deinum]

The JPA documentation and assuming you use hibernate check the Hibernate docs. The Criteria API is comes from JPA so there might be some tutorials around for that.

[rmalviya]

A great project such as specification-arg-resolver can be used. Read the blog post.