Slick 入門 - KZKY memo

現時点で，2.1が2系最新だが3系が出た．
ここでのまとめは，2.1.0のドキュメントを参考にしている．

基本

Collection-likeにqueryを扱える．普通のSQLも扱える．

サポートしてるDB

詳しくはここDriver Capabilities

DB2 (via slick-extensions)
Derby/JavaDB
H2
HSQLDB/HyperSQL
Microsoft Access
Microsoft SQL Server (via slick-extensions)
MySQL
Oracle (via slick-extensions)
PostgreSQL
SQLite

NoSQLは対応中とのこと

開発環境

Eclipse Luna
Scala IDE 4.0
Scala 2.11
Gradle

build.gradle

dependencies {
    ....
    compile group: 'com.typesafe.slick', name: 'slick_2.11', version: '2.1.0'
    compile group: 'org.slf4j', name: 'slf4j-nop', version: '1.6.4'
    compile group: 'mysql', name: 'mysql-connector-java', version: '5.1.34'
    ....
}

基本設計

ベストプラクティスではないがtypesafeのサンプルから考えられる設計方針

Tableファイル

1オブジェクト

クラスと名前をそろえる
カラムのtypedefをする
コンストラクタ内でカラムをtypeを使ってタプルとして定義

1クラス

オブジェクトと名前を揃える
extends Tableに定義したtypeをパラメータとして渡す
extends Tableにテーブル名(文字列)をコンストラクタ引数として渡す

Getting Started

ここの例をやってみる

DBはmysql

まずはテーブル作成

addresses

id: int primary key
street: string
city: string

people

id: int primary key
name: string
age: int
address_id: int foreign key

テーブル作成，レコード挿入に，今回ははSlickを使わない．

テーブル作成SQL

# create db and use that
create database slick_sample
use database slick_sample

# drop tables
drop table if exists People;
drop table if exists Addresses;

# create tables
CREATE TABLE slick_sample.Addresses
(
id         int not null primary key auto_increment,
street     varchar(256),
city       varchar(256)
);

CREATE TABLE slick_sample.People
(
id           int not null primary key auto_increment, 
name         varchar(256),
age          int,
address_id   int, 
foreign key (address_id) references Addresses(id)
);

# insert into addresses
insert into Addresses (street, city) values ("Delancey Street", "New York");
insert into Addresses (street, city) values ("Henry Street", "New York");

# insert into people
insert into People (name, age, address_id) values ("Bob", 35, 1);
insert into People (name, age, address_id) values ("Meary", 36, 1);
insert into People (name, age, address_id) values ("Kevin", 36, 2);

Addresses.scala

package edu.kzk.slick.schema

import scala.slick.driver.MySQLDriver.simple._

object Addresses {
  type Address = (Int, String, String)
}

class Addresses(tag: Tag) extends Table[Addresses.Address](tag, "Addresses") {
  def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
  def street = column[String]("STREET")
  def city = column[String]("CITY")
  def * = (id, street, city)
}

People.scala

package edu.kzk.slick.schema

import scala.slick.driver.MySQLDriver.simple._

object People {
  type Person = (Int, String, Int, Int)
}

class People(tag: Tag)(implicit addresses: TableQuery[Addresses]) extends Table[People.Person](tag, "People") {
  def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("NAME")
  def age = column[Int]("AGE")
  def addressId = column[Int]("ADDRESS_ID")
  def * = (id, name, age, addressId)

  def address = foreignKey("ADDRESS", addressId, addresses)(_.id)
}

HelloSample.scala

package edu.kzk.slick

import scala.slick.driver.MySQLDriver.simple._
import scala.slick.lifted.TableQuery
import edu.kzk.slick.schema.Addresses
import edu.kzk.slick.schema.People

object HelloSample extends App {

  val scheme = "jdbc:mysql"
  val host = "localhost"
  val port = "3306"
  val db = "slick_sample"
  val url = s"${scheme}://${host}:${port}/${db}"
  val driver = "com.mysql.jdbc.Driver"
  val user = "mysql"
  val password = "hoge"

  Database.forURL(url, driver = driver,
    user = user,
    password = password) withSession {

      implicit session =>

        println("Read sample")
        implicit lazy val addresses = TableQuery[Addresses];
        addresses.foreach {
          case (id, street, city) =>
            println(s"id=${id}, street=${street}, city=${city}")
        }

        println("Join sample")
        val people = TableQuery[People];
        val joinQuery = for {
          p <- people
          a <- p.address
        } yield (p.name, p.age, a.street, a.city)
        joinQuery.foreach {
          case (name, age, street, city) =>
            println(s"name=${name}, age=${age}, street=${street}, city=${city}")
        }

    }
}

