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

java, spring, spring-data, spring-data-jpa, spring-mvc

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

- Use the `JpaSpecificationExecutor` and write a `Specification`

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

Problem

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 `@RequestParam`s 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: Instead of implementing `JpaSpecificationExecutor` you should implement `JpaSpecificationExecutor<Travel>` to avoid type check warnings. 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. 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 :) 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!

Original source

Related problems