Search code examples
jooq

Specify function as value in Jooq

Using Jooq, I would like to specify the value of a field during insert as a function.

In SQL Server the equivalent query is:

insert into table (a, t) values ('foo', SYSDATETIME())

Solution

  • Mixing bind values with expressions:

    Assuming you're using generated code, write this:

    // Assuming this static import, as always:
import static org.jooq.impl.DSL.*;

ctx.insertInto(TABLE)
   .columns(TABLE.A, TABLE.T)
   .values(val("foo"), currentTimestamp()) // or currentLocalDateTime()
   .execute();

    The VALUES() clause only has 2 overloads:

    • Accepting only bind variables
    • Accepting only expressions

    It doesn't have an overload mixing both things, because there would be an exponential number of needed overloads. So the key thing to do here is to wrap your bind variables using DSL.val() explicitly.

    See also this question: How do I create a Field<T> from a value T in jOOQ, explicitly?

    A note on SYSDATETIME

    jOOQ supports the standard SQL CURRENT_TIMESTAMP expression via DSL.currentTimestamp() and DSL.currentLocalDateTime(). If you prefer using SYSDATETIME, you can create a plain SQL template for that, or use DSL.function()