テーブル間に依存関係があるので，ここでのポイントは

class People(tag: Tag)(implicit addresses: TableQuery[Addresses]) extends Table[People.Person](tag, "People") {
...
}

のimplicit．

ORM to Slick

ここの例をやってみる

ORMSample.scala

package edu.kzk.slick

import scala.slick.driver.MySQLDriver.simple._
import scala.slick.lifted.TableQuery

import edu.kzk.slick.schema.Addresses
import edu.kzk.slick.schema.People

object ORMSample extends App {

  val scheme = "jdbc:mysql"
  val host = "localhost"
  val port = "3306"
  val db = "slick_sample"
  val url = s"${scheme}://${host}:${port}/${db}"
  val driver = "com.mysql.jdbc.Driver"
  val user = "mysql"
  val password = "hoge"

  Database.forURL(url, driver = driver,
    user = user,
    password = password) withSession {

      implicit session =>

        implicit lazy val addresses = TableQuery[Addresses]
        val people = TableQuery[People];
        val peopleQuery: Query[People, People.Person, Seq] =
          people.filter { p => p.id === 2 }

        val peopleQueryResults = peopleQuery.run
        peopleQueryResults.foreach { p => println(s"name=${p._2}, age=${p._3}") }

        val addressesQuery: Query[Addresses, Addresses.Address, Seq] =
          peopleQuery.flatMap(_.address)
        val addressesQueryResults = addressesQuery.run
        addressesQueryResults.foreach {
          case (id, city, street) => s"id=${id}, city=${city}, street=${city}"
        }

    }
}

行の特定のカラムのみほしいときはmapを使う

people.map(p => (p.name, p.age))

SQL to Slick

ここの例をやってみる

SQLSample.scala

package edu.kzk.slick

import scala.slick.driver.MySQLDriver.simple._
import scala.slick.jdbc.StaticQuery.interpolation
import scala.slick.jdbc.GetResult
import scala.slick.lifted.TableQuery

import edu.kzk.slick.schema.Addresses
import edu.kzk.slick.schema.People

object SQLSample extends App {
  val scheme = "jdbc:mysql"
  val host = "localhost"
  val port = "3306"
  val dbName = "slick_sample"
  val url = s"${scheme}://${host}:${port}/${dbName}"
  val driver = "com.mysql.jdbc.Driver"
  val user = "mysql"
  val password = "hoge"

  val db = Database.forURL(url, driver = driver,
    user = user,
    password = password)
  val query = sql"select id, name, age from People".as[(Int, String, Int)]

  val people = db.withSession {
    implicit session =>
      query.list.foreach(p =>
        println(s"id=${p._1}, name=${p._2}, age=${p._3}")
      )
  }

}

Query結果のlistの要素がタプルなので，あるColumnへのアクセスが _i なのがイケてない．

Connection

ここのTarnsactionの例はまとめるのみ

コネクションpoolの仕組みは用意されてない
JEE/Springのコネクションpoolを使用する
sessionはwithSessionのスコープのみ有効なのでsessionを返して使い回さない！
使いたかったらdb.createSessionするが，session lifecycleは自分で管理する

val query = for (c <- coffees) yield c.name
val session : Session = db.createSession
val result  = query.list(session)
session.close

Transaction

db.withSession {
    implicit session => {
        session.withTransaction {
        // your queries go here
       
        if (/* some failure */ false){
          session.rollback // signals Slick to rollback later
        }
       
      } // <- rollback happens here, if an exception was thrown or session.rollback was called
    }
}

か

db.withTransaction{
  implicit session =>
  // your queries go here
}

helperを作ってクエリを扱いたい場合，sessionが必要なのでimplicit sesssionする

class Helpers(implicit session: Session){
  def execute[T, C[_]](query: Query[T,_, C]) = query.list
  // ... place further helpers methods here
}
val query = for (c <- coffees) yield c.name
db.withSession {
  implicit session =>
      val helpers = (new Helpers)
      import helpers._
      execute(query)
}
// (new Helpers).execute(query) // <- Would not compile here (no implicit session)