Query with dynamic criteria in Spring-boot extending CrudRepository

Question

I have a spring-boot application, and I would like to have a dynamic query criteria, i.e., I want to create a method that can receive the following optional parameters: Age and Name. My problem is that, these parameters are optional, that means they can be NULL sometimes, and I wanted to build my query dynamically, e.g., if the parameters are not null or empty I put them in my query as criteria, otherwise I don't.

The solution that I found for this problem was basically to create a custom implementation for my repository (UserRepositoryCustomImpl), and build the query by myself. However I was wondering if there is no "spring oriented solution", like some annotations, or something like that in which I could say, "Hey use this Map, to build this query dynamically".

Step one to these questions, read [the manual](http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#specifications). — M. Deinum, Jul 25 '16 at 12:08
either use specifications or create two methods and check at service layer. You could also use OR I guess, but not sure how it works for nulls — Andrii Plotnikov, Jul 25 '16 at 12:47
@M.Deinum Already read the manual, and did not find the solution that I needed in there. Otherwise I would not be asking questions in here, and wasting other people time. — tsukanomon, Jul 25 '16 at 16:05
@Sarief exactly my problem, I tried it with AND and OR using the "automatic query methods" that you can do when using CrudRepository, such as FindAllByAgeAndName(...), but the problem is when u pass null. Also I wanted to pass as parameter a Map so I could put any criteria that I actually wanted, and build the query dynamically. — tsukanomon, Jul 25 '16 at 16:07
@tsukanomon map would not do. You can compose criteria with AND, create criteria builder and generic interface for that form to criteria convertion. then, you write once how form transforms to criteria. — Andrii Plotnikov, Jul 25 '16 at 19:03
I linked you to the section that has the answer. Use specifications. That create a dynamic query based on the input. There is also another question (with answer) here on stack overflow answering the exact same question. — M. Deinum, Jul 26 '16 at 05:24
The question (with the answer) is here http://stackoverflow.com/questions/20280708/filtering-database-rows-with-spring-data-jpa-and-spring-mvc. — M. Deinum, Jul 26 '16 at 05:33

score 0 · Answer 1 · answered Jul 25 '16 at 16:35

I feel I've lived your same situation: my HTML form has N filters but I want they only apply to the search if they are selected or changed. Once you model your form binded with it's respective selects, inputs, etc... from the View, if any of these fields aren't selected or have the default value, just want to ignore but still using the convenience and ease of the Spring Repository (in my case JpaRespository).

With my little Spring perspective I've tried to use the Specification approach (IMO better than implement your custom Repository). It works perfectly, but don't know if it's the best way.

Here is an official Spring example https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/

And here a personal example a little simplified (and probably breaking some best coding practices, with the aim to be understandable):

MyObjectRepository.java

    public interface MyObjectRepository extends JpaRepository<MyObject, Long>, JpaSpecificationExecutor<MyObject> { }

MyObjectServiceImpl.java

@Service("MyObjectService")
public class MyObjectServiceImpl implements MyObjectService {

@Autowired
MyObjectRepository myObjectRespository;

@Override
public Page<MyObject> getAllMyObjectByFields(int pageIndex, FormMyObject formMyObject) {
    Specification<MyObject> spec = new Specification<MyObject>() {
          @Override
          public Predicate toPredicate(Root<MyObject> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
            if (formMyObject == null) {
              throw new IllegalStateException("At least one parameter should be provided to construct complex query");
            }
            List<Predicate> predicates = new ArrayList<Predicate>();
                // only if the content is present add a criteria to the search, you decide/know if will be null, or empty...
                if (formMyObject.getYourField() != null && formMyObject.getYourField().length() > 0) {
                   predicates.add(
                           builder.and(builder.equal(root.get("field"), formMyObject.getYourField())));
                }

                // add as many criteria as you want/need
                if(){
                   predicates.add( ... );
                }


                Predicate[] predicatesArray = new Predicate[predicates.size()];
            return builder.and(predicates.toArray(predicatesArray));
          }
    };

    PageRequest page = new PageRequest(pageIndex, formMyObject.getMaxResult());     
    // using the built in findAll method from Repository with dynamic custom filters 
    return myObjectRespository.findAll(spec, page);

Query with dynamic criteria in Spring-boot extending CrudRepository

1 Answers1