如何在Kotlin Exposed+PostgreSQL中实现标签的不存在则插入？

我正在开发一款费用追踪应用，采用Spring Boot+Kotlin构建API，用户数据存储于PostgreSQL数据库，选用Exposed ORM处理Spring与Postgres之间的CRUD操作。

为了在数据库中插入费用记录，用户可选择标签以更好地整理费用。费用与标签为多对多关系（一个费用可关联多个标签，一个标签可关联多个费用）。

表结构代码 #

Expense 表

object ExpenseTable : IntIdTable("expense") {
    val userId: Column<String> = varchar("user_id", 50)
    val concept: Column<String> = varchar("concept", 50)
    val total: Column<Double> = double("total")
    val dateAdded: Column<LocalDateTime> = datetime("date_added")
    val comments: Column<String?> = varchar("comments", 200).nullable()
}

class ExpenseEntity(
    id: EntityID<Int>
) : IntEntity(id) {
    companion object : IntEntityClass<ExpenseEntity>(ExpenseTable)

    var userId by ExpenseTable.userId
    var concept by ExpenseTable.concept
    var total by ExpenseTable.total
    var dateAdded by ExpenseTable.dateAdded
    var tags by TagEntity via ExpensesTags
    var comments by ExpenseTable.comments

    fun toExpense() = Expenses(
        id.value,
        userId,
        concept,
        total,
        dateAdded,
        tags.toList().toTags(),
        comments
    )
}

Tag 表

object TagsTable: IntIdTable("tag") {
    val tagName: Column<String> = varchar("tag_name", 25)
    val dateAdded: Column<LocalDateTime> = datetime("date_added")
}

class TagEntity(
    id: EntityID<Int>
): IntEntity(id) {
    companion object: IntEntityClass<TagEntity>(TagsTable)

    var tagName by TagsTable.tagName
    var dateAdded by TagsTable.dateAdded

    fun toTags() = Tags(
        id.value,
        tagName,
        dateAdded
    )
}

多对多关系表

object ExpensesTags : Table() {
    val expense = reference("expense", ExpenseTable)
    val tag = reference("tag", TagsTable)
    override val primaryKey = PrimaryKey(expense, tag, name = "PK_ExpensesTags")
}

我希望确保用户创建的标签唯一，目前的问题是无法通过ORM现有代码直接判断用户使用的标签是否存在。

我当前的实现如下，但这种先查询再判断是否插入的方式不够优雅，存在冗余：

fun insertExpense(expenses: ExpensesPost): Expenses {
    val userIdName = authenticationFacade.userId()
    val tagsPost = expenses.tag

    // We only accept 10 tags max per request
    if (tagsPost.size > MAX_TAG_REQUEST) throw BadRequestException(
        Status.BAD_REQUEST,
        "Only $MAX_TAG_REQUEST tags are allowed"
    )

    var insertedExpense: Expenses? = null
    loggedTransaction {
        // Check if some tags already exists
        tagsPost.forEach { tag ->
            val internTag = tagsCrudTable.find { TagsTable.tagName eq tag.tagName }.firstOrNull()
            // Only insert into the table tags that doesn't exist
            if (internTag == null) {
                tagsCrudTable.new {
                    dateAdded = tag.dateAdded
                    tagName = tag.tagName
                }
            }
        }
        // Get all the tags that come from the request
        val tagsArr = mutableListOf<TagEntity>()
        tagsPost.map {
            val internTag = tagsCrudTable.find {
                TagsTable.tagName eq it.tagName
            }.first()
            tagsArr.add(internTag)
        }
        val expense = expenseCrudTable.new {
            userId = userIdName
            concept = expenses.concept
            total = expenses.total
            dateAdded = expenses.dateAdded
            comments = expenses.comments
        }

        expense.tags = SizedCollection(tagsArr)
        insertedExpense = expense.toExpense()
    }

    return insertedExpense ?: throw EntityNotFoundException(
        status = Status.NO_DATA,
        customMessage = "Something went wrong",
        id = authenticationFacade.userId()
    )
}

