CODEWITHSUNDEEP
javaoracle-databasespring-data-jpacriteriapredicate
Arun Sudhakaran
Arun Sudhakaran

Reputation: 2405

Combination of And with Or using CriteriaBuilder and Predicate

We have an existing predicate to fetch data from a table, we need to add this condition as well to that query

AND (SERVICE_ID IN (1,2,34,5) or (SERVICE_ID IN (83,24) and DEST_WALLET_TYPE=4 and DEST_ENTITY_TYPE = 6))

We tried different ways but all we can achieve is this

and (SERVICE_ID in (1,2,34,5) 
or DEST_WALLET_TYPE_ID=4 and DEST_ENTITY_TYPE=6 and (SERVICE_ID in (24 , 83)))

A simple bracket should start after the or and should end after the final condition.

We tried these

Step-1

Predicate p1 = builder.and(root.get("serviceId").in(params.getFinancialServiceList()));
                
Predicate p22 = builder.equal(root.get("destWalletType"), (params.getMainWallet()));
Predicate p23 = builder.equal(root.get("destEntity"), params.getBillerEntity());
Predicate p2 = builder.and(p22, p23, builder.and(root.get("serviceId").in(Services.TOPUP, Services.DATA_RECHARGE)));
                
builder.or(p1, p2);

Step-2

Predicate p1 = builder.and(root.get("serviceId").in(params.getFinancialServiceList()));
                
builder.or(p1, 
            builder.and(builder.equal(root.get("destWalletType"), (params.getMainWallet()))),
            builder.and(builder.equal(root.get("destEntity"), params.getBillerEntity())),
            builder.and(builder.and(root.get("serviceId").in(Services.TOPUP, Services.DATA_RECHARGE))));

Can we get some help?

Upvotes: 0

Views: 1078

Answers (1)

ankit
ankit

Reputation: 94

Use this:

Predicate p1 = root.get("serviceId").in(params.getFinancialServiceList());
Predicate p21 = root.get("serviceId").in(Services.TOPUP, Services.DATA_RECHARGE);
Predicate p22 = builder.equal(root.get("destWalletType"), (params.getMainWallet()));
Predicate p23 = builder.equal(root.get("destEntity"), params.getBillerEntity());

Predicate p2 = builder.and(p21, p22, p23);
builder.or(p1, p2);

I tried a similar query in my project.

final Predicate p1 = root.get("id").in(1, 2, 3);
final Predicate p21 = root.get("regulatorId").in(1, 2);
final Predicate p22 = criteriaBuilder.equal(root.get("name"), ("app"));
final Predicate p23 = criteriaBuilder.equal(root.get("type"), AppType.DEVELOPER);

final Predicate p2 = criteriaBuilder.and(p21, p22, p23);
criteriaBuilder.or(p1, p2);

Generated query:

select generatedAlias0 from App as generatedAlias0 where 
( generatedAlias0.id in (1, 2, 3) ) or 
( ( generatedAlias0.regulatorId in (1, 2) ) and ( generatedAlias0.name=:param0 ) and ( generatedAlias0.type=:param1 ) )

Upvotes: 1

Related Questions