Comparison in JPQL and Criteria API

Most JPQL queries use at least one comparison operator in their WHERE clause.

Comparison Operators

ObjectDB supports two sets of comparison operators, as shown in the following table:

	Set 1 - JPQL / SQL	Set 2 - Java / JDO
Less Than	<	<
Greater Than	>	>
Less Than or Equal To	<=	<=
Greater Than or Equal To	>=	>=
Equal	=	==
Not Equal	<>	!=

The two sets differ in the Equal and the Not Equal operators. JPQL follows the SQL notation, where Java uses its own notation (which is also in use by JDOQL, the JDO Query Language). ObjectDB supports both forms. Besides the different notation, there is also a difference in the way that NULL values are handled by these operators.

Comparing NULL values

The following table shows how NULL values are handled by each comparison operator. One column presents a comparison of NULL value with a non NULL value. The other column presents a comparison of two NULL values:

Operators	One NULL operand	Two NULL operands
<, <=, >, >=	NULL	NULL
=	NULL	NULL
<>	NULL	NULL
==	FALSE	TRUE
!=	TRUE	FALSE

Comparison operators are always evaluated to TRUE, FALSE or NULL.

When both operands are not NULL (not shown in the table) the operator is evaluated to either TRUE or FALSE, and in that case, == is equivalent to = and != is equivalent to <>.

When at least one of the two operands is NULL, == and != implement the ordinary Java logic, in which, for example, null == null is true. All the other operators implement the SQL logic in which NULL represents an unknown value and expressions that include an unknown value are evaluated as unknown, i.e. to NULL.

IS [NOT] NULL

To check for NULL using standard JPQL you can use the special IS NULL and IS NOT NULL operators which are provided by JPQL (and SQL):

c.president IS NULL
c.president IS NOT NULL

The expressions above are equivalent (respectively) to the following non standard JPQL (but standard Java and JDOQL) expressions:

c.president == null
c.president != null

Comparable Data Types

Comparison is supported for values of the following data types:

• Values of numeric types, including primitive types (byte, short, char, int, long, float, double), wrapper types (Byte, Short, Character, Integer, Long, Float, Double),  BigInteger and BigDecimal can be compared by using any comparison operator.

• String values can be compared by using any comparison operator.
  Equality operators (=, <>, ==, !=) on strings in queries follow the logic of Java's equals, comparing the content rather than the identity.
• Date values can be compared by using any comparison operator.
  Equality operators (=, <>, ==, !=) on date values in queries follow the logic of equals, comparing the content rather than the identity.

• Values of the boolean and Boolean types can be compared by equality operators (=, <>, ==, !=) which follow the logic of Java's equals (for Boolean instances).
• Enum values can be compared by using the equality operators (=, <>, ==, !=).

• Instances of user defined classes (entity classes and embeddable classes) can be compared by using the equality operators (=, <>, ==, !=). For entities, e1 = e2 if e1 and e2 have the same type and the same primary key value. For embeddable objects, e1 = e2 if e1 and e2 have exactly the same content.

ObjectDB supports comparison of any two values that belong to the same group as detailed above. Therefore, for example, a double value can be compared to a BigInteger instance but not to a String instance.

[NOT] BETWEEN

The BETWEEN operator is a convenient shortcut that can replace two simple comparisons.

The two following expressions are equivalent (:min and :max are query parameters):

x BETWEEN :min AND :max

x >= :min AND x <= :max

Similarly, NOT BETWEEN is also a shortcut and the following expressions are equivalent:

x NOT BETWEEN :min AND :max

x < :min OR x > :max

Criteria Query Comparison

