如何在jOOQ中使用多列unnest实现批量更新？

PostgreSQL多列unnest特性说明 #

PostgreSQL支持unnest(anyarray, anyarray [, ...])语法，可将多个数组按索引位置展开为多行多列结果，示例如下：

select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b);

执行结果：

a | b
---+-----
1 | foo
2 | bar
| baz

原生批量更新SQL实现 #

基于该特性，可通过CTE构造批量更新逻辑，将待更新ID数组与对应新值数组关联后执行更新：

WITH
   updates as (
       SELECT * FROM unnest(
           target_ids,
           updated_foos
) as x(target_id, new_foo)
   )
UPDATE target t
    SET foo = u.new_foo
    FROM updates u
    WHERE t.id = u.target_id;

其中target_ids存储待更新实体ID，updated_foos存储对应属性新值，ID数组第i个元素对应新值数组第i个元素，实现批量映射更新。

现有jOOQ（Kotlin）实现问题 #

当前实现依赖原生SQL字符串拼接，虽能正常运行，但代码冗余且不够优雅：

val targetId = "target_id"
val newFoo = "new_foo"

val updates = DSL // 存储待更新数据的临时表（targetId, newFoo）
    .name("updates")
    .fields(targetId, newFoo)
    .`as`(
        DSL.select(
            DSL.field(targetId),
            DSL.field(newFoo)
        ).from(
            DSL.table(
                "(SELECT * FROM unnest(?,?)) as x($targetId, $newFoo)",
                DSL.value(updatingTargets.toTypedArray()),
                DSL.value(newFoos.toTypedArray()),
            )
        )
    )

ctx
    .with(updates)
    .update(TARGET)
    .set(TARGET.FOO, updates.field(newFoo, String::class.javaObjectType))
    .from(updates)
    .where(TARGET.ID.eq(updates.field(targetId, Int::class.javaObjectType)))
    .execute()

优化后的jOOQ实现方案 #

以下方案完全基于jOOQ API构造，避免原生SQL字符串拼接，兼顾简洁性与类型安全性：

方案1：保留CTE结构 #

// 定义字段别名与类型
val targetIdField = DSL.field("target_id", Int::class.java)
val newFooField = DSL.field("new_foo", String::class.java)

// 构造多列unnest表表达式
val unnestTable = DSL.table(
    DSL.function(
        "unnest",
        // 指定返回字段的类型列表
        arrayOf(SQLDataType.INTEGER.nullable(true), SQLDataType.VARCHAR.nullable(true)),
        DSL.value(updatingTargets.toTypedArray()),
        DSL.value(newFoos.toTypedArray())
    )
).`as`("x", targetIdField, newFooField)

// 构建CTE
val updates = DSL.name("updates")
    .fields(targetIdField, newFooField)
    .`as`(DSL.select(targetIdField, newFooField).from(unnestTable))

// 执行批量更新
ctx.with(updates)
    .update(TARGET)
    .set(TARGET.FOO, updates.field(newFooField))
    .from(updates)
    .where(TARGET.ID.eq(updates.field(targetIdField)))
    .execute()

方案2：简化为直接FROM子句（无需CTE） #

如果不需要复用CTE逻辑，可直接将unnest表嵌入UPDATE的FROM子句，代码更简洁：

ctx.update(TARGET)
    .set(TARGET.FOO, DSL.field("new_foo", String::class.java))
    .from(
        DSL.table(
            DSL.function(
                "unnest",
                arrayOf(SQLDataType.INTEGER, SQLDataType.VARCHAR),
                DSL.value(updatingTargets.toTypedArray()),
                DSL.value(newFoos.toTypedArray())
            )
        ).`as`("x", "target_id", "new_foo")
    )
    .where(TARGET.ID.eq(DSL.field("x.target_id", Int::class.java)))
    .execute()

两种方案均通过jOOQ的DSL.function直接调用PostgreSQL的多参数unnest函数，配合字段别名完成映射，彻底摆脱原生SQL字符串拼接。

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