如何在嵌套case class中使用ScalikeJDBC的SQLSyntaxSupport

数据库表结构：

create table samples (
    id text,
    event_name text,
    event_data text,
    created_at timestamp,
    scope text
)

Scala类定义：

case class SampleData(id: String, eventName: String, eventData: String)
case class SampleMetadata(createdAt: OffsetDateTime, scope: String)
case class Sample(data: SampleData, metadata: SampleMetadata)

尝试实现的SQLSyntaxSupport代码：

object SampleSchema extends SQLSyntaxSupport[Sample] {
  override val tableName = "sources"

  override lazy val columns = Seq(
    "id",
    "event_name",
    "event_data",
    "created_at",
    "scope"
  )

  val insertColumns = Seq(
    column.id,
    column.eventName,
    column.eventData,
    column.createdAt,
    column.scope
  )
}

插入操作代码：

def insert(source: Sample): Unit = DB localTx { implicit session =>
    applyUpdate {
      insertInto(SampleSchema)
        .columns(SampleSchema.insertColumns: _*)
        .values(SampleSchema.toInsertValues(session, source): _*)
    }
}

编译报错信息：

Sample#eventName not found. Expected fields are #data, #metadata
 column.eventName 

报错原因是SQLSyntaxSupport默认只能识别Sample的直接字段（data、metadata），无法自动解析嵌套属性。以下是两种无需修改Sample结构的解决方案：

方法1：手动定义列映射+自定义插入值提取 #

直接手动指定数据库列的引用，同时自己实现嵌套字段的值提取逻辑：

import scalikejdbc._
import java.time.OffsetDateTime

case class SampleData(id: String, eventName: String, eventData: String)
case class SampleMetadata(createdAt: OffsetDateTime, scope: String)
case class Sample(data: SampleData, metadata: SampleMetadata)

object SampleSchema extends SQLSyntaxSupport[Sample] {
  // 修正表名与数据库表一致
  override val tableName = "samples"

  // 手动定义每个数据库列的引用
  val id = column("id")
  val eventName = column("event_name")
  val eventData = column("event_data")
  val createdAt = column("created_at")
  val scope = column("scope")

  val insertColumns = Seq(id, eventName, eventData, createdAt, scope)

  // 自定义提取嵌套字段的插入值
  def toInsertValues(sample: Sample): Seq[Any] = {
    Seq(
      sample.data.id,
      sample.data.eventName,
      sample.data.eventData,
      sample.metadata.createdAt,
      sample.metadata.scope
    )
  }
}

// 调整后的插入方法
def insert(sample: Sample): Unit = DB.localTx { implicit session =>
  applyUpdate {
    insertInto(SampleSchema)
      .columns(SampleSchema.insertColumns: _*)
      .values(SampleSchema.toInsertValues(sample): _*)
  }
}

方法2：额外实现结果集映射（支持查询场景） #

如果后续需要从数据库查询并映射回嵌套的Sample对象，可以补充实现结果集的映射逻辑：

object SampleSchema extends SQLSyntaxSupport[Sample] {
  override val tableName = "samples"

  val id = column("id")
  val eventName = column("event_name")
  val eventData = column("event_data")
  val createdAt = column("created_at")
  val scope = column("scope")

  val insertColumns = Seq(id, eventName, eventData, createdAt, scope)

  def toInsertValues(sample: Sample): Seq[Any] = {
    Seq(
      sample.data.id,
      sample.data.eventName,
      sample.data.eventData,
      sample.metadata.createdAt,
      sample.metadata.scope
    )
  }

  // 实现数据库结果集到嵌套Sample对象的映射
  override def apply(rs: WrappedResultSet, rn: ResultName[Sample]): Sample = {
    Sample(
      data = SampleData(
        id = rs.get(id),
        eventName = rs.get(eventName),
        eventData = rs.get(eventData)
      ),
      metadata = SampleMetadata(
        createdAt = rs.get(createdAt),
        scope = rs.get(scope)
      )
    )
  }
}

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