ScalaのORM「Slick」でいろんなSQLを投げてみる

概要

SlickはScalaのCollection操作っぽい記述でDBにSQLを投げることができる。

どう書くとどういったSQLが投げられるのかは普通に使ってるだけだとイマイチわからないので。よく使いそうな記述とそれによって生成されるSQLについてまとめてみる。

SQLは改行を入れたりエスケープ文字を消したりして、多少見やすくしてます。SLick1.0.1を使ってますが、ざっと見た感じでは2.0.0-RC1でもほとんど変わらない。

@CretedDate 2014/01/11
@Env Scala2.10.2
@Env 1.0.1

クエリの出力方法

実行クエリはH2に対して TRACE_LEVEL_SYSTEM_OUT=2 を指定することで、H2が受け取ったクエリを標準出力させている。DBによって多少の出力クエリの違いはあると思われる。

import scala.slick.driver.H2Driver.simple._
Database.forURL(
  "jdbc:h2:mem:test1;TRACE_LEVEL_SYSTEM_OUT=2", 
  driver = "org.h2.Driver") withSession { implicit session: Session =>
}

利用するテーブルの作成

ID, 名前, 値段, サイズ, 砂糖の有無が設定できる coffees というテーブルを作成し、これに対してクエリを投げることにする。

import scala.slick.driver.H2Driver.simple._

object Coffees extends Table[Coffee]("coffees") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name", O.NotNull)
  def price = column[Int]("price", O.NotNull)
  def size = column[String]("size", O.NotNull)
  def sugar = column[Boolean]("sugar", O.Default(true))

  def * = id ~ name ~ price ~ size ~ sugar <> (Coffee, Coffee.unapply _)
}

