Generating correct and or condition with JPA Criteria API

Question

I am trying to generate query with multiple brackets in 'or' and 'and' conditions but internal brackets are not getting generated. Brackets for outer predicates are generating properly but not for internal ones. Code:

    CriteriaBuilder criteriaBuilder = this.getEntityManager().getCriteriaBuilder();
    CriteriaQuery<ConfigurationKey> query = criteriaBuilder.createQuery(ConfigurationKey.class);
    Root<ConfigurationKey> configurationKeyRoot = query.from(ConfigurationKey.class);
    Join<ConfigurationKey, Customer> configurationKeyCustomerJoin = configurationKeyRoot.join(ConfigurationKey_.customer);

    final List<Predicate> predicates = new ArrayList<>();
    predicates.add(criteriaBuilder.equal(configurationKeyCustomerJoin.get(Customer_.externalId), customerId));
    predicates.add(criteriaBuilder.equal(configurationKeyRoot.get(ConfigurationKey_.configType), configType));

    final List<Predicate> orPredicates = new ArrayList<>();
    keys.forEach((x, y) -> {
                orPredicates.add(
                        criteriaBuilder.and(
                                criteriaBuilder.equal(configurationKeyRoot.get(ConfigurationKey_.keyType), x),
                                criteriaBuilder.equal(configurationKeyRoot.get(ConfigurationKey_.keyValue), y)
                        )
                );
            }

    );

    predicates.add(criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()])));
    query.where(criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])));
    query.select(configurationKeyRoot);
    TypedQuery<ConfigurationKey> typedQuery = this.getEntityManager().createQuery(query);

Generated Query:

select
    configurat0_.id as id1_1_,
    configurat0_.createdAt as createdA2_1_,
    configurat0_.updatedAt as updatedA3_1_,
    configurat0_.configType as configTy4_1_,
    configurat0_.customerId as customer7_1_,
    configurat0_.keyType as keyType5_1_,
    configurat0_.keyValue as keyValue6_1_ 
from
    configuration_keys configurat0_ 
inner join
    customers customer1_ 
        on configurat0_.customerId=customer1_.id 
where
    customer1_.externalId=? 
    and configurat0_.configType=? 
    and (
        configurat0_.keyType=? 
        and configurat0_.keyValue=? 
        or configurat0_.keyType=? 
        and configurat0_.keyValue=?
    )

Expected Query:

select
    configurat0_.id as id1_1_,
    configurat0_.createdAt as createdA2_1_,
    configurat0_.updatedAt as updatedA3_1_,
    configurat0_.configType as configTy4_1_,
    configurat0_.customerId as customer7_1_,
    configurat0_.keyType as keyType5_1_,
    configurat0_.keyValue as keyValue6_1_ 
from
    configuration_keys configurat0_ 
inner join
    customers customer1_ 
        on configurat0_.customerId=customer1_.id 
where
    customer1_.externalId=? 
    and configurat0_.configType=? 
    and (
        (configurat0_.keyType=? 
        and configurat0_.keyValue=?) 
        or 
        (configurat0_.keyType=? 
        and configurat0_.keyValue=?)
    )

Please point any mistake.

I bet it is because [AND has precedence over OR](http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or) so the parentheses are redundant! — Nikos Paraskevopoulos, Nov 29 '16 at 20:06
yes, got answer here http://stackoverflow.com/questions/3382678/jpa-2-0-criteria-and-grouping-of-predicates — bpsingh, Nov 29 '16 at 20:28

score 5 · Answer 1 · answered Feb 20 '19 at 10:04

Specifications<PcPlacement> specification = Specifications.where(null);
Specifications<PcPlacement> specificationInner = Specifications.where(null);

specificationInner = specificationInner.or(buildReferringEntitySpecificationWithContains(
                            criteria.getUserFullName(), PcPlacement_.pcUser, PcUser_.fullName));

specificationInner = specificationInner.or(buildReferringEntitySpecificationWithContains(
                            criteria.getUserEmailId(), PcPlacement_.pcUser, PcUser_.emailId));

specification = specification.and(specificationInner);

That's either Spring or Hibernate (or both), bot the questions focused on JPA alone... — bruno, Mar 08 '21 at 13:17

Generating correct and or condition with JPA Criteria API

1 Answers1

Linked