Filtering Database Rows with Spring-Data-JPA and Spring-Mvc

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

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.

How to filter an entity by multiple fields using a Spring JPA Repository?

Here, since your user does not always provide all fields you need to use more Dynamic queries. I recommend Specifications as the best way to achieve it.

You can find more info in official documentation: https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/

Related Topics