JOOQ在insertInto的values方法调用sequence.nextval()多字段报错问题

问题现象 #

• 用.set()逐个赋值（包含sequence.nextval()）能正常执行
• 仅插入sequence字段时，.values()方法调用正常
• 插入多个字段时，.values()里同时传sequence.nextval()和普通值会报错，错误提示：无法解析方法 'values(Field<Long>, int)'

示例代码 #

正常执行代码1

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

正常执行代码2

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

报错代码

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

原因分析 #

JOOQ的.values()方法对参数类型有严格要求：要么全是纯值类型（比如Long、int），要么全是Field类型。当你混合传入Field<Long>（sequence.nextval()的返回结果）和int类型时，Java的类型推断无法找到匹配的方法重载，就会抛出这个错误。

解决办法 #

方法1：把普通值包装成Field类型

用DSL.val()方法把基本类型转成对应的Field对象，让所有参数类型统一：

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

方法2：继续使用.set()逐个赋值

这种方式不需要考虑参数类型统一，JOOQ会逐个处理每个字段的赋值，兼容性更好：

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

方法3：使用Row类型构造行数据

通过Row2（对应2个字段）来构造要插入的行，明确类型：

Row2<Field<Long>, Integer> row = DSL.row(APPOINTMENTTYPE_SEQ.nextval(), 255);
int y = dslContext.insertInto(APPOINTMENTTYPE, APPOINTMENTTYPE.ID, APPOINTMENTTYPE.BLUE)
                  .values(row)
                  .execute();

内容的提问来源于stack exchange，提问作者Chiral Systems