JOOQ sequences in insertinto, set vs values

Issue

I have a simple table with a seqence. When I use sequence.nextval() in the insertInto .set method everything is fine. When I use sequence.nextval() in the insertInto .values method with just the sequence, specifiend again everything is fine. When I use sequence.nextval() in the insertInto .values method with more than the sequence, specifiend, I get an error.

This is OK

        int w = dslContext
                .insertInto(APPOINTMENTTYPE)
                .set(APPOINTMENTTYPE.MC, 501559436L)
                .set(APPOINTMENTTYPE.ID, HIBERNATE_SEQUENCE.nextval())
                .set(APPOINTMENTTYPE.NAME, "test name")
                .set(APPOINTMENTTYPE.DESCRIPTION, "test description")
                .set(APPOINTMENTTYPE.BLUE, 255)
                .set(APPOINTMENTTYPE.GREEN, 255)
                .set(APPOINTMENTTYPE.RED, 255)
                .set(APPOINTMENTTYPE.REMINDERINTERVAL, 1)
                .execute();

This is also OK

        int x = dslContext.insertInto(APPOINTMENTTYPE, 
APPOINTMENTTYPE.ID).values(APPOINTMENTTYPE_SEQ.nextval()).execute();

This gives the error Cannot resolve method 'values(Field<Long>, int)'

        int y = dslContext.insertInto(APPOINTMENTTYPE, APPOINTMENTTYPE.ID, APPOINTMENTTYPE.BLUE).values(APPOINTMENTTYPE_SEQ.nextval(), 255).execute();

Solution

There are only two sets of overloads of the values() clause:

• One where all arguments are bind values, i.e. values(T1, T2, ...)
• One where all arguments are expressions, i.e. values(Field<T1>, Field<T2>, ...)

If arbitrary combinations of bind values and expressions were to be supported, we'd quickly get a prohibitive (exponential!) number of combinations in an already big API.

As such, in your case, you just have to wrap your bind value using DSL.val() explicitly, i.e. DSL.val(255). I.e. like this:

int x = dslContext
    .insertInto(APPOINTMENTTYPE, APPOINTMENTTYPE.ID, APPOINTMENTTYPE.BLUE)
    .values(APPOINTMENTTYPE_SEQ.nextval(), DSL.val(255))
    .execute();

See also:

• https://www.jooq.org/doc/latest/manual/sql-building/bind-values/indexed-parameters/
• How do I create a Field<T> from a value T in jOOQ, explicitly?

This limitation obviously doesn't affect methods with fixed sets of arguments, such as set()

Answered By - Lukas Eder