Plain SQL Queries

高度な操作について、SQL文を直接書きたくなる事があるかもしれない。Slickの Plain SQL クエリでは、JDBCの低レイアに触れる事無しに、よりScalaベースな記述を行う事が出来る。

Scaffolding

SLick example jdbc/PlainSQLでは Plain SQL の特徴についていくつか説明している。インポートすべきパッケージが*lifted embedding*や*direct embedding*とは異なっている事に注意して欲しい。

import scala.slick.session.Database
import Database.threadLocalSession
import scala.slick.jdbc.{GetResult, StaticQuery => Q}

まず初めに、 Slick driver をインポートする必要がない。SlickのJDBCに基づくAPIはJDBC自身のみに依存しているし、データベース特有の抽象化を全く実装する必要がない。データベースに接続するために必要なものは、scala.slick.session.Databaseとセッション処理を単純化したthreeadLocalSessionのみである。

Plain SQL クエリを用いるために必要なクラスは、ここではQという名前でインポートしている、scala.slick.jdbc.StaticQueryである。

データベースの接続方法は*in the usual way*にある。例を示すために、以下のようなcase classを定義した。

case class Supplier(id: Int, name: String, street: String, city: String, state: String, zip: String)
case class Coffee(name: String, supID: Int, price: Double, sales: Int, total: Int)
...
Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver") withSession {
}

DDL/DML Statements

最もシンプルな StaticQuery のメソッドは、updateNA である（NA = no args）。updateNA は、結果の代わりにDDLステートメントから行数を返すStaticQuery[Unit, Int]を作成する、これは[lifted embedding][3]を用いるクエリと同じように実行する事が出来る。ここでは結果を得ずに、クエリを .execute を用いて実行させている。

// 主キーと外部キーを含むテーブルを作成する
Q.updateNA("create table suppliers("+
  "id int not null primary key, "+
  "name varchar not null, "+
  "street varchar not null, "+
  "city varchar not null, "+
  "state varchar not null, "+
  "zip varchar not null)").execute
.updateNA("create table coffees("+
  "name varchar not null, "+
  "sup_id int not null, "+
  "price double not null, "+
  "sales int not null, "+
  "total int not null, "+
  "foreign key(sup_id) references suppliers(id))").execute

String を既存の StaticQuery オブジェクトに対し、+ を用いて結合する事が出来る。この際、新しい StaticQuery が生成される。StaticQuery.u は、便利な関数であり、StaticQuery.updateNA("") で生成される空の update クエリを生成する。SUPPLIERS テーブルにいくつかのデータを挿入するためにStaticQuery.uを用いてみる。

// 複数のsupplierを挿入する
(Q.u + "insert into suppliers values(101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199')").execute
(Q.u + "insert into suppliers values(49, 'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', '95460')").execute
(Q.u + "insert into suppliers values(150, 'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', '93966')").execute

SQLコード内にリテラルを埋め込む事は、一般的にセキュリティやパフォーマンスの観点から推奨されない。特に、ユーザが提供したデータを実行時に用いるような際には危険な処理になる。変数をクエリ文字列に追加するためには、特別な連結オペレータである +? を用いる。これはSQL文が実行される際に、渡された値を用いてインスタンス化するものである。

def insert(c: Coffee) = (Q.u + "insert into coffees values (" +? c.name +
  "," +? c.supID + "," +? c.price + "," +? c.sales + "," +? c.total + ")").execute
...
// Insert some coffees
Seq(
  Coffee("Colombian", 101, 7.99, , ),
  Coffee("French_Roast", 49, 8.99, , ),
  Coffee("Espresso", 150, 9.99, , ),
  Coffee("Colombian_Decaf", 101, 8.99, , ),
  Coffee("French_Roast_Decaf", 49, 9.99, , )
).foreach(insert)

SQL文は全ての呼び出しで同じもの（insert into coffees values (?,?,?,?,?)）となっている。

Query Statements

