Jooq 3.16开源版结合Postgres实现批量Upsert的问题求助

问题确认 #

你的判断完全正确：Jooq 3.16.x官方仅支持到Postgres 14，与Postgres 15.7存在兼容性差异。你遇到的using (select 1 as "one")引发的ERROR: subquery in FROM must have an alias错误，确实和github issue #14582描述的问题一致——Postgres 15对FROM子句中的子查询别名要求更严格，而Jooq 3.16的usingDual()生成的SQL未适配这个变化。

另外，Jooq 3.16确实没有DSL.excluded()方法，该方法是3.17版本新增的；而insertInto + onConflict时的字段数量不匹配问题，是因为Jooq 3.16在批量操作时会强制要求参数数量与插入字段一致，哪怕doUpdate部分不需要某些字段。

可行解决方案 #

1. 升级Jooq版本（最优解） #

直接升级到Jooq 3.17及以上版本，该版本已全面支持Postgres 15，且修复了#14582问题，同时解决了你遇到的两个insertInto + onConflict问题：

• 新增DSL.excluded()方法，可直接引用插入字段的值
• 批量Upsert时，doUpdate部分无需包含仅插入字段，Jooq会自动处理参数匹配，不会触发“未指定参数”异常

示例代码：

dslContext.insertInto(YOUR_TABLE)
    .columns(YOUR_TABLE.ID, YOUR_TABLE.UPDATEABLE_FIELD, YOUR_TABLE.INSERT_ONLY_FIELD)
    .values(1, "val1", "insert_val1")
    .values(2, "val2", "insert_val2") // 批量值
    .onConflict(YOUR_TABLE.ID)
    .doUpdate()
    .set(YOUR_TABLE.UPDATEABLE_FIELD, DSL.excluded(YOUR_TABLE.UPDATEABLE_FIELD))
    .execute();

2. 不升级Jooq的临时兼容方案 #

针对insertInto + onConflict的字段数量不匹配问题

手动在doUpdate部分给仅插入字段设置为表自身的当前值，这样既不会修改该字段，又能满足参数数量要求：

dslContext.insertInto(YOUR_TABLE)
    .columns(YOUR_TABLE.ID, YOUR_TABLE.UPDATEABLE_FIELD, YOUR_TABLE.INSERT_ONLY_FIELD)
    .values(1, "val1", "insert_val1")
    .values(2, "val2", "insert_val2")
    .onConflict(YOUR_TABLE.ID)
    .doUpdate()
    .set(YOUR_TABLE.UPDATEABLE_FIELD, YOUR_TABLE.UPDATEABLE_FIELD) // 用表自身字段替代excluded()
    .set(YOUR_TABLE.INSERT_ONLY_FIELD, YOUR_TABLE.INSERT_ONLY_FIELD) // 保持原字段值，避免参数缺失
    .execute();

针对MERGE语法的子查询别名问题

放弃usingDual()，改用显式给子查询加别名的方式构造USING数据源，批量操作时需将批量数据转为子查询传入：

// 批量数据构造为子查询
Field<Integer> batchId = field("id", Integer.class);
Field<String> batchUpdateVal = field("update_val", String.class);
Field<String> batchInsertVal = field("insert_val", String.class);

Table<?> batchTable = select(batchId, batchUpdateVal, batchInsertVal)
    .from(values(
        row(1, "val1", "insert_val1"),
        row(2, "val2", "insert_val2")
    ).as("batch_data", "id", "update_val", "insert_val"));

dslContext.mergeInto(YOUR_TABLE)
    .using(batchTable)
    .on(YOUR_TABLE.ID.eq(batchId))
    .whenMatchedThenUpdate()
    .set(YOUR_TABLE.UPDATEABLE_FIELD, batchUpdateVal)
    .whenNotMatchedThenInsert()
    .set(YOUR_TABLE.ID, batchId)
    .set(YOUR_TABLE.UPDATEABLE_FIELD, batchUpdateVal)
    .set(YOUR_TABLE.INSERT_ONLY_FIELD, batchInsertVal)
    .execute();

3. 原生SQL兜底方案 #

如果上述方案都无法落地，直接使用Postgres原生的INSERT ... ON CONFLICT语句执行批量操作：

String sql = """
    INSERT INTO your_table (id, updateable_field, insert_only_field)
    VALUES (?, ?, ?), (?, ?, ?)
    ON CONFLICT (id) DO UPDATE SET
        updateable_field = EXCLUDED.updateable_field;
    """;

// 构造批量参数数组
Object[] params = {1, "val1", "insert_val1", 2, "val2", "insert_val2"};
dslContext.execute(sql, params);

内容的提问来源于stack exchange，提问作者stupor-mundi