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:

The main difference between the two sets is the syntax for the equal to (= vs. ==) and not equal to (<> vs. !=) operators. JPQL follows SQL notation, while Java uses its own notation, which is also used by JDOQL (the JDO Query Language). ObjectDB supports both forms. In addition to the different notation, these operator sets also handle NULL values differently.

Comparing NULL values

The following table shows how each comparison operator handles NULL values. One column shows the result of comparing a NULL value with a non-NULL value. The other column shows the result of comparing two NULL values:

Comparison operators always evaluate to TRUE, FALSE, or NULL.

When neither operand is NULL (a case not shown in the table), the operator evaluates to either TRUE or FALSE. In this case, == is equivalent to =, and != is equivalent to <>.

When at least one operand is NULL, the == and != operators follow standard Java logic. For example, null == null evaluates to true. All other operators (=, <>, <, >, <=, >=) follow SQL logic, where NULL represents an unknown value. Any expression that includes an unknown value evaluates to NULL (unknown).

IS [NOT] NULL

To check for NULL values in standard JPQL, use the IS NULL and IS NOT NULL operators, which are also part of the SQL standard:

c.president IS NULL
c.president IS NOT NULL

The preceding expressions are equivalent to the following expressions, which are non-standard in JPQL but standard in Java and JDOQL:

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

Comparable data types

Comparison operators are supported for values of the following data types:

• Numeric types: Any comparison operator can be used with primitive types (byte, short, char, int, long, float, double), their corresponding wrapper types (Byte, Short, Character, Integer, Long, Float, Double), BigInteger, and BigDecimal.
• String values: Any comparison operator can be used.
  The equality operators (=, <>, ==, !=) follow the logic of Java's equals() method, comparing content rather than object identity.
• Data and Time values: Any comparison operator can be used.
  The equality operators (=, <>, ==, !=) follow the logic of the equals() method, comparing content rather than object identity.
• boolean and Boolean values: Can be compared using the equality operators (=, <>, ==, !=). For Boolean instances, the comparison follows the logic of the equals() method.
• Enum values: Can be compared using the equality operators (=, <>, ==, !=).
• User-defined class instances (entities and embeddable classes): Can be compared using the equality operators (=, <>, ==, !=). For entities, e1 = e2 is true if e1 and e2 have the same type and the same primary key value. For embeddable objects, e1 = e2 is true if e1 and e2 have the same content.

ObjectDB supports comparisons between any two values that belong to the same group listed above. For example, a double can be compared to a BigInteger instance, but not to a String instance.

[NOT] BETWEEN

The BETWEEN operator is a convenient shortcut for two comparisons.

The following two expressions are equivalent. In these examples, :min and :max are query parameters:

x BETWEEN :min AND :max
x >= :min AND x <= :max

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

x NOT BETWEEN :min AND :max
x < :min OR x > :max

Criteria query comparison

