Criteria Builder to exclude multiple conditions

Teong 0
Viewed 23

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.

jpa hibernate java
1 Answers

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)
                )
        )
    );
Nikos Paraskevopoulos0
Related