jOOQ add to current value on update

Question

I have a column with a running count of events that happened. I'd like to perform an equivalent to the following SQL statement via a jOOQ update:

update event_table set event_count = event_count + 3;

The 3 is artibrary, it would be an int representing the current count detected in my Java program.

Is there a way to do this without selecting the value in one jOOQ select then summing in another jOOQ update, causing two database interactions?

Answer 1

Every SQL statement can be translated directly to a jOOQ statement. Use the UPDATE statement support in jOOQ. https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/update-statement

Specifically:

DSLContext ctx = ...

ctx.update(EVENT_TABLE)
   .set(EVENT_COUNT, EVENT_COUNT.plus(3))
   .execute();

As a general rule of thumb:

• All functions (e.g. fn(a, b)) are available from the DSL class as DSL.fn(a, b)
• All infix operators (e.g. a op b) are available from the Field type as a.op(b)