updateNA と似た、返り値となる行の型パラメータを取る queryNA というメソッドがある。このメソッドは select を実行し、結果をiteratorで回す事が出来る。

Q.queryNA[Coffee]("select * from coffees") foreach { c =>
  println("  " + c.name + "t" + c.supID + "t" + c.price + "t" + c.sales + "t" + c.total)
}

これらを上手く機能させるためには、Slickは PositionedResult オブジェクトから Coffee の値をどのようにして読み取ればいいのかを知らせなくてはならない。これは暗黙的な GetResult によって行われる。GetResult を持つ基本的なJDBCの型や、NULLを許可するカラムを表すためのOptionや、タプルに対して、暗黙的な GetResult が定義されていなくてはならない。この例においては Supplier クラスや Coffee クラスのための GetResult を以下のように用意する必要がある。

// Result set getters
implicit val getSupplierResult = GetResult(r => Supplier(r.nextInt, r.nextString, r.nextString,
      r.nextString, r.nextString, r.nextString))
implicit val getCoffeeResult = GetResult(r => Coffee(r.<<, r.<<, r.<<, r.<<, r.<<))

GetResult[T] は PositionedResult => T となる関数のシンプルなラッパーである。上の例において、1つ目の GetResult では現在の行から次の Int 、次の String といった値を読み込む getInt 、getString といった PositionedResult の明示的なメソッドを用いている。2つ目の GetResult では自動的に型を推測する簡易化されたメソッド << を用いている。コンスタクタの呼び出しにおいて実際に型を判別出来る際にのみこれは用いる事ができる。

パラメータの無いクエリのための、queryNA メソッドは2つの型パラメータ（1つはクエリパラメータ、もう1つは返り値となる行の型パラメータ）を取るクエリによって補完される。同様に、updateNA のための適切な update が存在する。StaticQuery の実行関数は型パラメータを用いて呼ばれる必要がある。以下の例では .list がそれにあたる。

// 価格が$9.00より小さい全てのコーヒーに対し、coffeeのnameとsupplierのnameを取り出す
val q2 = Q.query[Double, (String, String)]("""
  select c.name, s.name
  from coffees c, suppliers s
  where c.price < ? and s.id = c.sup_id
""")
// この場合、結果はListとして読むことが出来る
val l2 = q2.list(9.0)
for (t <- l2) println("  " + t._1 + " supplied by " + t._2)

また、パラメータを直接的にクエリへ適用させる事も出来る。これを用いると、パラメータの無いクエリへと変換させることが出来る。これは通常の関数適用と同じように、クエリのパラメータを決めさせる事が出来る。

val supplierById = Q[Int, Supplier] + "select * from suppliers where id = ?"
println("Supplier #49: " + supplierById(49).first)

String Interpolation

SQL を発行する string interpolation 接頭辞である、sql や sqlu を用いるためには、以下のインポート文を追加する。

import Q.interpolation

再利用可能なクエリを必要としない場合には、interpolationはパラメータが付与されたクエリを生成する、最も簡単で統語的にナイスな手法である。クエリを挿入するどんな変数や式も、バインドした変数を結果を返すクエリ文字列へと変換する事が出来る（クエリへ直接挿入されるリテラル値を取得するのに $ の代わりに #$ を用いることも出来る）。返り値の型は呼び出しの中で、sql interpolatorによって作られたオブジェクトを StaticQuery へと変換させる .as によって指定される。

def coffeeByName(name: String) = sql"select * from coffees where name = $name".as[Coffee]
println("Coffee Colombian: " + coffeeByName("Colombian").firstOption)

update 文を生成するための interpolator に、sqlu というものもある。これは Int 値を返す事を強制するため、.as のような関数を必要としない。

def deleteCoffee(name: String) = sqlu"delete from coffees where name = $name".first
val rows = deleteCoffee("Colombian")
println(s"Deleted $rows rows")

Next Page ❧ Slick Extensions

slick-doc-ja 2.0

Plain SQL Queries

Scaffolding

DDL/DML Statements

Query Statements

String Interpolation

Contents