object Main extends App {
  Database.forURL(
      "jdbc:h2:mem:test1;TRACE_LEVEL_SYSTEM_OUT=2", 
      driver = "org.h2.Driver") withSession { implicit session: Session =>

    // create table
    Coffees.ddl.create
}

17行目で、ddl.create してるので、これを実行すると、下記のようなクエリが実行されます。

create table "coffees"
(
  "id" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
  "name" VARCHAR NOT NULL,
  "price" INTEGER NOT NULL,
  "size" VARCHAR NOT NULL,
  "sugar" BOOLEAN DEFAULT true NOT NULL
);

SELECT * FROM coffees

普通に全部検索してみる。

import scala.slick.driver.H2Driver.simple._
Database.forURL(url, driver = "org.h2.Driver") withSession { implicit session: Session =>
  Query(Coffees).list
}

list とか head とか headOptionは implicit な Sessionを引数に取る。ので、withSession のところで implicit Session をもらうようにしている。

で、この子を実行すると下記のようなクエリが実行される。

select x2."id", x2."name", x2."price", x2."size", x2."sugar" from "coffees" x2;

一部のカラムだけ取得

ORMだとあまり使うことはないけど、パフォーマンス考えてid と nameだけ取ろうとかした場合は下記のように書ける。

// mapを利用した場合
Query(Coffees).map(row => (row.id, row.name)).list

// forを利用した場合
(for (row <- Query(Coffees)) yield (row.id, row.name)).list

mapを使った場合と、forを使った場合、2通りの書きかたがある。

id(Int) と name(String) が取得値として設定されているので、戻り値の型は List[(Int, String)]になる。

これを実行すると下記のようなクエリになる。

select x2."id", x2."name" from "coffees" x2;

WHERE句を使う

id が 1 のレコードだけ取得する。=== でも is でも意味は同じ。

// mapを利用した場合
Query(Coffees)
  .filter(_.id === 1)
  .map(row => (row.id, row.name)).firstOption

// forを利用した場合
(for (
  row <- Query(Coffees);
  if row.id is 1
) yield (row.id, row.name)).firstOption

出力されたSQL。

select x2."id", x2."name" from "coffees" x2 where x2."id" = 1;

NOT条件をつける

isNot や =!= でNOT条件が付く。

// filterを使った場合
Query(Coffees)
  .filter(_.id isNot 1)
  .map(row => (row.id, row.name)).firstOption

// forを利用した場合
(for (
  row <- Query(Coffees);
  if row.id =!= 1
) yield (row.id, row.name)).firstOption

結果。NOT( 条件 ) という形になるらしい。

select x2."id", x2."name" from "coffees" x2 where not (x2."id" = 1);

AND条件とLIKE

ANDで複数の条件を重ねてみる。あと、ついでにLIKEも使ってみる。priceが500円以下で、nameがiceで始まるレコードを取得する場合。

// filterの場合
Query(Coffees)
  .filter(row => (row.price < 500) && (row.name like "ice%"))
  .map(row => (row.id, row.name)).list

// forの場合
(for (
  row <- Query(Coffees);
  if row.price < 500;
  if row.name like "ice%"
) yield (row.id, row.name)).list

forの方の条件は && にしても良いけど、なんとかくif ifと重ねている。filterの方も同じように、filter(条件1).filter(条件2)と繋げても問題ない。

実行すると、ちゃんとANDとLIKEが出力される。

select x2."id", x2."name"
from "coffees" x2
where (x2."price" < 500) and (x2."name" like 'ice%');

OR条件とINとUPPER CASE

さっきはANDだったので、ORも試してみる。ついでにINとかUPPER CASEとかもしてみる。

IN は inSetBindを使えば良いらしい。UPPER CASEは普通にtoUpperCase

// filterの場合
Query(Coffees)
  .filter(row => (
    (row.id inSetBind List(1, 2)) || (row.name is "ice coffee"))
    && (row.size is "L"))
  .map(row => (row.id, row.name.toUpperCase)).list

// forの場合
(for (
  row <- Query(Coffees);
  if (row.id inSetBind List(1, 2)) || (row.name is "ice coffee");
  if row.size is "L"
) yield (row.id, row.name.toUpperCase)).list

実行結果。INの中身はprepared statementでbindされる形になっている。

select x2."id", ucase(x2."name")
from "coffees" x2
where ((x2."id" in (?, ?)) or (x2."name" = 'ice coffee')) and (x2."size" = 'L')
{1: 1, 2: 2};

BETWEEN

BETWEEN で idが 100〜200のレコードを取ってみる。

Query(Coffees).filter( row => row.id.between(100, 200) ).list

select x2."id", x2."name", x2."price", x2."size", x2."sugar"
from "coffees" x2
where x2."id" between 100 and 200;

COUNT

とりあえずテーブルの全レコード数を取る。この辺の情報はStackOverFlowに書いてあるのをそのまま打つとダメなケースが多かった。

// mapの場合
Query(Coffees).map(_.id.count).first

// forの場合
(for(row <- Query(Coffees)) yield row.id.count).first

firstで取ってるので、これの結果は Int 型になります。

出力されたクエリ。

select count(x2."id") from "coffees" x2;

これに普通にWHEREを重ねることもできる。

// mapの場合
Query(Coffees).filter(_.price < 500).map(_.id.count).first

// forの場合
(for (row <- Query(Coffees) if row.price < 500) yield row.id.count).first

select count(x2."id") from "coffees" x2 where x2."price" < 500;

GROUP BY

size で group by して、price の MAX を取ってみる。

Query(Coffees)
  .groupBy(_.size)
  .map { case (size, c) => size -> c.map(_.price).max }.list

Scalaの普通のgroupByのコードと変わらない。

select x2."size", max(x2."price")
from "coffees" x2
group by x2."size";

ここでは count を使っているけど、 sum とか avg とかも普通に使える。

groupBy の前に filter を入れてみる。

Query(Coffees)
  .filter(_.name like "ice%")
  .groupBy(_.size)
  .map { case (size, c) => size -> c.map(_.price).max }.list

普通にできた。

select x2."size", max(x2."price")
from "coffees" x2
where x2."name" like 'ice%'
group by x2."size";

今度は groupBy の後に filter を付けてみる。

Query(Coffees)
  .groupBy(_.size)
  .map { case (size, c) => size -> c.map(_.price).max }
  .filter { case (size, max) => max > 500 }.list

SELECTしてからWHEREするようになった。

select x2.x3, x2.x4
from (
  select x5."size" as x3, max(x5."price") as x4
  from "coffees" x5
  group by x5."size"
) x2
where x2.x4 > 500;

ORDER BY

ORDER BYしてみる。とりあえず price の降順で。

Query(Coffees).sortBy(row => row.price.desc)

結果

select x2."id", x2."name", x2."price", x2."size", x2."sugar"
from "coffees" x2
order by x2."price" desc;

複数の条件で ORDER BY する。下記は price と id でソートする例。

Query(Coffees).sortBy(row => row.price ~ row.id).list

結果

select x2."id", x2."name", x2."price", x2."size", x2."sugar"
from "coffees" x2
order by x2."price", x2."id";

複数条件で DESC を混ぜる場合は、~ で連結しようとするとコンパイルエラーになった。

ということで、こんな感じで。

Query(Coffees).sortBy(row => (row.price.desc, row.id)).list

select x2."id", x2."name", x2."price", x2."size", x2."sugar"
from "coffees" x2
order by x2."price" desc, x2."id";

limitでページングする

10レコード目から20レコード目までを取ってみる。

val page = 1
Query(Coffees).drop(page * 10).take(10).list

何やら無駄に二段階になった。

select x2.x3, x2.x4, x2.x5, x2.x6, x2.x7
  from (
    select x8."id" as x3, x8."name" as x4, x8."price" as x5, x8."size" as x6, x8."sugar" as x7
    from "coffees" x8 limit 10 offset 10
  ) x2;

DELETE

全件削除

Query(Coffees).delete

delete from "coffees"

条件付き削除

Query(Coffees).filter( _.id === 1 ).delete

delete from "coffees" where "coffees"."id" = 1;

UPDATE

指定IDの1件を更新

Query(Coffees).filter(_.id === 1).update(Coffee(1, "java", 380, "L", false))

WHERE id = 1 しつつ、指定の値で UPDATE している。

update "coffees"
set "id" = ?, "name" = ?, "price" = ?, "size" = ?, "sugar" = ?
where "coffees"."id" = 1
{1: 1, 2: 'java', 3: 380, 4: 'L', 5: FALSE};

次に price が500円より高いレコードの sugar を全部 false に UPDATE してみる。

Query(Coffees).filter(_.price > 500).map(_.sugar).update(false)

WHERE句で条件を付けつつ、mapで指定したsugarだけを

update "coffees"
set "sugar" = ?
where "coffees"."price" > 500 {1: FALSE};

INSERT

DELETE、UPDATEと書いたし、一応INSERTも書いておこう。

Coffees.insert(Coffee(1, "java", 380, "L", false))

INSERT INTO "coffees"
("id","name","price","size","suger")
VALUES
(?,?,?,?,?)
{1: 1, 2: 'java', 3: 380, 4: 'L', 5: FALSE};

INNER JOIN

テーブル新しく作るのも面倒なので、ここまで使ってきた coffees テーブル同士を結合してみる。

あまり意味のあるクエリではないけど、イメージとしてこんな感じで。

(for (
  c1 <- Query(Coffees) if (c1.size === "L");
  c2 <- Query(Coffees) if (c2.price < 500) && (c1.id is c2.id)
) yield (c1.id, c2.name)).list

select x2."id", x3."name"
from "coffees" x2, "coffees" x3
where
  (x2."size" = 'L')
  and ((x3."price" < 500)
  and (x2."id" = x3."id"));

INNER JOINメソッドで繋げてみる。取得するカラムは、1個目のテーブルの id と name、2個目テーブルの size にする。

(for ((c1, c2) <- Coffees innerJoin Coffees on (_.id === _.id)) yield {
  c1.id ~ c1.name ~ c2.size
}).list

こう記号に変換されると何が何やら。

select x2.x3, x2.x4, x5.x6 from (
  select x7."id" as x3, x7."name" as x4
  from "coffees" x7
) x2
inner join
(
  select x8."id" as x9, x8."size" as x6
  from "coffees" x8
) x5
on x2.x3 = x5.x9;

LEFT JOIN

LEFT JOIN は INNER JOIN と書き方は変わらない。なんとなく後ろに filter を付けてみる。

(for ((c1, c2) <- Coffees leftJoin Coffees on (_.id === _.id)) yield (
  c1.id ~ c1.name ~ c2.size))
  .filter(_._3 === "L").list

select x2.x3, x2.x4, x5.x6 from (
  select x7.\&quot;id\&quot; as x3, x7.\&quot;name\&quot; as x4
  from \&quot;coffees\&quot; x7
) x2
left outer join (
  select x8.\&quot;id\&quot; as x9, x8.\&quot;size\&quot; as x6
  from \&quot;coffees\&quot; x8
) x5
on x2.x3 = x5.x9
where x5.x6 = 'L';

MERGE

軽くググった限りではMERGEはSQLとしては投げられないっぽい。なので普通にSELECTしてINSERTかUPDATEする。

val data = Coffee(1, "java", 380, "L", false)

val q = Query(Coffees).filter(_.id is data.id)
q.firstOption match {
  case None => Coffees.insert(data)
  case Some(_) => q.update(data)
}

まとめ

日常生活で使いそうなクエリはだいたい抑えられたように思う。

もっと複雑なクエリも割と書けたりするけど、あまりそうしたことに凝り過ぎるとろくなことがないので、「これをORMで書くのは難しいだろうな」と思うようなことに出くわした時は、無理せず StaticQuery で SQL を直接投げるという方針で。