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 Interface)Used 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 Interface)Type for query expressions.<String> name = country.get("name");
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<String> nameParam = cb.parameter(String.class);

  // Create Integer path and parameter expressions:
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<Integer> area = country.get("area");
  Expressionjavax.persistence.criteria.Expression (JPA Interface)Type for query expressions.<Integer> areaParam = cb.parameter(Integer.class);

  // Equal (=)
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. eq1 = cb.equal(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. eq2 = cb.equal(name, "India");
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. eq3 = cb.equal(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. eq4 = cb.equal(area, 1000000);

  // Not Equal (<>)
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ne1 = cb.notEqual(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ne2 = cb.notEqual(name, "India");
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ne3 = cb.notEqual(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ne4 = cb.notEqual(area, 1000000);

  // Greater Than (>)
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. gt1 = cb.greaterThan(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. gt2 = cb.greaterThan(name, "India");
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. gt3 = cb.gt(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. gt4 = cb.gt(area, 1000000);

  // Greater Than or Equal (>=)
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ge1 = cb.greaterThanOrEqualTo(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ge2 = cb.greaterThanOrEqualTo(name, "India");
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ge3 = cb.ge(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. ge4 = cb.ge(area, 1000000);

  // Less Than (<)
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. lt1 = cb.lessThan(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. lt2 = cb.lessThan(name, "India");
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. lt3 = cb.lt(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. lt4 = cb.lt(area, 1000000);

  // Less Than or Equal (<=)
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. le1 = cb.lessThanOrEqualTo(name, nameParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. le2 = cb.lessThanOrEqualTo(name, "India");
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. le3 = cb.le(area, areaParam);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. le4 = cb.le(area, 1000000);

  // BETWEEN
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. b1 = cb.between(name, nameParam, cb.literal("Y"));
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. b2 = cb.between(name, "X", "Y");
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. b3 = cb.between(area, areaParam, cb.literal(2000000));
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. b4 = cb.between(area, 1000000, 2000000);

  // IS [NOT] NULL
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. n1 = cb.isNull(name);
  Predicatejavax.persistence.criteria.Predicate (JPA Interface)The type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. n2 = cb.isNotNull(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 >