如何用jOOQ的multiset或row替代jsonObject实现类型安全查询

问题背景 #

我之前基于JSON函数实现查询语句复用，运行正常，但无法提供类型安全性。现在希望切换为multiset处理嵌套集合、row处理单个嵌套对象的模式，彻底弃用jsonObject。

现有实现代码 #

数据类定义

data class FoodRecord(
  val id: UUID,
  val createdAt: Instant,
  val createdBy: UserGeneralRecord,
  val lastModifiedAt: Instant,
  val lastModifiedBy: UserGeneralRecord,
  val name: String,
  val synonyms: List<String>
)

data class UserGeneralRecord(
  val id: UUID,
  val nickname: String,
  val role: UserRole
) {
  companion object {
    private val user = JUser.USER

    val select = jsonObject(
      key("id").value(user.ID),
      key("nickname").value(user.NICKNAME),
      key("role").value(user.ROLE)
    )
  }
}

查询实现

private val food: JFood = JFood.FOOD
private val foodSynonym: JFoodSynonym = JFoodSynonym.FOOD_SYNONYM
private val user: JUser = JUser.USER

override fun fetch(query: FetchFoodByIdQuery): FoodRecord {
  return ctx.getContext()
    .select(
      jsonObject(
        key("id").value(food.ID),
        key("createdAt").value(food.CREATED_AT),
        key("createdBy").value(
          field(
            select(UserGeneralRecord.select)
              .from(user)
              .where(user.ID.eq(food.CREATED_BY))
          )
        ),
        key("lastModifiedAt").value(food.LAST_MODIFIED_AT),
        key("lastModifiedBy").value(
          field(
            select(UserGeneralRecord.select)
              .from(user)
              .where(user.ID.eq(food.LAST_MODIFIED_BY))
          )
        ),
        key("name").value(food.NAME),
        key("synonyms").value(
          field(
            select(jsonArrayAgg(foodSynonym.SYNONYM))
              .from(foodSynonym)
              .where(foodSynonym.FOOD_ID.eq(food.ID))
          )
        ),
      )
    )
    .from(food)
    .where(food.ID.eq(query.id))
    .fetchOneInto(FoodRecord::class.java)
    ?: throw NoResultFoundException("Food is not found by id: ${query.id}")
}

期望的实现思路（无法运行的示例） #

override fun fetch(query: FetchFoodByIdQuery): FoodRecord {
  return ctx.getContext()
    .select(
      food.ID,
      food.CREATED_AT,
      row(
        select( // 该查询逻辑需复用（与lastModifiedBy一致）
          user.ID,
          user.NICKNAME,
          user.ROLE
        ).from(user)
          .where(user.ID.eq(food.CREATED_BY)) 
      ).`as`("createdBy"),
      food.LAST_MODIFIED_AT,
      row(
        select( // 该查询逻辑需复用（与createdBy一致）
          user.ID,
          user.NICKNAME,
          user.ROLE
        ).from(user)
          .where(user.ID.eq(food.LAST_MODIFIED_BY)) 
      ).`as`("lastModifiedBy"),
      multiset(
        select(foodSynonym.SYNONYM).from(foodSynonym)
          .where(foodSynonym.FOOD_ID.eq(food.ID))
      ).`as`("synonyms")
    )
    .from(food)
    .where(food.ID.eq(query.id))
    .fetchOneInto(FoodRecord::class.java)
    ?: throw NoResultFoundException("Food is not found by id: ${query.id}")
}

正确的类型安全实现方案 #

1. 重构UserGeneralRecord的复用逻辑

将原有的JSON对象查询改为返回类型明确的子查询，确保复用逻辑的类型安全：

data class UserGeneralRecord(
  val id: UUID,
  val nickname: String,
  val role: UserRole
) {
  companion object {
    private val user = JUser.USER

    // 定义可复用的用户查询，返回类型明确的Record
    fun selectById(userId: Field<UUID>): Select<Record3<UUID, String, UserRole>> {
      return select(user.ID, user.NICKNAME, user.ROLE)
        .from(user)
        .where(user.ID.eq(userId))
    }
  }
}

2. 实现类型安全的Food查询

使用rowSubquery处理单个嵌套对象，multiset处理嵌套集合，同时复用用户查询逻辑：

private val food: JFood = JFood.FOOD
private val foodSynonym: JFoodSynonym = JFoodSynonym.FOOD_SYNONYM
private val user: JUser = JUser.USER

override fun fetch(query: FetchFoodByIdQuery): FoodRecord {
  return ctx.getContext()
    .select(
      food.ID,
      food.CREATED_AT,
      // 通过rowSubquery将子查询转为Row类型，映射到UserGeneralRecord
      rowSubquery(UserGeneralRecord.selectById(food.CREATED_BY)).`as`("createdBy"),
      food.LAST_MODIFIED_AT,
      rowSubquery(UserGeneralRecord.selectById(food.LAST_MODIFIED_BY)).`as`("lastModifiedBy"),
      food.NAME,
      // multiset直接返回List<String>，无需JSON解析，类型安全
      multiset(
        select(foodSynonym.SYNONYM)
          .from(foodSynonym)
          .where(foodSynonym.FOOD_ID.eq(food.ID))
      ).`as`("synonyms")
    )
    .from(food)
    .where(food.ID.eq(query.id))
    // jOOQ自动映射嵌套类型到data class
    .fetchOneInto(FoodRecord::class.java)
    ?: throw NoResultFoundException("Food is not found by id: ${query.id}")
}

关键优势说明 #

• 类型安全：rowSubquery返回明确的Row结构，multiset返回泛型列表，jOOQ在映射到data class时会严格校验类型，避免JSON序列化带来的类型丢失风险。
• 逻辑复用：通过抽离selectById函数，createdBy和lastModifiedBy的查询逻辑完全复用，消除代码冗余。
• 性能优化：无需JSON序列化/反序列化步骤，查询结果直接映射为对象，性能更优。

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