Numbers in JPQL and Criteria Queries

Numeric values may appear in JPQL queries in many forms:

as numeric literals - e.g. 123, -12.5.
as parameters - when numeric values are assigned as arguments.
as path expressions - in navigation to persistent numeric fields.
as aggregate expressions - e.g. COUNT.
as collection functions - when the return value is numeric, e.g. INDEX, SIZE.
as string functions - when the return value is numeric, e.g. LOCATE, LENGTH.
as composite arithmetic expressions that use operators and functions to combine simple numeric values into a more complex expression.

This page covers the following topics:

Arithmetic Operators

The following arithmetic operators are supported by JPA:

2 unary operators: + (plus) and - (minus).
4 binary operators: + (addition), - (subtraction), * (multiplication) and / (division).

ObjectDB also supports the % (modulo) and the ~ (bitwise complement) operators that are supported in Java and JDO. JPA follows Java numeric promotion principles. For example, the resulting type of a binary arithmetic operation on an int value and a double value is double.

The ABS Function

The ABS function removes the minus sign from a specified argument and returns the absolute value, which is always a positive number or zero.

For example:

ABS(-5) is evaluated to 5
ABS(10.7) is evaluated to 10.7

The ABS function takes as an argument a numeric value of any type and returns a value of the same type.

The MOD Function

The MOD function calculates the remainder of the division of one number by another, similar to the modulo operator (%) in Java (which is also supported by ObjectDB as an extension).

For example:

MOD(11, 3) is evaluated to 2 (3 goes into 11 three times with a remainder of 2)
MOD(8, 4) is evaluated to 0 (4 goes into 8 twice with a remainder of 0)

The MOD function takes two integer values of any type and returns an integer value. If the two operands share exactly the same type the result type is the same. If the two operands have different types, numeric promotion is used as with binary arithmetic operations in Java (e.g. for int and long operands the MOD function returns a long value).

The SQRT Function

The SQRT function returns the square root of a specified argument.

For example:

SQRT(9) is evaluated to 3
SQRT(2) is evaluated to 1.414213562373095

The SQRT function takes as an argument a numeric value of any type and always returns a double value.

Criteria Query Arithmetic Expressions

JPQL arithmetic operators and functions (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.

Binary Operators

The creation of a binary arithmetic operator requires two operands. At least one operand must be a criteria numeric expression. The other operand may be either another numeric expression or a simple Java numeric object:

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

  // Addition (+)
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> sum1 = cb.sumCriteriaBuilder.sum(x,y) - JPA MethodCreate an expression that returns the sum
 of its arguments.(path, param); // 2 expressions
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> sum2 = cb.sumCriteriaBuilder.sum(x,y) - JPA MethodCreate an expression that returns the sum
 of its arguments.(path, 1000); // expression + number
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> sum3 = cb.sumCriteriaBuilder.sum(x,y) - JPA MethodCreate an expression that returns the sum
 of its arguments.(1000, path); // number + expression

  // Subtraction (-)
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> diff1 = cb.diffCriteriaBuilder.diff(x,y) - JPA MethodCreate an expression that returns the difference
 between its arguments.(path, param); // 2 expressions
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> diff2 = cb.diffCriteriaBuilder.diff(x,y) - JPA MethodCreate an expression that returns the difference
 between its arguments.(path, 1000); // expression - number
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> diff3 = cb.diffCriteriaBuilder.diff(x,y) - JPA MethodCreate an expression that returns the difference
 between its arguments.(1000, path); // number - expression

  // Multiplication (*)
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> prod1 = cb.prodCriteriaBuilder.prod(x,y) - JPA MethodCreate an expression that returns the product
 of its arguments.(path, param); // 2 expressions
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> prod2 = cb.prodCriteriaBuilder.prod(x,y) - JPA MethodCreate an expression that returns the product
 of its arguments.(path, 1000); // expression * number
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> prod3 = cb.prodCriteriaBuilder.prod(x,y) - JPA MethodCreate an expression that returns the product
 of its arguments.(1000, path); // number * expression

  // Division (/)
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> quot1 = cb.quotCriteriaBuilder.quot(x,y) - JPA MethodCreate an expression that returns the quotient
 of its arguments.(path, param); // 2 expressions
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> quot2 = cb.quotCriteriaBuilder.quot(x,y) - JPA MethodCreate an expression that returns the quotient
 of its arguments.(path, 1000); // expression / number
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> quot3 = cb.quotCriteriaBuilder.quot(x,y) - JPA MethodCreate an expression that returns the quotient
 of its arguments.(1000, path); // number / expression

  // Modulo (%)
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> mod1 = cb.modCriteriaBuilder.mod(x,y) - JPA MethodCreate an expression that returns the modulus
 of its arguments.(path, param); // 2 expressions
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> mod2 = cb.modCriteriaBuilder.mod(x,y) - JPA MethodCreate an expression that returns the modulus
 of its arguments.(path, 1000); // expression % number
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> mod3 = cb.modCriteriaBuilder.mod(x,y) - JPA MethodCreate an expression that returns the modulus
 of its arguments.(1000, path); // number % expression

The above examples demonstrate only integer expressions, but all the numeric types (byte, short, int, long, float, double, BigInteger, BigDecimal) are supported.

Unary Operators

Creation of the unary minus (-) operator and the ABS and SQRT functions requires one operand, which must be a numeric expression:

  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> abs = cb.absCriteriaBuilder.abs(x) - JPA MethodCreate an expression that returns the absolute value
 of its argument.(param); // ABS(expression)
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> neg = cb.negCriteriaBuilder.neg(x) - JPA MethodCreate an expression that returns the arithmetic negation
 of its argument.(path); // -expression
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> sqrt = cb.sqrtCriteriaBuilder.sqrt(x) - JPA MethodCreate an expression that returns the square root
 of its argument.(cb.literalCriteriaBuilder.literal(value) - JPA MethodCreate an expression for a literal.(100)); // SQRT(expression)

As shown above, a number can always be converted to a numeric expression by using the literalCriteriaBuilder.literal(value) - JPA MethodCreate an expression for a literal. method.

< JPQL Paths and Types ^ Query Expressions Strings in JPQL >