JPQL comparison operators (which are described above) are available also as JPA criteria query expressions. The CriteriaBuilderjavax.persistence.criteria.CriteriaBuilderJPA interfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings.See JavaDoc Reference Page... interface provides factory methods for building these expressions, as shown in the following examples:

  // Create String path and parameter expressions:
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<String> name = country.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("name");
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<String> nameParam = cb.parameterparameter(paramClass)CriteriaBuilder's methodCreate a parameter expression.See JavaDoc Reference Page...(String.class);

  // Create Integer path and parameter expressions:
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Integer> area = country.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("area");
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Integer> areaParam = cb.parameterparameter(paramClass)CriteriaBuilder's methodCreate a parameter expression.See JavaDoc Reference Page...(Integer.class);

  // Equal (=)
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... eq1 = cb.equalequal(x, y)CriteriaBuilder's methodCreate a predicate for testing the arguments for equality.See JavaDoc Reference Page...(name, nameParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... eq2 = cb.equalequal(x, y)CriteriaBuilder's methodCreate a predicate for testing the arguments for equality.See JavaDoc Reference Page...(name, "India");
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... eq3 = cb.equalequal(x, y)CriteriaBuilder's methodCreate a predicate for testing the arguments for equality.See JavaDoc Reference Page...(area, areaParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... eq4 = cb.equalequal(x, y)CriteriaBuilder's methodCreate a predicate for testing the arguments for equality.See JavaDoc Reference Page...(area, 1000000);

  // Not Equal (<>)
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... ne1 = cb.notEqualnotEqual(x, y)CriteriaBuilder's methodCreate a predicate for testing the arguments for inequality.See JavaDoc Reference Page...(name, nameParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... ne2 = cb.notEqualnotEqual(x, y)CriteriaBuilder's methodCreate a predicate for testing the arguments for inequality.See JavaDoc Reference Page...(name, "India");
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... ne3 = cb.notEqualnotEqual(x, y)CriteriaBuilder's methodCreate a predicate for testing the arguments for inequality.See JavaDoc Reference Page...(area, areaParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... ne4 = cb.notEqualnotEqual(x, y)CriteriaBuilder's methodCreate a predicate for testing the arguments for inequality.See JavaDoc Reference Page...(area, 1000000);

  // Greater Than (>)
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... gt1 = cb.greaterThangreaterThan(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than the second.See JavaDoc Reference Page...(name, nameParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... gt2 = cb.greaterThangreaterThan(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than the second.See JavaDoc Reference Page...(name, "India");
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... gt3 = cb.gtgt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than the second.See JavaDoc Reference Page...(area, areaParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... gt4 = cb.gtgt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than the second.See JavaDoc Reference Page...(area, 1000000);

  // Greater Than or Equal (>=)
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... ge1 = cb.greaterThanOrEqualTogreaterThanOrEqualTo(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than or equal to the second.See JavaDoc Reference Page...(name, nameParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... ge2 = cb.greaterThanOrEqualTogreaterThanOrEqualTo(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than or equal to the second.See JavaDoc Reference Page...(name, "India");
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... ge3 = cb.gege(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than or equal to the second.See JavaDoc Reference Page...(area, areaParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... ge4 = cb.gege(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than or equal to the second.See JavaDoc Reference Page...(area, 1000000);

  // Less Than (<)
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... lt1 = cb.lessThanlessThan(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than the second.See JavaDoc Reference Page...(name, nameParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... lt2 = cb.lessThanlessThan(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than the second.See JavaDoc Reference Page...(name, "India");
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... lt3 = cb.ltlt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than the second.See JavaDoc Reference Page...(area, areaParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... lt4 = cb.ltlt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than the second.See JavaDoc Reference Page...(area, 1000000);

  // Less Than or Equal (<=)
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... le1 = cb.lessThanOrEqualTolessThanOrEqualTo(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than or equal to the second.See JavaDoc Reference Page...(name, nameParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... le2 = cb.lessThanOrEqualTolessThanOrEqualTo(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than or equal to the second.See JavaDoc Reference Page...(name, "India");
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... le3 = cb.lele(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than or equal to the second.See JavaDoc Reference Page...(area, areaParam);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... le4 = cb.lele(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than or equal to the second.See JavaDoc Reference Page...(area, 1000000);

  // BETWEEN
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... b1 = cb.betweenbetween(v, x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 between the second and third arguments in value.See JavaDoc Reference Page...(name, nameParam, cb.literal("Y"));
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... b2 = cb.betweenbetween(v, x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 between the second and third arguments in value.See JavaDoc Reference Page...(name, "X", "Y");
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... b3 = cb.betweenbetween(v, x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 between the second and third arguments in value.See JavaDoc Reference Page...(area, areaParam, cb.literal(2000000));
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... b4 = cb.betweenbetween(v, x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 between the second and third arguments in value.See JavaDoc Reference Page...(area, 1000000, 2000000);

  // IS [NOT] NULL
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... n1 = cb.isNullisNull(x)CriteriaBuilder's methodCreate a predicate to test whether the expression is null.See JavaDoc Reference Page...(name);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... n2 = cb.isNotNullisNotNull(x)CriteriaBuilder's methodCreate a predicate to test whether the expression is not null.See JavaDoc Reference Page...(name);

As demonstrated above, the first argument of every one of these methods is a criteria expression. The second argument (and the third argument in between) can be either a criteria expression or a comparable Java object.

The 2 letter methods (gt, ge, lt, le) can only be used for numeric comparison. The other methods can be used with any comparable objects (and isNull and isNotNull also take a non comparable object as an argument). For comparison of numbers, gt and greaterThan are equivalent, but it is a good practice to use the short form (gt) when applicable to emphasis a numeric comparison.

.