How to add multiple AND OR conditions in criteria builder through spring jpa Specification

Question

I am using toPredicate() method of org.springframework.data.jpa.domain.Specification and want to add two list of AND OR predicates but only or conditions are working.

static class RtoReportsSpecification {
    public static Specification<ReportFilter> applyFilter(ReportFilter reportFilter, String userId) {
        return new Specification<ReportFilter>() {
            private static final long serialVersionUID = 1L;

            @Override
            public Predicate toPredicate(Root<ReportFilter> root, CriteriaQuery<?> query,
                    CriteriaBuilder criteriaBuilder) {
                    
                List<Predicate> andPredicates = new ArrayList<>();
                andPredicates.add(criteriaBuilder.equal(root.get("field1"), reportFilter.getField1()));
                andPredicates.add(criteriaBuilder.equal(root.get("geography"), reportFilter.getGeography()));
                andPredicates.add(criteriaBuilder.equal(root.get("deptName"), reportFilter.getDeptName()));
                criteriaBuilder.and(andPredicates.toArray(new Predicate[andPredicates.size()]));

                List<Predicate> orPredicates = new ArrayList<>();
                orPredicates.add(criteriaBuilder.or(criteriaBuilder.equal(root.get("email1"), userId)));
                orPredicates.add(criteriaBuilder.or(criteriaBuilder.equal(root.get(email2), userId)));
                orPredicates.add(criteriaBuilder.equal(root.get("email3"), userId));
                orPredicates.add(criteriaBuilder.equal(root.get("email4"), userId));
                criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()]));
                
                query.orderBy(criteriaBuilder.asc(root.get("empName")));
                return criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()]));
            }
        };
    }
}

public List<RtoReport> findRtoReportsData(ReportFilter reportFilter, String userId) {
    return rtoReportsRepo.findAll(RtoReportsSpecification.applyFilter(reportFilter, userId));
}

But only or conditions are applied not and conditions.

Answer 1

only or conditions are applied because you do not use the predicate created by criteriaBuilder.and(andPredicates.toArray(new Predicate[andPredicates.size()])); and return just criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()]));

you have to join and predicates and with or predicates, like this

public static Specification<ReportFilter> applyFilter(ReportFilter reportFilter, String userId) {
    return (root, query, criteriaBuilder) -> {
        List<Predicate> andPredicates = new ArrayList<>();
        andPredicates.add(criteriaBuilder.equal(root.get("field1"), reportFilter.getField1()));
        andPredicates.add(criteriaBuilder.equal(root.get("geography"), reportFilter.getGeography()));
        andPredicates.add(criteriaBuilder.equal(root.get("deptName"), reportFilter.getDeptName()));
        Predicate andPredicate = criteriaBuilder.and(andPredicates.toArray(new Predicate[0]));

        List<Predicate> orPredicates = new ArrayList<>();
        orPredicates.add(criteriaBuilder.or(criteriaBuilder.equal(root.get("email1"), userId)));
        orPredicates.add(criteriaBuilder.or(criteriaBuilder.equal(root.get("email2"), userId)));
        orPredicates.add(criteriaBuilder.equal(root.get("email3"), userId));
        orPredicates.add(criteriaBuilder.equal(root.get("email4"), userId));
        Predicate orPredicate = criteriaBuilder.or(orPredicates.toArray(new Predicate[0]));

        query.orderBy(criteriaBuilder.asc(root.get("empName")));
        return criteriaBuilder.or(andPredicate, orPredicate);
    };
}

Create the list, merge it in a single predicate (and, or) and in the end, merge it ! CriteriaBuilder do not save anything internally, consider it like a stateless utility class.