Case insensitive search with Predicate and Criteria API

Question

I have currently implemented a dynamic query builder that works perfectly if my query conditions are spelled correctly. Since this might not be always the case I need a solution that is flexible enough to take any variation of the condition, primarily supporting case insensitivity.

Current specification's toPredicate method override code looks like this:

final List<Predicate> predicates = new ArrayList<Predicate>();

    Path<String> username = root.get("username");
    Path<String> agentCode = root.get("agentCode");
    Path<EntityStatus> status = root.get("status");
    Path<String> firstname = root.get("firstName");
    Path<String> lastname = root.get("lastName");
    Path<String> email = root.get("eMail");


if(criteria.getUsername()!=null && !criteria.getUsername().isEmpty()) {
      predicates.add(cb.equal(username, criteria.getUsername()));
    }

    if(criteria.getAgentCode()!=null && !criteria.getAgentCode().isEmpty()) {
      predicates.add(cb.equal(agentCode, criteria.getAgentCode()));
    }

    if(criteria.getFirstName()!=null && !criteria.getFirstName().isEmpty()) {
      predicates.add(cb.like(firstname, "%"+criteria.getFirstName()+"%"));
    }

    if(criteria.getLastName()!=null && !criteria.getLastName().isEmpty()) {
      predicates.add(cb.equal(lastname, criteria.getLastName()));
    }

    if(criteria.getEMail()!=null && !criteria.getEMail().isEmpty()) {
      predicates.add(cb.equal(email, criteria.getEMail()));
    }

    if(criteria.getStatus()!=null) {
      predicates.add(cb.equal(status, criteria.getStatus()));
    }

    return cb.and(predicates.toArray(new Predicate[predicates.size()]));
  }

And my repository interface that is being called from the service layer looks like this.

public interface UserRepo extends PagingAndSortingRepository<User, Long> {
     List<User> findAll(Specification spec);
}

"_take any variation of the condition._" could you be more specific about the variation to allow ? Is it just about case sensitivity ? By the way, you should do a method that will do the checks and add if it is valid, this is really a repetitive code) — AxelH, Sep 18 '17 at 08:59
@AxelH yes, sorry for not specifying. It primarily has to support being case insensitive. — H.G., Sep 18 '17 at 09:01
Possible duplicate of [JPA2: Case-insensitive like matching anywhere](https://stackoverflow.com/questions/4580285/jpa2-case-insensitive-like-matching-anywhere) — JEY, Sep 18 '17 at 09:21
@JEY it is not a duplicate, I have reviewd that post and in it the solution is building custom query Using CriteriaQuery. I am not using that i am using only CriteriaBuilder and letting spring JPA via the interface to create and execute query. My code is more like simply setting conditions — H.G., Sep 18 '17 at 09:24

score 10 · Answer 1 · answered Sep 18 '17 at 11:15

10

As @Nikolas Charalambidis has suggested, I've used a variation of his answer that is suitable for my exact implementation. The answer required modifying only predacate.add() lines. With this answer the solution is flexible enough to support both case insensitivity as well as partial input.

predicates.add(cb.like(cb.lower(email), "%"+criteria.getEMail().toLowerCase()+"%"));

answered Sep 18 '17 at 11:15

H.G.

415
2
4
17

Accept [Nikolas C](https://stackoverflow.com/a/46275834/4391450) answer then, your update is not about the case sensitive problem but a "contains" query. Since you already had that part in the first name criteria, this was not necessary in an answer – AxelH Sep 18 '17 at 11:21
If `criteria.getEmail()` does not return a literal string it may be neccessary to use `cb.lower()` on it as well. – JimmyB Sep 18 '17 at 11:50
@JimmyB: in the OP's case, it does. So what's the point of your comment? – Nikolas Charalambidis Sep 18 '17 at 11:56
@NikolasCharalambidis Nowhere in the question do I see anything mentioned about what type `getEmail()` can return. – JimmyB Sep 18 '17 at 11:59
@JimmyB: If OP applied the `toLowerCase()` method to `getEMail()` method and confirmed it works, it obviously returns the String value. The same assumption according this logics would be that `cb` may be not `CriteriaBuilder`... Yea, how do we know? – Nikolas Charalambidis Sep 18 '17 at 12:03
This solved my problem. I have multiple methods that each generate a Predicate, which are called based on certain conditions. Having this effectively create a 'LOWER()' statement in the sql was perfect. – Stephan Feb 14 '19 at 20:53

Nikolas Charalambidis · Accepted Answer · 2017-09-18T10:26:40.733

4

Normally, you achieve the case-insensitivity with using method equalsIgnoreCase().

However in this case you just parse values to be compared without implementing the comparison itself. Thus you can parse all the values in lower-case using the method toLowerCase() forcing them to be compared case insensitively.

predicates.add(cb.equal(email.toLowerCase(), criteria.getEMail().toLowerCase()));

Edit: I have taken a look on the JavaEE documentation of CriteriaBuilder and found the method lower() which may be helpful.

edited Sep 18 '17 at 10:26

answered Sep 18 '17 at 09:24

Nikolas Charalambidis

40,893
16
117
183

1

The problem is that email.toLowerCase() is just a path set i lines above method override, and it is not possible to invoke it on the path. Path email = root.get("eMail"); – H.G. Sep 18 '17 at 09:31
Is the class `Path` under your implementation? If so, is okey for your use-case override the getters? – Nikolas Charalambidis Sep 18 '17 at 10:25
I used your idea but changed it a little to correspond my implementation. I will post it as answer – H.G. Sep 18 '17 at 11:12
Looks great, glad it helped :) – Nikolas Charalambidis Sep 18 '17 at 11:43

Case insensitive search with Predicate and Criteria API

2 Answers2