ScalikeJDBCを使ってMySQLにINSERTやSELECTをしたい

MySQLに対してScalikeJDBCを使ってINSERTやSELECTのクエリーを発行する方法。

build.sbt

// scalikejdbc
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc" % "2.1.2",
  "mysql" % "mysql-connector-java" % "5.1.29",
  "ch.qos.logback"    %   "logback-classic" % "1.1.1"
)

import org.scalatest._
import scalikejdbc._
import com.mysql.jdbc.Driver

case class User(id: Int, name: String, emailAddress: Option[String])

object User extends SQLSyntaxSupport[User] {
  override val tableName = "users"
  // for example 1
  def apply(rs: WrappedResultSet): User = User(rs.int("id"), rs.string("name"), rs.stringOpt("email_address"))
  // for example 2 (case classに無いカラム名を使うとコンパイル時に分かる)
  def apply(u: ResultName[User])(rs: WrappedResultSet): User = User(rs.int(u.id), rs.string(u.name), rs.stringOpt(u.emailAddress))
}

class SandboxSpec extends FunSuite {
  test("scalikejdbc") {
    Class.forName("com.mysql.jdbc.Driver")
    ConnectionPool.singleton("jdbc:mysql://192.168.0.77/testing?characterEncoding=UTF-8", "root", "mypass")

    implicit val session = AutoSession

    // insert example 1: 素のSQLに近い書き方
    val alice = User(0, "alice", None)
    val bob = User(0, "bob", Some("[e-mail]"))
    sql"INSERT INTO users (name, email_address) values (${alice.name}, ${alice.emailAddress})".update.apply()
    sql"INSERT INTO users (name, email_address) values (${bob.name}, ${bob.emailAddress})".update.apply()

    // insert example 2: DSLを使った書き方 (case classに無いカラム名を使うとコンパイル時に分かる)
    val uc = User.column
    withSQL { insert.into(User).namedValues(uc.name -> "carol", uc.emailAddress -> Some("[e-mail]")) }.update.apply()

    // select example 1: 素のSQLに近い書き方
    val users2 = sql"SELECT * FROM users".map(User(_)).list.apply()
    println(users2)

    // select example 2: DSLを使った書き方
    val u = User.syntax("u")
    val users = withSQL { select.from(User as u) }.map(User(u.resultName)).list.apply()
    println(users)

    // select example 3: whereで絞り込む
    val users3 = withSQL { select.from(User as u).where.eq(u.name, "bob") }.map(User(u.resultName)).list.apply()
    println(users3)
  }
}

withSQL { ... }.update.apply() は applyUpdate { ... } と書くこともできるみたい。

withSQL { insert.into(User).namedValues(uc.name -> "carol", uc.emailAddress -> Some("[e-mail]")) }.update.apply()

applyUpdate { insert.into(User).namedValues(uc.name -> "carol", uc.emailAddress -> Some("[e-mail]")) }