The JPQL comparison operators described above are also available as JPA criteria query expressions. The CriteriaBuilderjakarta.persistence.criteria.CriteriaBuilderUsed 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:
    Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> name = country.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name");
    Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> nameParam = cb.parameterjakarta.persistence.criteria.CriteriaBuilder.parameter(Class)Create a parameter expression.(String.class);
    // Create Integer path and parameter expressions:
    Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> area = country.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("area");
    Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> areaParam = cb.parameterjakarta.persistence.criteria.CriteriaBuilder.parameter(Class)Create a parameter expression.(Integer.class);
    // Equal (=)
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. eq1 = cb.equaljakarta.persistence.criteria.CriteriaBuilder.equal(Expression,Expression)Create a predicate for testing the arguments for equality.(name, nameParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. eq2 = cb.equaljakarta.persistence.criteria.CriteriaBuilder.equal(Expression,Object)Create a predicate for testing the arguments for equality.(name, "India");
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. eq3 = cb.equaljakarta.persistence.criteria.CriteriaBuilder.equal(Expression,Expression)Create a predicate for testing the arguments for equality.(area, areaParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. eq4 = cb.equaljakarta.persistence.criteria.CriteriaBuilder.equal(Expression,Object)Create a predicate for testing the arguments for equality.(area, 1000000);
    // Not Equal (<>)
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. ne1 = cb.notEqualjakarta.persistence.criteria.CriteriaBuilder.notEqual(Expression,Expression)Create a predicate for testing the arguments for inequality.(name, nameParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. ne2 = cb.notEqualjakarta.persistence.criteria.CriteriaBuilder.notEqual(Expression,Object)Create a predicate for testing the arguments for inequality.(name, "India");
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. ne3 = cb.notEqualjakarta.persistence.criteria.CriteriaBuilder.notEqual(Expression,Expression)Create a predicate for testing the arguments for inequality.(area, areaParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. ne4 = cb.notEqualjakarta.persistence.criteria.CriteriaBuilder.notEqual(Expression,Object)Create a predicate for testing the arguments for inequality.(area, 1000000);
    // Greater Than (>)
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. gt1 = cb.greaterThanjakarta.persistence.criteria.CriteriaBuilder.greaterThan(Expression,Expression)Create a predicate for testing whether the first argument is greater than the second.(name, nameParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. gt2 = cb.greaterThanjakarta.persistence.criteria.CriteriaBuilder.greaterThan(Expression,Y)Create a predicate for testing whether the first argument is greater than the second.(name, "India");
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. gt3 = cb.gtjakarta.persistence.criteria.CriteriaBuilder.gt(Expression,Expression)Create a predicate for testing whether the first argument is greater than the second.(area, areaParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. gt4 = cb.gtjakarta.persistence.criteria.CriteriaBuilder.gt(Expression,Number)Create a predicate for testing whether the first argument is greater than the second.(area, 1000000);
    // Greater Than or Equal (>=)
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. ge1 = cb.greaterThanOrEqualTojakarta.persistence.criteria.CriteriaBuilder.greaterThanOrEqualTo(Expression,Expression)Create a predicate for testing whether the first argument is greater than or equal to the second.(name, nameParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. ge2 = cb.greaterThanOrEqualTojakarta.persistence.criteria.CriteriaBuilder.greaterThanOrEqualTo(Expression,Y)Create a predicate for testing whether the first argument is greater than or equal to the second.(name, "India");
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. ge3 = cb.gejakarta.persistence.criteria.CriteriaBuilder.ge(Expression,Expression)Create a predicate for testing whether the first argument is greater than or equal to the second.(area, areaParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. ge4 = cb.gejakarta.persistence.criteria.CriteriaBuilder.ge(Expression,Number)Create a predicate for testing whether the first argument is greater than or equal to the second.(area, 1000000);
    // Less Than (<)
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. lt1 = cb.lessThanjakarta.persistence.criteria.CriteriaBuilder.lessThan(Expression,Expression)Create a predicate for testing whether the first argument is less than the second.(name, nameParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. lt2 = cb.lessThanjakarta.persistence.criteria.CriteriaBuilder.lessThan(Expression,Y)Create a predicate for testing whether the first argument is less than the second.(name, "India");
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. lt3 = cb.ltjakarta.persistence.criteria.CriteriaBuilder.lt(Expression,Expression)Create a predicate for testing whether the first argument is less than the second.(area, areaParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. lt4 = cb.ltjakarta.persistence.criteria.CriteriaBuilder.lt(Expression,Number)Create a predicate for testing whether the first argument is less than the second.(area, 1000000);
    // Less Than or Equal (<=)
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. le1 = cb.lessThanOrEqualTojakarta.persistence.criteria.CriteriaBuilder.lessThanOrEqualTo(Expression,Expression)Create a predicate for testing whether the first argument is less than or equal to the second.(name, nameParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. le2 = cb.lessThanOrEqualTojakarta.persistence.criteria.CriteriaBuilder.lessThanOrEqualTo(Expression,Y)Create a predicate for testing whether the first argument is less than or equal to the second.(name, "India");
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. le3 = cb.lejakarta.persistence.criteria.CriteriaBuilder.le(Expression,Expression)Create a predicate for testing whether the first argument is less than or equal to the second.(area, areaParam);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. le4 = cb.lejakarta.persistence.criteria.CriteriaBuilder.le(Expression,Number)Create a predicate for testing whether the first argument is less than or equal to the second.(area, 1000000);
    // BETWEEN
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. b1 = cb.betweenjakarta.persistence.criteria.CriteriaBuilder.between(Expression,Expression,Expression)Create a predicate for testing whether the first argument is between the second and third arguments in value.(name, nameParam, cb.literal("Y"));
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. b2 = cb.betweenjakarta.persistence.criteria.CriteriaBuilder.between(Expression,Y,Y)Create a predicate for testing whether the first argument is between the second and third arguments in value.(name, "X", "Y");
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. b3 = cb.betweenjakarta.persistence.criteria.CriteriaBuilder.between(Expression,Expression,Expression)Create a predicate for testing whether the first argument is between the second and third arguments in value.(area, areaParam, cb.literal(2000000));
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. b4 = cb.betweenjakarta.persistence.criteria.CriteriaBuilder.between(Expression,Y,Y)Create a predicate for testing whether the first argument is between the second and third arguments in value.(area, 1000000, 2000000);
    // IS [NOT] NULL
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. n1 = cb.isNulljakarta.persistence.criteria.CriteriaBuilder.isNull(Expression)Create a predicate to test whether the expression is null.(name);
    Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. n2 = cb.isNotNulljakarta.persistence.criteria.CriteriaBuilder.isNotNull(Expression)Create a predicate to test whether the expression is not null.(name);

As shown in the examples, the first argument for each of these methods is a criteria expression. The second argument (and the third argument for between()) can be either another criteria expression or a comparable Java object.

The two-letter methods (gt(), ge(), lt(), le()) are for numeric comparisons only. The other methods can be used with any comparable objects. The isNull() and isNotNull() methods also accept non-comparable objects as arguments. For numeric comparisons, gt() and greaterThan() are equivalent, but it is good practice to use the shorter form to emphasize that the comparison is numeric.