I have a sql query like below
select * from enquiry where NOT(request_type = 'SELF_SERVICE' and enquiry_status_id = 19);
My criteria query builder is like below
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Enquiry> query = cb.createQuery(Enquiry.class);
Root<Enquiry> enquiryRoot = query.from(Enquiry.class);
List<Predicate> predicates = new ArrayList<>();
List<Predicate> predicates3 = new ArrayList<>();
Predicate predicate3 = cb.equal(enquiryRoot.get("requestType"), "SELF_SERVICE");
Predicate predicate4 = cb.notEqual(enquiryRoot.get("status").get("id"), 19);
predicates3.add(cb.and(predicate3, predicate4));
predicates.add(cb.and(predicates3.toArray(new Predicate[0])));
query.where(cb.and(predicates.toArray(new Predicate[0])));
Is there any way to exclude the condition like sql query above?
Kindly advice. Thanks.
CodePudding user response:
There is CriteriaBuilder.not
. I think the following maps nicely to your SQL; note that the criteria code you have shown probably does not do what you want.
So the desired SQL:
select * from enquiry
where NOT(request_type = 'SELF_SERVICE' and enquiry_status_id = 19);
Maps to this criteria API code:
query
.select(enquiryRoot)
.where(
cb.not(
cb.and(
cb.equal(enquiryRoot.get("requestType"), "SELF_SERVICE"),
cb.equal(enquiryRoot.get("status").get("id"), 19)
)
)
);