Arithmetic operations and concatenation

This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

# Mathematical operations

Your database can do the math for you. Most arithmetic operations are supported, but also string concatenation can be very efficient if done already in the database.

Arithmetic operations are implemented just like 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));	

# Datetime arithmetic

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));

For more advanced datetime arithmetic, use the Factory'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

# String concatenation

This is not really an arithmetic expression, but it's still an expression with operators: The string concatenation. jOOQ provides you with the Field's concat() method:

SELECT 'A' || 'B' || 'C' FROM DUAL

-- Or in MySQL:
SELECT concat('A', 'B', 'C')

 
// For all RDBMS, including MySQL:
create.select(concat("A", "B", "C"));