现寻求更优实现方式，如何在Kotlin Exposed中高效实现标签的不存在则插入操作？

第一步：数据库层面加唯一约束 #

首先在TagsTable的tagName字段上添加唯一约束，从根源上保证标签名称的唯一性，避免并发场景下的重复插入问题：

object TagsTable: IntIdTable("tag") {
    val tagName: Column<String> = varchar("tag_name", 25).uniqueIndex()
    val dateAdded: Column<LocalDateTime> = datetime("date_added")
}

第二步：封装"获取或创建"标签的复用函数 #

利用Exposed对PostgreSQL INSERT ... ON CONFLICT语法的封装，实现高效的"不存在则插入"逻辑，避免冗余查询：

方式1：仅插入不存在的标签（忽略冲突）

private fun getOrCreateTag(tagPost: TagPost): TagEntity {
    // 先查询已有标签
    return TagEntity.find { TagsTable.tagName eq tagPost.tagName }.firstOrNull() ?: run {
        // 不存在则插入，冲突时忽略
        TagsTable.insertIgnore {
            it[tagName] = tagPost.tagName
            it[dateAdded] = tagPost.dateAdded
        }
        // 再次查询确保获取到标签（新插入或原有）
        TagEntity.find { TagsTable.tagName eq tagPost.tagName }.first()
    }
}

方式2：存在时更新字段（可选）

如果需要在标签已存在时更新dateAdded等字段，可使用upsert：

private fun getOrCreateTag(tagPost: TagPost): TagEntity {
    TagsTable.upsert(where = { TagsTable.tagName eq tagPost.tagName }) {
        set(tagName, tagPost.tagName)
        set(dateAdded, tagPost.dateAdded)
    }
    return TagEntity.find { TagsTable.tagName eq tagPost.tagName }.first()
}

第三步：简化insertExpense函数 #

利用封装好的函数，简化整个流程，减少数据库交互次数：

fun insertExpense(expenses: ExpensesPost): Expenses {
    val userIdName = authenticationFacade.userId()
    val tagsPost = expenses.tag

    if (tagsPost.size > MAX_TAG_REQUEST) throw BadRequestException(
        Status.BAD_REQUEST,
        "Only $MAX_TAG_REQUEST tags are allowed"
    )

    return loggedTransaction {
        // 批量处理标签：获取或创建
        val tags = tagsPost.map { getOrCreateTag(it) }
        
        // 创建费用记录并关联标签
        val expense = ExpenseEntity.new {
            userId = userIdName
            concept = expenses.concept
            total = expenses.total
            dateAdded = expenses.dateAdded
            comments = expenses.comments
        }
        expense.tags = SizedCollection(tags)
        
        expense.toExpense()
    } ?: throw EntityNotFoundException(
        status = Status.NO_DATA,
        customMessage = "Something went wrong",
        id = userIdName
    )
}

额外优化：批量处理减少查询次数 #

如果一次性处理多个标签，可通过批量查询+批量插入进一步优化：

private fun getOrCreateTags(tagsPost: List<TagPost>): List<TagEntity> {
    val tagNames = tagsPost.map { it.tagName }
    // 批量查询已有标签，按名称分组
    val existingTags = TagEntity.find { TagsTable.tagName inList tagNames }.associateBy { it.tagName }
    
    // 筛选出需要新增的标签
    val newTags = tagsPost.filter { !existingTags.containsKey(it.tagName) }
    if (newTags.isNotEmpty()) {
        // 批量插入新标签
        TagsTable.batchInsert(newTags) { tag ->
            this[TagsTable.tagName] = tag.tagName
            this[TagsTable.dateAdded] = tag.dateAdded
        }
    }
    
    // 再次查询所有需要的标签
    return TagEntity.find { TagsTable.tagName inList tagNames }.toList()
}

在insertExpense中替换为调用这个批量函数即可。

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