Numbers in JPQL and Criteria Queries

Numeric values can appear in JPQL queries in several forms:

As numeric literals, such as 123 and -12.5.
As parameters that are bound to numeric arguments.
As path expressions that navigate to persistent numeric fields.
As aggregate expressions, such as COUNT.
As collection functions that return a numeric value, such as INDEX and SIZE.
As string functions that return a numeric value, such as LOCATE and LENGTH.
As composite arithmetic expressions that use operators and functions to combine simple numeric values into more complex expressions.

This page covers the following topics:

Arithmetic operators The ABS function The MOD function The SQRT function Criteria query arithmetic expressions

Arithmetic operators

JPA supports the following arithmetic operators:

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

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

The ABS function

The ABS function returns the absolute value of a numeric argument. The result is always a positive number or zero.

For example:

ABS(-5) evaluates to 5.
ABS(10.7) evaluates to 10.7.

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

The MOD function

The MOD function calculates the remainder of a division, similar to the Java modulo operator (%). ObjectDB also supports the % operator as an extension.

For example:

MOD(11, 3) evaluates to 2.
MOD(8, 4) evaluates to 0.

The MOD function takes two integer arguments and returns an integer value. If both operands have the same type, the result is of that same type. If the operands have different types, Java numeric promotion rules apply. For example, if the operands are an int and a long, the MOD function returns a long.

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 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.

Binary Operators

Creating a binary arithmetic operator requires two operands. At least one operand must be a criteria numeric expression. The other operand can be another numeric expression or a simple Java numeric object.

  // Create path and parameter expressions:
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> path = country.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("population");
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> param = cb.parameterjakarta.persistence.criteria.CriteriaBuilder.parameter(Class)Create a parameter expression.(Integer.class);
  // Addition (+)
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> sum1 = cb.sumjakarta.persistence.criteria.CriteriaBuilder.sum(Expression,Expression)Create an expression that returns the sum of its arguments.(path, param); // 2 expressions
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> sum2 = cb.sumjakarta.persistence.criteria.CriteriaBuilder.sum(Expression,N)Create an expression that returns the sum of its arguments.(path, 1000); // expression + number
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> sum3 = cb.sumjakarta.persistence.criteria.CriteriaBuilder.sum(N,Expression)Create an expression that returns the sum of its arguments.(1000, path); // number + expression
  // Subtraction (-)
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> diff1 = cb.diffjakarta.persistence.criteria.CriteriaBuilder.diff(Expression,Expression)Create an expression that returns the difference between its arguments.(path, param); // 2 expressions
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> diff2 = cb.diffjakarta.persistence.criteria.CriteriaBuilder.diff(Expression,N)Create an expression that returns the difference between its arguments.(path, 1000); // expression - number
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> diff3 = cb.diffjakarta.persistence.criteria.CriteriaBuilder.diff(N,Expression)Create an expression that returns the difference between its arguments.(1000, path); // number - expression
  // Multiplication (*)
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> prod1 = cb.prodjakarta.persistence.criteria.CriteriaBuilder.prod(Expression,Expression)Create an expression that returns the product of its arguments.(path, param); // 2 expressions
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> prod2 = cb.prodjakarta.persistence.criteria.CriteriaBuilder.prod(Expression,N)Create an expression that returns the product of its arguments.(path, 1000); // expression * number
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> prod3 = cb.prodjakarta.persistence.criteria.CriteriaBuilder.prod(N,Expression)Create an expression that returns the product of its arguments.(1000, path); // number * expression
  // Division (/)
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> quot1 = cb.quotjakarta.persistence.criteria.CriteriaBuilder.quot(Expression,Expression)Create an expression that returns the quotient of its arguments.(path, param); // 2 expressions
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> quot2 = cb.quotjakarta.persistence.criteria.CriteriaBuilder.quot(Expression,Number)Create an expression that returns the quotient of its arguments.(path, 1000); // expression / number
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> quot3 = cb.quotjakarta.persistence.criteria.CriteriaBuilder.quot(Number,Expression)Create an expression that returns the quotient of its arguments.(1000, path); // number / expression
  // Modulo (%)
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> mod1 = cb.modjakarta.persistence.criteria.CriteriaBuilder.mod(Expression,Expression)Create an expression that returns the modulus (remainder under integer division) of its arguments.(path, param); // 2 expressions
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> mod2 = cb.modjakarta.persistence.criteria.CriteriaBuilder.mod(Expression,Integer)Create an expression that returns the modulus (remainder under integer division) of its arguments.(path, 1000); // expression % number
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> mod3 = cb.modjakarta.persistence.criteria.CriteriaBuilder.mod(Integer,Expression)Create an expression that returns the modulus (remainder under integer division) of its arguments.(1000, path); // number % expression

The previous examples use integer expressions, but all numeric types are supported, including byte, short, int, long, float, double, BigInteger, and BigDecimal.

Unary Operators

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

  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> abs = cb.absjakarta.persistence.criteria.CriteriaBuilder.abs(Expression)Create an expression that returns the absolute value of its argument.(param); // ABS(expression)
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> neg = cb.negjakarta.persistence.criteria.CriteriaBuilder.neg(Expression)Create an expression that returns the arithmetic negation of its argument.(path); // -expression
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> sqrt = cb.sqrtjakarta.persistence.criteria.CriteriaBuilder.sqrt(Expression)Create an expression that returns the square root of its argument.(cb.literaljakarta.persistence.criteria.CriteriaBuilder.literal(T)Create an expression for a literal.(100)); // SQRT(expression)

As shown in the previous example, you can convert a number to a numeric expression by using the literaljakarta.persistence.criteria.CriteriaBuilder.literal(T)Create an expression for a literal. method.

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