Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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 ??

like image 533
tortoise Avatar asked Jun 19 '14 20:06

tortoise


People also ask

Which method can be used for ordering the results using criteria queries?

The CriteriaQuery interface defines the orderBy method to order query results according to attributes of an entity.

How do you use JPA criteria?

Create an instance of Session from the SessionFactory object. Create an instance of CriteriaBuilder by calling the getCriteriaBuilder() method. Create an instance of CriteriaQuery by calling the CriteriaBuilder createQuery() method. Create an instance of Query by calling the Session createQuery() method.

What is predicate in Criteria API?

javax.persistence.criteria The type of a simple or compound predicate: a conjunction or disjunction of restrictions. A simple predicate is considered to be a conjunction with a single conjunct. Return the top-level conjuncts or disjuncts of the predicate.

What is predicate in Spring data JPA?

It defines a specification as a predicate over an entity. Spring has a wrapper around the JPA criteria API (that uses predicates) and is called the specification API. Spring Data JPA repository abstraction allows executing predicates via JPA Criteria API predicates wrapped into a Specification object.


1 Answers

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));
like image 65
Robby Cornelissen Avatar answered Sep 18 '22 00:09

Robby Cornelissen