如何在Kotlin Exposed DSL中实现指定列的Upsert（插入或更新）功能

需求说明 #

实现Account表的Upsert逻辑：

• 插入新记录时，保存所有10列的数据
• 当主键（clientAccountNumber）冲突时，仅更新firstName、lastName和phoneNumber三列

表结构定义 #

object AccountTable : IdTable<String>(name = "Account") {
    val clientAccountNumber = varchar("clientAccountNumber", AccountId.LEN).entityId()
    override val id: Column<EntityID<String>> = clientAccountNumber

    val userId = uuid("userId").references(UserTable.id).nullable()
    val bourseCode = varchar("bourseCode", BourseCode.MAX_LEN)
    val firstName = varchar("firstName", Account.FIRST_NAME_MAX_LEN)
    val lastName = varchar("lastName", Account.LAST_NAME_MAX_LEN)
    val phoneNumber = varchar("phoneNumber", PhoneNumber.MAX_LEN)
    val nationalCode = varchar("nationalCode", NationalCode.MAX_LEN)
    val marketMakerFor = varchar("marketMakerFor", Account.MARKET_MAKER_MAX_LEN)
    val applyCommissionFeeFloor = bool("applyCommissionFeeFloor")
    val preview = bool("preview")
}

Upsert实现代码 #

// 示例数据：包含所有列的待插入/更新值
val accountData = mapOf(
    AccountTable.clientAccountNumber to "ACC123456",
    AccountTable.userId to UUID.randomUUID(),
    AccountTable.bourseCode to "TSE",
    AccountTable.firstName to "John",
    AccountTable.lastName to "Doe",
    AccountTable.phoneNumber to "+123456789",
    AccountTable.nationalCode to "1234567890",
    AccountTable.marketMakerFor to "MM1",
    AccountTable.applyCommissionFeeFloor to true,
    AccountTable.preview to false
)

// 执行Upsert操作
AccountTable.upsert(
    // 指定冲突判断的主键列
    keys = listOf(AccountTable.id),
    // 冲突时仅更新指定三列，值取本次插入语句中的对应值
    onUpdate = listOf(
        AccountTable.firstName eq AccountTable.firstName.insertValue,
        AccountTable.lastName eq AccountTable.lastName.insertValue,
        AccountTable.phoneNumber eq AccountTable.phoneNumber.insertValue
    )
) {
    // 填充所有列数据，插入新记录时生效
    accountData.forEach { (column, value) ->
        this[column] = value
    }
}

关键逻辑说明 #

• keys参数：指定用于判断记录冲突的列，这里用主键id（对应clientAccountNumber），当该列值已存在时触发更新逻辑。
• onUpdate参数：通过column.eq(column.insertValue)定义更新规则，意思是冲突发生时，将目标列的值替换为本次插入操作中提供的新值。只需要在这里列出需要更新的列即可。
• 初始化块：填充所有列的数据，确保插入新记录时能保存完整的账户信息。

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