Plain SQLクエリ

もしかすると、高レベルに抽象化されてサポートされたオペレーションに対し、SQLコードをそのまま書きたいといった要求があるかもしれない。そのような場合には、低レベルなJDBCのAPIを用いるのではなく、Slickが提供するScalaベースの Plain SQL を利用して欲しい。

Note
本章の残りでは、Slick Plain SQL Queries templateをベースに説明を行う。Activatorからテンプレートを落としてきて、直接編集したり実行しながら読んでみて欲しい。

Scaffolding

データベースのコネクションは、いつもと同じように開かれる。全ての Plain SQL DBIOActions内で実行される。これは複数のアクションを組み合わせたものする事も可能である。

String Interpolation

Slickの Plain SQL はsql、sqlu、tsqlという文字列の補間（string interpolation）を通して組み立てることが出来る。これらはSlickドライバからapi._をインポートする事で利用可能となる。

import slick.driver.H2Driver.api._

最も簡単な使用法としては、以下のようなメソッドの中で利用しているように、sqluの中にSQLコードをそのまま書いてしまうことだ。

def createCoffees: DBIO[Int] =
  sqlu"""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))"""
...
def createSuppliers: DBIO[Int] =
  sqlu"""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)"""
...
def insertSuppliers: DBIO[Unit] = DBIO.seq(
  // Insert some suppliers
  sqlu"insert into suppliers values(101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199')",
  sqlu"insert into suppliers values(49, 'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', '95460')",
  sqlu"insert into suppliers values(150, 'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', '93966')"
)

sqlu補間子は、結果の代わりに列の数を返すDMLステートメントとして用いられる。それゆえ、sqluを用いた場合は返り値の型がDBIO[Int]となる。

クエリに注入される変数や表現は、クエリ文字列の中でバインド変数などで表される。クエリ文字列に直接変数を入れることはしない。このような対応は、SQLインジェクションをなくすためにある。以下の例を見て欲しい。

def insert(c: Coffee): DBIO[Int] =
  sqlu"insert into coffees values (${c.name}, ${c.supID}, ${c.price}, ${c.sales}, ${c.total})"

このメソッドにより生成されるSQLステートメントは、常に同じものになる。

insert into coffees values (?, ?, ?, ?, ?)

この種のコードに役立つ便利なDBIO.sequenceコンビネータは以下のように利用できる。

val inserts: Seq[DBIO[Int]] = Seq(
  Coffee("Colombian", 101, 7.99, 0, 0),
  Coffee("French_Roast", 49, 8.99, 0, 0),
  Coffee("Espresso", 150, 9.99, 0, 0),
  Coffee("Colombian_Decaf", 101, 8.99, 0, 0),
  Coffee("French_Roast_Decaf", 49, 9.99, 0, 0)
).map(insert)
...
val combined: DBIO[Seq[Int]] = DBIO.sequence(inserts)
combined.map(_.sum)

与えられた順序でデータベースのI/Oアクションを直列に実行するシンプルなDBIO.seqとは異なり、DBIO.sequenceは個々のアクションの結果を保護するために、Seq[DBIO[T]]をDBIO[Seq[T]]へ変換する。これは挿入時に影響のあった列の数を数え上げる際などに用いられている。

Result Sets

以下のコードでは、ステートメントにより得られた結果を返却するsql補間子を利用している。sql補間子自身はDBIOの値を生成したりはしない。これは、.asというメソッドを返り値となる型を組み合わせて呼び出す必要がある。

sql"""select c.name, s.name
      from coffees c, suppliers s
      where c.price < $price and s.id = c.sup_id""".as[(String, String)]

この結果の型は、DBIO[Seq[(String, String)]]となる。asを呼び出す際には、結果から要求する型の値を抽出するGetResultパラメータを暗黙的に必要としている。基本的なJDBCの型やOption、タプルなどに対するGetResultは予め定義されている。それ以外の型に対するGetResultは、各自で定義して欲しい。

// 適当なケースクラス
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)
...
// 結果を抽出するためにGetResult
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という関数の単なるラッパーにすぎない。Supplierのための暗黙的なGetResultは、列からIntかStringの値を読み出すために、明示的なPositionedResultを用いている。2個めのCoffeeの例では、期待する型を自動的に導出しようと試みる<<というショートカットメソッドを利用している（コンストラクタの呼び出しに対して明らかに型が導出出来る場合にのみ利用可能）。

Splicing Literal Values

パラメータはSQLステートメントに対してバインド変数を用いて挿入されるわけだが、動的に生成されたSQLコードを呼び出す際などでは、もしかすると直接ステートメントの中にリテラルを書く必要が生じるかもしれない。このような場合には以下の例のように、全ての補間子の中で$の代わりに#$を用いて変数をバインドしてあげれば良い。

val table = "coffees"
sql"select * from #$table where name = $name".as[Coffee].headOption

Type-Checked SQL Statements

今まで見てきた補間子は、SQLステートメントを実行時に構築する。これはステートメントを構築する安全で簡単な方法となっている一方、単なる埋め込み文字列にしかならない。もしステートメントにシンタックスエラーがあったり、データベースとScalaのコードに何かしら型の違いがあったする場合にも、コンパイル時に検出が出来なく、非常に残念である。そのような場合には、sql補間子の代わりにtsql補間子を使う事を検討してみて欲しい。

def getSuppliers(id: Int): DBIO[Seq[(Int, String, String, String, String, String)]] =
  tsql"select * from suppliers where id > $id"

tsqlは.asを呼び出す必要無しに、直接DBIOActionを生成する。

tsqlを利用する際は、SQLコンパイラをデータベースにアクセスさせるために、コンパイル時に解決できる設定を提供してあげる必要がある。これはStaticDatabaseConfigアノテーションを利用して明示する。

@StaticDatabaseConfig("file:src/main/resources/application.conf#tsql")

上の例だと、application.confというファイルにおける、"tsql"というパスを指し示しており、ここにはDatabaseの設定だけではなく、StaticDatabaseConfigオブジェクトのための適切な設定を記述しなくてはならない。

Note
パスを省いたり、URLのフラグメントのみを指定したりすると、クラスパスにある中からapplication.confを見つけようとする。また、resource:というURLスキーマを利用しても良いが、いずれにしても実行時のクラスパスと異なり、コンパイラ時のクラスパスからそれらは見えるようにする必要がある。ビルドツールによっては設定が出来ないかもしれないため、基本的にはfile:のURLスキーマで相対パスを指定するのが良い。

実行時に、設定のされたDatabaseConfigを取得させても構わない。

val dc = DatabaseConfig.forAnnotation[JdbcProfile]
import dc.driver.api._
val db = dc.db

ここでは、基本的なapi._というインポートとDatabaseを利用している。同じ設定を用いさせることは特に強制しておらず、SlickドライバとDatabaseを他の方法で実行時に渡しても良いし、コンパイル時のチェックのみにStaticDatabaseConfigを利用するといった方法も1つの選択肢として考えられる。

Next Page ❧ ORMからSlickを利用する人へ