compound predicate in JPA Criteria Query - Use both and, or methods

Question

Requirement: There are some initial conditions.lets say P1 is the predicate. There is a date field in database table which could be null. If it is null, I can proceed with initial predicate itself. If it is not null, the value of that field has to be between specified dates.

Approach1: I am trying to use both 'and' 'or' conditions in my query. The problem is with combination of predicates.

Predicate P1 = criteriaBuilder.equal(a,b);
Predicate P2 = criteriaBuilder.isNull(myDate);
Predicate P3 = criteriaBuilder.isNotNull(myDate);
Predicate P4 = criteriaBuilder.between(currentDate,previousDate);


Predicate P34 = criteriaBuilder.and(P3,P4);
Predicate P234 = criteriaBuilder.or(P2,P34);
Predicate P1234 = criteriaBuilder.and(P1,P234);
criteriaBuilder.where(P1234);

Expected conditions in query: P1 AND (P2 OR (P3 AND P4))

Actual conditions in query: P1 AND P2 OR P3 AND P4

Approach2: Use some mathematics set theory and expand the brackets.

Predicate P12 = criteriaBuilder.and(P1,P2);
Predicate P34 = criteriaBuilder.and(P3,P4);
Predicate P134 = criteriaBuilder.and(P1,P34);
Predicate P12134 = criteriaBuilder.or(P12,P134);
criteriaBuilder.where(P12134);

Expected conditions in query: (P1 AND P2) OR (P1 AND P3 AND P4)

Actual conditions in query: P1 AND P2 OR P1 AND P3 AND P4

In both the approaches I am missing the brackets. Suggest me the approach to get these brackets. Is there a better approach to satisfy the requirement ??

Cavit

Answer 1

In both cases, the expected conditions and the actual conditions are equivalent. Expressions are evaluated from left to right, and AND takes precedence over OR.

Looking at your first query, both with and without brackets, the following combinations yield true, while all others will yield false:

SELECT true AND true OR true AND true;
SELECT true AND false OR true AND true;
SELECT true AND true OR false AND false;

SELECT true AND (true OR (true AND true));
SELECT true AND (false OR (true AND true));
SELECT true AND (true OR (false AND false));

Ballyhoo

For sure they are not the same. Check this query: SELECT '1', FALSE AND (TRUE OR (TRUE AND TRUE)) UNION ALL SELECT '2', FALSE AND TRUE OR TRUE AND TRUE – Mackmackay