使用ScalikeJDBC嵌套对象

Question

我希望在ScalikeJDBC中有一个带有平面数据库表的嵌套对象。

Meta(id: String)

Poll(meta: Option[Meta], value: String)

使用到表的映射

Poll(id VARCHAR(100), value VARCHAR(100))

我不确定如何做到这一点，特别是使用SQLSyntaxSupport。

Answer 1

尝试以下代码。

另请参阅：- http://scalikejdbc.org/documentation/operations.html - http://scalikejdbc.org/documentation/sql-interpolation.html

build.sbt

scalaVersion := "2.12.4"
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"     % "3.2.1",
  "org.scalikejdbc" %% "scalikejdbc-syntax-support-macro" % "3.2.1",
  "com.h2database"  %  "h2"              % "1.4.196",
  "ch.qos.logback"  %  "logback-classic" % "1.2.3"
)

sample.scala

import scalikejdbc._

Class.forName("org.h2.Driver")
ConnectionPool.singleton("jdbc:h2:mem:hello", "user", "pass")

case class Meta(id: String)
case class Poll(meta: Option[Meta] = None, value: String)

implicit val metaBinders = Binders.of[Meta]
  // For TypeBinder (f: Any => A)
  {
    case null => null
    case str: String => Meta(str)
    case v: Any => Meta(v.toString)
  }
  // For ParameterBinderFactory (A => (PreparedStatement, Int) => Unit)
  { v =>
    (ps, idx) =>
    ps.setString(idx, v.id)
  }

object Poll extends SQLSyntaxSupport[Poll] {
  override val nameConverters = Map("^meta$" -> "id")
  def apply(rs: WrappedResultSet, rn: ResultName[Poll]) = autoConstruct(rs, rn)
}

implicit val session = AutoSession
sql"""
create table poll (
  id varchar(64),
  value varchar(64) not null
)
""".execute.apply()

sql"insert into poll (id, value) values ('meta-id-1', 'value-1')".update.apply()
sql"insert into poll (id, value) values (null, 'value-2')".update.apply()

val p = Poll.syntax("p")
val polls = withSQL {
  select.from(Poll as p)
}.map(rs => Poll(rs, p.resultName)).list.apply()

println(polls)

标准输出

22:10:49.828 [run-main-0] DEBUG scalikejdbc.ConnectionPool$ - Registered connection pool : ConnectionPool(url:jdbc:h2:mem:hello, user:user) using factory : <default>
22:10:49.832 [run-main-0] DEBUG scalikejdbc.ConnectionPool$ - Registered singleton connection pool : ConnectionPool(url:jdbc:h2:mem:hello, user:user)
22:10:50.123 [run-main-0] DEBUG scalikejdbc.StatementExecutor$$anon$1 - SQL execution completed

  [SQL Execution]
   create table poll ( id varchar(64), value varchar(64) not null ); (1 ms)

  [Stack Trace]
    ...
    Foo$.delayedEndpoint$Foo$1(foo.scala:34)
    Foo$delayedInit$body.apply(foo.scala:2)
    scala.Function0.apply$mcV$sp(Function0.scala:34)
    scala.Function0.apply$mcV$sp$(Function0.scala:34)
    scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
    scala.App.$anonfun$main$1$adapted(App.scala:76)
    scala.collection.immutable.List.foreach(List.scala:389)
    scala.App.main(App.scala:76)
    scala.App.main$(App.scala:74)
    Foo$.main(foo.scala:2)
    $line3.$read$$iw$$iw$.<init>(<console>:12)
    $line3.$read$$iw$$iw$.<clinit>(<console>)
    $line3.$eval$.$print$lzycompute(<console>:7)
    $line3.$eval$.$print(<console>:6)
    $line3.$eval.$print(<console>)
    ...

