CODEWITHSUNDEEP
javajpa
user3157090
user3157090

Reputation: 537

JPA Criteria builder How to group logical AND conditions

JPA Criteria builder How to group logical operator AND

Current

select * from Offers marketingo0_ where (upper(marketingo0_.SOURCE_KEY_UID)=? OR marketingo0_.STATUS=? and marketingo0_.STATUS=? )

Expected

select * from Offers marketingo0_ where upper(marketingo0_.SOURCE_KEY_UID)=? OR (marketingo0_.STATUS=? and marketingo0_.STATUS=? ) 

        List<Predicate> innerPredicates = new ArrayList<Predicate>();
    List<Predicate> predicates = new ArrayList<Predicate>();
    List<Predicate> outerPredicates = new ArrayList<Predicate>();


    //Create all outer predicates
    createPredicates(filter.getRootBooleanClause(),builder, marketingOffer, outerPredicates);


    //Create all sub clauses predicates
    for (BooleanClause subClause : filter.getRootBooleanClause().getSubClauses()) {

        List<Predicate> groupPredicates = new ArrayList<Predicate>();

        createPredicates(subClause,builder, marketingOffer, groupPredicates);

        if(groupPredicates!=null && groupPredicates.size()>0 && filter.getOperator().equals(LogicOperator.OR)){
            innerPredicates.add(builder.and(groupPredicates.toArray(new Predicate[groupPredicates.size()])));
        }else if(groupPredicates!=null &&  groupPredicates.size()>0 && filter.getRootBooleanClause().getOperator().equals(LogicOperator.AND)){
            innerPredicates.add(builder.or(groupPredicates.toArray(new Predicate[groupPredicates.size()])));
        }
    }

    if(innerPredicates.size()>0){
        outerPredicates.addAll(innerPredicates);
    }


    if(outerPredicates.size()>0 && filter.getRootBooleanClause().getOperator().equals(LogicOperator.OR)){
        predicates.add(builder.or(outerPredicates.toArray(new Predicate[outerPredicates.size()])));
    }else if(outerPredicates.size()>0 && filter.getRootBooleanClause().getOperator().equals(LogicOperator.AND)){
        predicates.add(builder.and(outerPredicates.toArray(new Predicate[outerPredicates.size()])));
    }

Upvotes: 3

Views: 5069

Answers (2)

A_01
A_01

Reputation: 1141

To achieve 

upper(marketingo0_.SOURCE_KEY_UID)=? 
OR 
(marketingo0_.STATUS=? and marketingo0_.STATUS=? )

I think this should work

criteriaBuilder.or(
    criteriaBuilder.equal(criteriaBuilder.upper(root.<String>get("SOURCE_KEY_UID")), key),
    criteriaBuilder.and(
        criteriaBuilder.equal(root.get("STATUS"), status), 
        criteriaBuilder.equal(root.get("STATUS"), status) // This doesnot make sense but sticking to OP
    )
)

Upvotes: 1

JimmyB
JimmyB

Reputation: 12610

Try it like this:

builder.or(
    builder.and(
        <Column1 predicate>,
        <Column2 predicate>
    ),
    <ColumnC predicate>
);

The CriteriaBuilder will take care of correctly nesting the sub-expressions.

Upvotes: 3

Related Questions