Spring boot Is it possible to group predicates or use 'in' conditionally

Question

I'm having issues properly grouping my query using CriteriaBuilder and Predicates.

I want to create a query that can generate something like:

SELECT * from tableName where columnA = '1234' and (columnB Like '%33%' or columnB Like '%44%')

But what I'm getting (Obviously expected looking at the code) is:

SELECT * from tableName where columnA = '1234' and columnB Like '%33%' or columnB Like '%44%'

Which does not produce the same result as the first query.

Been trying to work around it, but this is my first time working with criteriaBuilder and predicates. Here's the code:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Foo> criteriaQuery = builder.createQuery(Foo.class);
Root r = criteriaQuery.from(Foo.class);
Predicate predicate = builder.conjunction();
for(Map.Entry<String, String> entry : searchParams.entrySet()){
    if((entry.getKey().equalsIgnoreCase("columnK") || entry.getKey().equalsIgnoreCase("columnY") ||
            entry.getKey().equalsIgnoreCase("columnZ") || entry.getKey().equalsIgnoreCase("columnJ")) && !Strings.isNullOrEmpty(entry.getValue())){
                        predicate = builder.and(predicate,
                    builder.like(r.get(entry.getKey()),
                            String.format("%%%s%%", entry.getValue().toString())));
    }
    else if(entry.getKey().equalsIgnoreCase("theDate") && !Strings.isNullOrEmpty(entry.getValue())){
        predicate = builder.and(predicate, builder.equal(r.get(entry.getKey()), entry.getValue()));
    }
}

//here's where the problem is ... I realize I can use IN, but I also didn't get that to work
boolean isFirstDone = false;
for(String oneId: idStringList){
    if(!isFirstDone) {
        predicate = builder.and(predicate, builder.equal(r.get("acceptorId"), oneId));
        isFirstDone = true;
    }
    else{
        predicate = builder.or(predicate, builder.equal(r.get("acceptorId"), oneId));
    }
}
criteriaQuery.where(predicate);

Thank you.

score 0 · Accepted Answer · answered Mar 07 '18 at 10:54

Okay, so I just cracked this after carefully reading this answer https://stackoverflow.com/a/9323183/5038073 The solution is not exactly what I needed but it helped... a lot

I created a path and combined it with my predicate.

Here's what changed:

This:

boolean isFirstDone = false;
for(String oneId: idStringList){
    if(!isFirstDone) {
        predicate = builder.and(predicate, builder.equal(r.get("acceptorId"), oneId));
        isFirstDone = true;
    }
    else{
        predicate = builder.or(predicate, builder.equal(r.get("acceptorId"), oneId));
    }
}

became:

Path<Object> path = r.get("acceptorId");
CriteriaBuilder.In<Object> in = builder.in(path);
for (String oneId: idStringList) {
    in.value(oneId);
}
predicate = builder.and(predicate, in);

Pretty simple for all the trouble it took me to solve.

Hope this helps someone else too!

Spring boot Is it possible to group predicates or use 'in' conditionally

1 Answers1