22:10:50.130 [run-main-0] DEBUG scalikejdbc.StatementExecutor$$anon$1 - SQL execution completed

  [SQL Execution]
   insert into poll (id, value) values ('meta-id-1', 'value-1'); (0 ms)

  [Stack Trace]
    ...
    Foo$.delayedEndpoint$Foo$1(foo.scala:36)
    Foo$delayedInit$body.apply(foo.scala:2)
    scala.Function0.apply$mcV$sp(Function0.scala:34)
    scala.Function0.apply$mcV$sp$(Function0.scala:34)
    scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
    scala.App.$anonfun$main$1$adapted(App.scala:76)
    scala.collection.immutable.List.foreach(List.scala:389)
    scala.App.main(App.scala:76)
    scala.App.main$(App.scala:74)
    Foo$.main(foo.scala:2)
    $line3.$read$$iw$$iw$.<init>(<console>:12)
    $line3.$read$$iw$$iw$.<clinit>(<console>)
    $line3.$eval$.$print$lzycompute(<console>:7)
    $line3.$eval$.$print(<console>:6)
    $line3.$eval.$print(<console>)
    ...

22:10:50.131 [run-main-0] DEBUG scalikejdbc.StatementExecutor$$anon$1 - SQL execution completed

  [SQL Execution]
   insert into poll (id, value) values (null, 'value-2'); (0 ms)

  [Stack Trace]
    ...
    Foo$.delayedEndpoint$Foo$1(foo.scala:37)
    Foo$delayedInit$body.apply(foo.scala:2)
    scala.Function0.apply$mcV$sp(Function0.scala:34)
    scala.Function0.apply$mcV$sp$(Function0.scala:34)
    scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
    scala.App.$anonfun$main$1$adapted(App.scala:76)
    scala.collection.immutable.List.foreach(List.scala:389)
    scala.App.main(App.scala:76)
    scala.App.main$(App.scala:74)
    Foo$.main(foo.scala:2)
    $line3.$read$$iw$$iw$.<init>(<console>:12)
    $line3.$read$$iw$$iw$.<clinit>(<console>)
    $line3.$eval$.$print$lzycompute(<console>:7)
    $line3.$eval$.$print(<console>:6)
    $line3.$eval.$print(<console>)
    ...

22:10:50.227 [run-main-0] DEBUG scalikejdbc.StatementExecutor$$anon$1 - SQL execution completed

  [SQL Execution]
   select p.id as i_on_p, p.value as v_on_p from poll p; (0 ms)

  [Stack Trace]
    ...
    Foo$.delayedEndpoint$Foo$1(foo.scala:42)
    Foo$delayedInit$body.apply(foo.scala:2)
    scala.Function0.apply$mcV$sp(Function0.scala:34)
    scala.Function0.apply$mcV$sp$(Function0.scala:34)
    scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
    scala.App.$anonfun$main$1$adapted(App.scala:76)
    scala.collection.immutable.List.foreach(List.scala:389)
    scala.App.main(App.scala:76)
    scala.App.main$(App.scala:74)
    Foo$.main(foo.scala:2)
    $line3.$read$$iw$$iw$.<init>(<console>:12)
    $line3.$read$$iw$$iw$.<clinit>(<console>)
    $line3.$eval$.$print$lzycompute(<console>:7)
    $line3.$eval$.$print(<console>:6)
    $line3.$eval.$print(<console>)
    ...

List(Poll(Some(Meta(meta-id-1)),value-1), Poll(None,value-2))

Answer 2

对于额外的问题，您可以手动绑定多个值，如下所示：

object Poll extends SQLSyntaxSupport[Poll] {
  override val columns = Seq("id" "creation_time", "value")
  def apply(rs: WrappedResultSet, rn: ResultName[Poll]) = {
    new Poll(
      meta = new Meta(
        id = rs.get[Long](rn.id), 
        creationTime = rs.get[ZonedDateTime](rn.creationTime)
      ),
      value = rs.get[String](rn.value)
    )
  }
}