Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
Arithmetic expressions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Numeric arithmetic expressions
Your database can do the math for you. Arithmetic operations are implemented just like numeric functions, with similar limitations as far as type restrictions are concerned. You can use any of these operators:
+ - * / %
In order to express a SQL query like this one:
SELECT ((1 + 2) * (5 - 3) / 2) % 10 FROM DUAL
You can write something like this in jOOQ:
create.select(val(1).add(2).mul(val(5).sub(3)).div(2).mod(10)).fetch();
Operator precedence
jOOQ does not know any operator precedence (see also boolean operator precedence). All operations are evaluated from left to right, as with any object-oriented API. The two following expressions are the same:
val(1).add(2) .mul(val(5).sub(3)) .div(2) .mod(10); (((val(1).add(2)).mul(val(5).sub(3))).div(2)).mod(10);
Datetime arithmetic expressions
jOOQ also supports the Oracle-style syntax for adding days to a Field<? extends java.util.Date>
SELECT SYSDATE + 3 FROM DUAL;
create.select(currentTimestamp().add(3)).fetch();
For more advanced datetime arithmetic, use the DSL's timestampDiff() and dateDiff() functions, as well as jOOQ's built-in SQL standard INTERVAL data type support:
-
INTERVAL YEAR TO MONTH:org.jooq.types.YearToMonth -
INTERVAL DAY TO SECOND:org.jooq.types.DayToSecond
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!