Comparison in JPQL and Criteria API

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

This page covers the following topics:

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.CriteriaBuilder - JPA InterfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings. interface provides factory methods for building these expressions, as shown in the following examples:

  // Create String path and parameter expressions:
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> name = country.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("name");
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> nameParam = cb.parameterCriteriaBuilder.parameter(paramClass) - JPA MethodCreate a parameter expression.(String.class);

  // Create Integer path and parameter expressions:
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> area = country.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("area");
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> areaParam = cb.parameterCriteriaBuilder.parameter(paramClass) - JPA MethodCreate a parameter expression.(Integer.class);

  // Equal (=)
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. eq1 = cb.equalCriteriaBuilder.equal(x,y) - JPA MethodCreate a predicate for testing the arguments for equality.(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. eq2 = cb.equalCriteriaBuilder.equal(x,y) - JPA MethodCreate a predicate for testing the arguments for equality.(name, "India");
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. eq3 = cb.equalCriteriaBuilder.equal(x,y) - JPA MethodCreate a predicate for testing the arguments for equality.(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. eq4 = cb.equalCriteriaBuilder.equal(x,y) - JPA MethodCreate a predicate for testing the arguments for equality.(area, 1000000);

  // Not Equal (<>)
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ne1 = cb.notEqualCriteriaBuilder.notEqual(x,y) - JPA MethodCreate a predicate for testing the arguments for inequality.(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ne2 = cb.notEqualCriteriaBuilder.notEqual(x,y) - JPA MethodCreate a predicate for testing the arguments for inequality.(name, "India");
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ne3 = cb.notEqualCriteriaBuilder.notEqual(x,y) - JPA MethodCreate a predicate for testing the arguments for inequality.(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ne4 = cb.notEqualCriteriaBuilder.notEqual(x,y) - JPA MethodCreate a predicate for testing the arguments for inequality.(area, 1000000);

  // Greater Than (>)
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. gt1 = cb.greaterThanCriteriaBuilder.greaterThan(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than the second.(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. gt2 = cb.greaterThanCriteriaBuilder.greaterThan(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than the second.(name, "India");
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. gt3 = cb.gtCriteriaBuilder.gt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than the second.(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. gt4 = cb.gtCriteriaBuilder.gt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than the second.(area, 1000000);

  // Greater Than or Equal (>=)
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ge1 = cb.greaterThanOrEqualToCriteriaBuilder.greaterThanOrEqualTo(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than or equal to the second.(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ge2 = cb.greaterThanOrEqualToCriteriaBuilder.greaterThanOrEqualTo(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than or equal to the second.(name, "India");
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ge3 = cb.geCriteriaBuilder.ge(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than or equal to the second.(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ge4 = cb.geCriteriaBuilder.ge(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than or equal to the second.(area, 1000000);

  // Less Than (<)
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. lt1 = cb.lessThanCriteriaBuilder.lessThan(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than the second.(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. lt2 = cb.lessThanCriteriaBuilder.lessThan(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than the second.(name, "India");
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. lt3 = cb.ltCriteriaBuilder.lt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than the second.(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. lt4 = cb.ltCriteriaBuilder.lt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than the second.(area, 1000000);

  // Less Than or Equal (<=)
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. le1 = cb.lessThanOrEqualToCriteriaBuilder.lessThanOrEqualTo(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than or equal to the second.(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. le2 = cb.lessThanOrEqualToCriteriaBuilder.lessThanOrEqualTo(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than or equal to the second.(name, "India");
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. le3 = cb.leCriteriaBuilder.le(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than or equal to the second.(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. le4 = cb.leCriteriaBuilder.le(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than or equal to the second.(area, 1000000);

  // BETWEEN
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. b1 = cb.betweenCriteriaBuilder.between(v,x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 between the second and third arguments in value.(name, nameParam, cb.literal("Y"));
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. b2 = cb.betweenCriteriaBuilder.between(v,x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 between the second and third arguments in value.(name, "X", "Y");
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. b3 = cb.betweenCriteriaBuilder.between(v,x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 between the second and third arguments in value.(area, areaParam, cb.literal(2000000));
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. b4 = cb.betweenCriteriaBuilder.between(v,x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 between the second and third arguments in value.(area, 1000000, 2000000);

  // IS [NOT] NULL
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. n1 = cb.isNullCriteriaBuilder.isNull(x) - JPA MethodCreate a predicate to test whether the expression is null.(name);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. n2 = cb.isNotNullCriteriaBuilder.isNotNull(x) - JPA MethodCreate a predicate to test whether the expression is not null.(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.

< Collections in JPQL ^ Query Expressions Logical Operators >