Logical Operators in JPQL and Criteria API

Logical operators in JPQL and in JPA criteria queries enable composition of complex JPQL boolean expressions out of simple JPQL boolean expressions.

Logical Operators

ObjectDB supports 2 sets of logical operators, as shown in the following table:

Set 1 - JPQL / SQL	Set 2 - Java / JDO
AND	&&
OR	||
NOT	!

JPQL follows the SQL notation, while Java uses its own notation (which is also in use by JDOQL, the JDO Query Language). ObjectDB supports both forms.

Binary AND (&&) Operator

The following query retrieves countries whose population and area (both) exceed specified limits:

SELECT c FROM Country c
WHERE c.population > :population AND c.area > :area

A valid operand of an AND operator must be one of: TRUE, FALSE, and NULL.

The following table shows how the AND operator is evaluated based on its two operands:

	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

NULL represents unknown. Therefore, if one operand is NULL and the other operand is FALSE the result is FALSE, because one FALSE operand is sufficient for a FALSE result. If one operand is NULL and the other operand is either TRUE or NULL, the result is NULL (unknown).

ObjectDB supports the Java/JDO && operator as a synonym of AND as part of its JDO support.

Binary OR (||) Operator

The following query retrieves countries whose population or area exceeds a specified limit:

SELECT c FROM Country c
WHERE c.population > :population OR c.area > :area

A valid operand of an OR operator must be one of: TRUE, FALSE, and NULL.

The following table shows how the OR operator is evaluated based on its two operands:

	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

NULL represents unknown. Therefore, if one operand is NULL and the other operand is TRUE the result is TRUE, because one TRUE operand is sufficient for a TRUE result. If one operand is NULL and the other operand is either FALSE or NULL, the result is NULL (unknown).

ObjectDB supports the Java/JDO || operator as a synonym for OR as part of its JDO support.

Unary NOT (!) Operator

The following query retrieves all the countries whose population does not exceed a specified limit:

SELECT c FROM Country c
WHERE NOT (c.population > :population)

The operand of a NOT operator must be one of: TRUE, FALSE, or NULL.

The following table shows how the NOT operator is evaluated based on its operand:

TRUE	FALSE	NULL
FALSE	TRUE	NULL

If the operand is NULL, which represents unknown, the result is also NULL (unknown).

ObjectDB also supports the Java/JDO ! operator as a replacement for NOT as part of its JDO support.

Criteria Query Logical Operators

Boolean Expressions and Predicates

Boolean expressions are represented in criteria queries by Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Boolean> and descendant interfaces. For example, a boolean path (a field or a property) is represented by Pathjavax.persistence.criteria.PathJPA interfaceRepresents a simple or compound attribute path from a bound type or collection, and is a "primitive" expression.See JavaDoc Reference Page...<Boolean>:

  Pathjavax.persistence.criteria.PathJPA interfaceRepresents a simple or compound attribute path from a 
 bound type or collection, and is a "primitive" expression.See JavaDoc Reference Page...<Boolean> isInUN = country.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("isInUN");
  Pathjavax.persistence.criteria.PathJPA interfaceRepresents a simple or compound attribute path from a 
 bound type or collection, and is a "primitive" expression.See JavaDoc Reference Page...<Boolean> isInEU = country.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("isInEU");
  Pathjavax.persistence.criteria.PathJPA interfaceRepresents a simple or compound attribute path from a 
 bound type or collection, and is a "primitive" expression.See JavaDoc Reference Page...<Boolean> isInOECD = country.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("isInOECD");

Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or disjunction of restrictions.See JavaDoc Reference Page... is a special sub interface of Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Boolean> that represents many operator and function expressions whose type is boolean - such as comparison operators:

  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... isLarge = cb.gtgt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than the second.See JavaDoc Reference Page...(country.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("area"), 1000000);

AND / OR Expressions

The CriteriaBuilder interface provides factory methods that take two Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Boolean> operands (including Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or disjunction of restrictions.See JavaDoc Reference Page... instances) and return a new Predicate instance:

  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... p1 = cb.andand(x, y)CriteriaBuilder's methodCreate a conjunction of the given boolean expressions.See JavaDoc Reference Page...(isInUN, isInEU);  // Member of both UN and EU
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... p2 = cb.oror(x, y)CriteriaBuilder's methodCreate a disjunction of the given boolean expressions.See JavaDoc Reference Page...(isInOECD, isLarge); // Either OECD member or large

Additional factory methods are available for a variant number of predicates:

  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... p3 = cb.andand(restrictions)CriteriaBuilder's methodCreate a conjunction of the given restriction predicates.See JavaDoc Reference Page...(p1, isLarge, cb.isTrueisTrue(x)CriteriaBuilder's methodCreate a predicate testing for a true value.See JavaDoc Reference Page...(isInOECD));
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... p4 = cb.oror(restrictions)CriteriaBuilder's methodCreate a disjunction of the given restriction predicates.See JavaDoc Reference Page...(p2, cb.isTrueisTrue(x)CriteriaBuilder's methodCreate a predicate testing for a true value.See JavaDoc Reference Page...(isInUN), cb.isTrueisTrue(x)CriteriaBuilder's methodCreate a predicate testing for a true value.See JavaDoc Reference Page...(isInEU));

In the above code non Predicate boolean expressions are converted to Predicate instances using the isTrueisTrue(x)CriteriaBuilder's methodCreate a predicate testing for a true value.See JavaDoc Reference Page... method. This is required because in the non binary version the factory methods accept only Predicate instances as arguments.

NOT Expression

There are two ways to create a NOT operator:

  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... p5 = cb.notnot(restriction)CriteriaBuilder's methodCreate a negation of the given restriction.See JavaDoc Reference Page...(isInUN);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... p6 = isLarge.notnot()Predicate's methodCreate a negation of the predicate.See JavaDoc Reference Page...();

The CriteriaBuilder's notnot(restriction)CriteriaBuilder's methodCreate a negation of the given restriction.See JavaDoc Reference Page... method creates a Predicate by negating a specified boolean expression. Alternatively, to create a negation of a Predicate instance, the Predicate's notnot()Predicate's methodCreate a negation of the predicate.See JavaDoc Reference Page... method can be invoked.