You are viewing the documentation for the 2.3.x release series. The latest stable release series is 2.4.x.

§Anorm によるシンプルな SQL データアクセス

Play には Anorm と呼ばれるシンプルなデータアクセスレイヤーが同梱されています。Anorm はデータベースとやり取りするのに SQL をそのまま利用すると同時に、結果のデータセットをパースしたり変換するための API を提供します。

Anorm は ORM (Object Relational Mapper) ではない

以下のドキュメントでは、MySQL world サンプルデータベースを利用します。

If you want to enable it for your application, follow the MySQL website instructions, and configure it as explained on the Scala database page .

§概観

Hibernate のような、SQL を完全に隠蔽するような高級な ORM に慣れた Java デベロッパーの方にとって、今時 Anorm がデータベースアクセスに昔ながらの SQL を直接使っていることは奇妙に思われるかもしれません。

そのようなツールは、確かに Java では必要だったと思います。しかしながら、Scala のような高級な言語の恩恵を受けられる場合には全く必要ではないと考えます。それどころか、そのような高級な ORM は Scala の場合はすぐ非生産的に感じられてしまうことでしょう。

§JDBC を使うのはつらいけど、私たちがより良い API を提供する

特に Java で JDBC API を直接使うことには飽き飽きしているかと思います。Java で JDBC を直接使うような場合、コードのあらゆる箇所でチェック例外を考慮しなければならず、また生のデータセットをアプリケーション独自のデータ構造に変換するために ResultSet を何度も何度も読み込む必要があります。

私たちは JDBC のよりシンプルな API を作ることにしました。Scala を使えば、例外にわずらわされることもなく、関数型の言語機能によりデータの変換処理がとても簡単になります。実際、Play の Scala SQL アクセスレイヤーの目的は、JDBC のデータを Scala のデータ構造へ効率的に変換するための API を提供することです。

§リレーショナルデータベースにアクセスするための別の DSL は必要ない

SQL は既にリレーショナルデータベースにアクセスするための最適な DSL です。私たちは SQL に代わる何か新しい何かを発明する必要はありません。加えて、SQL の文法や機能はデータベースベンダーによって異なってもいます。

このような SQL の方言をプロプライエタリかつ SQL ライクな DSL で抽象化しようとすると、(Hibernate のように) ベンダ固有の dialects に対応する必要があります。これは同時に、データベースの特有の便利な機能の利用を自ら制限することにもなってしまいます。

Play には SQL ステートメントの組み立てを補助する機能も提供しています。しかし、その主な目的は SQL を隠蔽することではありません。Play は平凡なクエリについてタイプ量を削減してくれるだけであり、いつでも素の SQL に戻ることができます。

§SQL を生成するための型安全な DSL は間違い

型安全な DSL はコンパイラによって検査されるため、素の SQL より優れていると言われることがあります。しかし、残念ながらコンパイラはあなたが自分自身で書いたデータ構造とデータベーススキーマをマッピングするメタモデル定義をもとにクエリを検査します。

このメタモデルが正しいという保証はありません。コンパイラがあなたのコードやクエリが正しくタイプされていると判断したとしても、メタモデルと実際のデータベーススキーマにミスマッチがあれば、残念なことに実行時エラーになってしまいます。

§SQL コードをコントロールする

Object Relational Mapping は平凡なケースではうまくいきます。しかし、実際にあなたが直面すると予想される複雑なスキーマや既存のデータベースに対応する必要があるとき、あなたが求める SQL クエリを生成するために ORM との格闘に多くの時間を費やすことになるでしょう。

SQL クエリを自分で書くのはシンプルな Hello World アプリケーションのような場合にはつまらないものに感じられますが、実際のアプリケーションでは SQL コードを完全にコントロールできることが結果的に時間を節約し、あなたのコードを簡潔にするでしょう。

§プロジェクトに Anorm を追加する

依存ライブラリに Anorm と jdbc プラグインを追加する必要があります :

libraryDependencies ++= Seq(
  jdbc,
  anorm
)

§SQL クエリを実行する

まずは SQL の実行方法を知る必要があります。

初めに、 anorm._ をインポートして、 SQL オブジェクトを使ってクエリを作成しましょう。クエリを実行するためには Connection が必要なので play.api.db.DB ヘルパーを使います。

import anorm._ 
import play.api.db.DB

DB.withConnection { implicit c =>
  val result: Boolean = SQL("Select 1").execute()    
}

execute() メソッドは SQL 実行が成功したかどうかを表す Boolean 値を返します。

Update を実行するためには、executeUpdate() が利用できます。このメソッドは更新された行数を返します。

val result: Int = SQL("delete from City where id = 99").executeUpdate()

自動採番の Long 型の主キーを持つデータを挿入する場合は、executeInsert() を使います。生成されるキーが一つより多い場合や Long 型でない場合、正しいキーを返却する ResultSetParser を executeInsert に渡します。

val id: Option[Long] = 
  SQL("insert into City(name, country) values ({name}, {country})")
  .on('name -> "Cambridge", 'country -> "New Zealand").executeInsert()

Scala は複数行の文字列リテラルをサポートしているため、複雑な SQL 文も自由に書くことができます。

val sqlQuery = SQL(
  """
    select * from Country c 
    join CountryLanguage l on l.CountryCode = c.Code 
    where c.code = 'FRA';
  """
)

もし SQL クエリが動的なパラメータをとる場合、{name} のような名前付きのプレースホルダをクエリ文字列内に宣言して、後でそれらに値を埋め込むことができます。

SQL(
  """
    select * from Country c 
    join CountryLanguage l on l.CountryCode = c.Code 
    where c.code = {countryCode};
  """
).on("countryCode" -> "FRA")

In case several columns are found with same name in query result, for example columns named code in both Country and CountryLanguage tables, there can be ambiguity. By default a mapping like following one will use the last column:

import anorm.{ SQL, SqlParser }

val code: String = SQL(
  """
    select * from Country c 
    join CountryLanguage l on l.CountryCode = c.Code 
    where c.code = {countryCode};
  """)
  .on("countryCode" -> "FRA").as(SqlParser.str("code").single)

If Country.Code is ‘First’ and CountryLanguage is ‘Second’, then in previous example code value will be ‘Second’. Ambiguity can be resolved using qualified column name, with table name:

import anorm.{ SQL, SqlParser }

val code: String = SQL(
  """
    select * from Country c 
    join CountryLanguage l on l.CountryCode = c.Code 
    where c.code = {countryCode};
  """)
  .on("countryCode" -> "FRA").as(SqlParser.str("Country.code").single)
// code == "First"

Columns can also be specified by position, rather than name:

// Parsing column by name or position
val parser = 
  SqlParser(str("name") ~ float(3) /* third column as float */ map {
    case name ~ f => (name -> f)
  }

val product: (String, Float) = SQL("SELECT * FROM prod WHERE id = {id}").
  on('id -> "p").as(parser.single)

java.util.UUID can be used as parameter, in which case its string value is passed to statement.

§Using multi-value parameter

Anorm parameter can be multi-value, like a sequence of string.
In such case, values will be prepared to be passed to JDBC.

// With default formatting (", " as separator)
SQL("SELECT * FROM Test WHERE cat IN ({categories})").
  on('categories -> Seq("a", "b", "c")
// -> SELECT * FROM Test WHERE cat IN ('a', 'b', 'c')

// With custom formatting
import anorm.SeqParameter
SQL("SELECT * FROM Test t WHERE {categories}").
  on('categories -> SeqParameter(
    values = Seq("a", "b", "c"), separator = " OR ", 
    pre = "EXISTS (SELECT NULL FROM j WHERE t.id=j.id AND name=",
    post = ")"))
/* ->
SELECT * FROM Test t WHERE 
EXISTS (SELECT NULL FROM j WHERE t.id=j.id AND name='a') 
OR EXISTS (SELECT NULL FROM j WHERE t.id=j.id AND name='b') 
OR EXISTS (SELECT NULL FROM j WHERE t.id=j.id AND name='c')
*/

§Edge cases

Passing anything different from string or symbol as parameter name is now deprecated. For backward compatibility, you can activate anorm.features.parameterWithUntypedName.

import anorm.features.parameterWithUntypedName // activate

val untyped: Any = "name" // deprecated
SQL("SELECT * FROM Country WHERE {p}").on(untyped -> "val")

Type of parameter value should be visible, to be properly set on SQL statement.
Using value as Any, explicitly or due to erasure, leads to compilation error No implicit view available from Any => anorm.ParameterValue.

// Wrong #1
val p: Any = "strAsAny"
SQL("SELECT * FROM test WHERE id={id}").
  on('id -> p) // Erroneous - No conversion Any => ParameterValue

// Right #1
val p = "strAsString"
SQL("SELECT * FROM test WHERE id={id}").on('id -> p)

// Wrong #2
val ps = Seq("a", "b", 3) // inferred as Seq[Any]
SQL("SELECT * FROM test WHERE (a={a} AND b={b}) OR c={c}").
  on('a -> ps(0), // ps(0) - No conversion Any => ParameterValue
    'b -> ps(1), 
    'c -> ps(2))

// Right #2
val ps = Seq[anorm.ParameterValue]("a", "b", 3) // Seq[ParameterValue]
SQL("SELECT * FROM test WHERE (a={a} AND b={b}) OR c={c}").
  on('a -> ps(0), 'b -> ps(1), 'c -> ps(2))

// Wrong #3
val ts = Seq( // Seq[(String -> Any)] due to _2
  "a" -> "1", "b" -> "2", "c" -> 3)

val nps: Seq[NamedParameter] = ts map { t => 
  val p: NamedParameter = t; p
  // Erroneous - no conversion (String,Any) => NamedParameter
}

SQL("SELECT * FROM test WHERE (a={a} AND b={b}) OR c={c}").on(nps :_*) 

// Right #3
val nps = Seq[NamedParameter]( // Tuples as NamedParameter before Any
  "a" -> "1", "b" -> "2", "c" -> 3)
SQL("SELECT * FROM test WHERE (a={a} AND b={b}) OR c={c}").
  on(nps: _*) // Fail - no conversion (String,Any) => NamedParameter

For backward compatibility, you can activate such unsafe parameter conversion,
accepting untyped Any value, with anorm.features.anyToStatement.

import anorm.features.anyToStatement

val d = new java.util.Date()
val params: Seq[NamedParameter] = Seq("mod" -> d, "id" -> "idv")
// Values as Any as heterogenous

SQL("UPDATE item SET last_modified = {mod} WHERE id = {id}").on(params:_*)

It’s not recommanded because moreover hiding implicit resolution issues, as untyped it could lead to runtime conversion error, with values are passed on statement using setObject.
In previous example, java.util.Date is accepted as parameter but would with most databases raise error (as it’s not valid JDBC type).

§SQL queries using String Interpolation

Since Scala 2.10 supports custom String Interpolation there is also a 1-step alternative to SQL(queryString).on(params) seen before. You can abbreviate the code as:

val name = "Cambridge"
val country = "New Zealand"

SQL"insert into City(name, country) values ($name, $country)")

It also supports multi-line string and inline expresions:

val lang = "French"
val population = 10000000
val margin = 500000

val code: String = SQL"""
  select * from Country c 
    join CountryLanguage l on l.CountryCode = c.Code 
    where l.Language = $lang and c.Population >= ${population - margin}
    order by c.Population desc limit 1"""
  .as(SqlParser.str("Country.code").single)

This feature tries to make faster, more concise and easier to read the way to retrieve data in Anorm. Please, feel free to use it wherever you see a combination of SQL().on() functions (or even an only SQL() without parameters).

§Stream API を利用してデータを取得する

select クエリから結果を参照する第一の方法は、 Stream API を利用することです。

SQL 文に対して apply() を呼び出すと、遅延評価される Row インスタンスの Stream を取得できます。各行（Row ）はディクショナリのデータ型になっています。

// Create an SQL query
val selectCountries = SQL("Select * from Country")
 
// Transform the resulting Stream[Row] to a List[(String,String)]
val countries = selectCountries().map(row => 
  row[String]("code") -> row[String]("name")
).toList

次の例ではデータベース内の Country の行数を数えます。結果セットは一つのカラムを持つ一行だけの結果になります。

// First retrieve the first row
val firstRow = SQL("Select count(*) as c from Country").apply().head
 
// Next get the content of the 'c' column as Long
val countryCount = firstRow[Long]("c")

§パターンマッチの利用

Row の内容を抽出するためにパターンマッチを利用することができます。このケースでは、カラム名は関係ありません。パラメータの順番と型だけがパターンマッチの際に考慮されます。

次の例は各行を適切な Scala の型に変換します。

case class SmallCountry(name:String) 
case class BigCountry(name:String) 
case class France
 
val countries = SQL("Select name,population from Country")().collect {
  case Row("France", _) => France()
  case Row(name:String, pop:Int) if(pop > 1000000) => BigCountry(name)
  case Row(name:String, _) => SmallCountry(name)      
}

collect(…) は部分関数が定義されていない case を無視するので、想定しない行は安全に読み飛ばすことができます。

§Using for-comprehension

Row parser can be defined as for-comprehension, working with SQL result type. It can be useful when working with lot of column, possibly to work around case class limit.

import anorm.SqlParser.{ str, int }

val parser = for {
  a <- str("colA")
  b <- int("colB")
} yield (a -> b)

val parsed: (String, Int) = SELECT("SELECT * FROM Test").as(parser.single)

§Retrieving data along with execution context

Moreover data, query execution involves context information like SQL warnings that may be raised (and may be fatal or not), especially when working with stored SQL procedure.

Way to get context information along with query data is to use executeQuery():

import anorm.SqlQueryResult

val res: SqlQueryResult = SQL("EXEC stored_proc {code}").
  on('code -> code).executeQuery()

// Check execution context (there warnings) before going on
val str: Option[String] =
  res.statementWarning match {
    case Some(warning) =>
      warning.printStackTrace()
      None

    case _ => res.as(scalar[String].singleOpt) // go on row parsing
  }

§特別なデータ型

§Clobs

CLOB/TEXT は以下のようにして取り出すことができます:

SQL("Select name,summary from Country")().map {
  case Row(name: String, summary: java.sql.Clob) => name -> summary
}

行が期待するフォーマットでない場合は例外にしたいので、ここでは特に map メソッドを使っています。

§Binary

同様にバイナリデータも取り出すことができます:

SQL("Select name,image from Country")().map {
  case Row(name: String, image: Array[Byte]) => name -> image
}

Note that different databases will return different data types in the Row. For instance, an SQL ‘smallint’ is returned as a Short by org.h2.Driver and an Integer by org.postgresql.Driver. A solution to this is to simply write separate case statements for each database (i.e. one for development and one for production).

Anorm provides common mappings for Scala types from JDBC datatypes.

When needed, it’s possible to customize such mappings, for example if underlying DB doesn’t support boolean datatype and returns integer instead. To do so, you have to provide a new implicit conversion for Column[T], where T is the target Scala type:

import anorm.Column

// Custom conversion from JDBC column to Boolean
implicit def columnToBoolean: Column[Boolean] = 
  Column.nonNull { (value, meta) =>
    val MetaDataItem(qualified, nullable, clazz) = meta
    value match {
      case bool: Boolean => Right(bool) // Provided-default case
      case bit: Int      => Right(bit == 1) // Custom conversion
      case _             => Left(TypeDoesNotMatch(s"Cannot convert $value: ${value.asInstanceOf[AnyRef].getClass} to Boolean for column $qualified"))
    }
  }

Custom or specific DB conversion for parameter can also be provided:

import java.sql.PreparedStatement
import anorm.ToStatement

// Custom conversion to statement for type T
implicit def customToStatement: ToStatement[T] = new ToStatement[T] {
  def set(statement: PreparedStatement, i: Int, value: T): Unit =
    ??? // Sets |value| on |statement|
}

If involved type accept null value, it must be appropriately handled in conversion. Even if accepted by type, when null must be refused for parameter conversion, marker trait NotNullGuard can be used: new ToStatement[T] with NotNullGuard { /* ... */ }.

For DB specific parameter, it can be explicitly passed as opaque value.
In this case at your own risk, setObject will be used on statement.

val anyVal: Any = myVal
SQL("UPDATE t SET v = {opaque}").on('opaque -> anorm.Object(anyVal))

§nullable なカラムを扱う

あるカラムがデータベーススキーマにおいて Null 値を含む場合、Option 型で操作する必要があります。

例えば、Country テーブルの indepYear というカラムが nullable の場合、それを Option[Int] にマッチさせます。

SQL("Select name,indepYear from Country")().collect {
  case Row(name:String, Some(year:Int)) => name -> year
}

このカラムを Int としてマッチさせようとすると、Null 値をパースできません。辞書型の row からカラムの内容を Int として取得する場合を考えてみましょう。

SQL("Select name,indepYear from Country")().map { row =>
  row[String]("name") -> row[Int]("indepYear")
}

このコードは null 値がヒットした場合に UnexpectedNullableFound(COUNTRY.INDEPYEAR) という例外を投げます。正しくは次のように Option[Int] にマップさせる必要があります。

SQL("Select name,indepYear from Country")().map { row =>
  row[String]("name") -> row[Option[Int]]("indepYear")
}

これは次で説明する parser API についても同様です。

§Parser API の利用

様々な select 文の結果をパースする汎用的かつ再利用可能なパーサーを定義するためには parser API が利用できます。

Note: Web アプリケーションのほとんどのクエリは同じようなデータセットを返すため、Parser API が効果的です。例えば、result set から Country をパースするパーサーと、Language をパースするパーサーを定義すると、それらを組み合わせ join クエリから Country と Language を同時にパースするパーサーを作成することができます。

はじめに、import anorm.SqlParser._ を書きます。

§結果を1行取得する

まずは RowParser が必要です。これは、一行をパースして Scala の値へ変換するパーサーです。例えば、一カラムの結果セットの行をパースして Scala の Long 値を生成するようなパーサーは次のように記述します。

val rowParser = scalar[Long]

次に、これを ResultSetParser へ変換する必要があります。次のように、一行をパースするパーサを定義しましょう。

val rsParser = scalar[Long].single

このパーサーは結果セットをパースして Long 値を返します。これは、select count のような単純な SQL 文の結果をパースするような場合に便利です。

val count: Long = 
  SQL("select count(*) from Country").as(scalar[Long].single)

§Option型の結果を1行取得する

coutry name から country_id を読み出す場合を考えてみましょう。しかし、クエリはnullを返すかもしれません。その場合は singleOpt パーサーを使いましょう:

val countryId: Option[Long] = 
  SQL("select country_id from Country C where C.country='France'")
  .as(scalar[Long].singleOpt)

§より複雑な結果を取得する

もっと複雑なパーサーを書いてみましょう。

str("name") ~ int("population") は String 型 name カラムと Integer 型 population カラムを含む列をパース可能な RowParser を作成します。また、 * を使うことで、この種の多くの行をパースする ResultSetParser を作成できます。

val populations: List[String~Int] = {
  SQL("select * from Country").as( str("name") ~ int("population") * ) 
}

ご覧のとおり、このクエリの結果の型は List[String~Int] 、つまり国名と人口のペアを要素とするリストになります。

このコードは次のように書くこともできます。

val result: List[String~Int] = {
  SQL("select * from Country")
  .as(get[String]("name") ~ get[Int]("population") *)
}

さて、String~Int という型は一体何でしょうか？これは Anorm で定義されている型で、データベースアクセスに関するコード以外での利用には適していません。例えば、(String, Int) のような単純なタプルをパースしたいことが多いでしょう。そのためには、 RowParser の map 関数を使って、結果をもっと便利な型に変換します。

val parser = str("name") ~ int("population") map { case n~p => (n,p) }

Note: この例では (String,Int) というタプルを生成しましたが、RowParser の結果を自前の case class など別の型に変換しても何ら問題ありません。

Now, because transforming A ~ B ~ C types to (A, B, C) is a common task, we provide a flatten function that does exactly that. So you finally write:

val result: List[(String, Int)] = 
  SQL("select * from Country").as(parser.*)

If list should not be empty, parser.+ can be used instead of parser.*.

§より複雑な例

次はさらに複雑な例に挑戦してみましょう。次のクエリの結果から国名とその国コード語毎にその国の口語一覧を取得するにはどうしたらよいでしょうか?

select c.name, l.language from Country c 
    join CountryLanguage l on l.CountryCode = c.Code 
    where c.code = 'FRA'

まず、全ての行から List[(String,String)] 、つまり国名と言語のタプルを要素とするリストをパースすることから始めます。

var p: ResultSetParser[List[(String,String)]] = {
  str("name") ~ str("language") map(flatten) *
}

結果は以下のようになります。

List(
  ("France", "Arabic"), 
  ("France", "French"), 
  ("France", "Italian"), 
  ("France", "Portuguese"), 
  ("France", "Spanish"), 
  ("France", "Turkish")
)

次に Scala の Collection API を使ってこれを変換して期待される結果に変換します：

case class SpokenLanguages(country:String, languages:Seq[String])

languages.headOption.map { f =>
  SpokenLanguages(f._1, languages.map(_._2))
}

最終的に、このような便利な関数を作ることができました。

case class SpokenLanguages(country:String, languages:Seq[String])

def spokenLanguages(countryCode: String): Option[SpokenLanguages] = {
  val languages: List[(String, String)] = SQL(
    """
      select c.name, l.language from Country c 
      join CountryLanguage l on l.CountryCode = c.Code 
      where c.code = {code};
    """
  )
  .on("code" -> countryCode)
  .as(str("name") ~ str("language") map(flatten) *)

  languages.headOption.map { f =>
    SpokenLanguages(f._1, languages.map(_._2))
  }
}

続いて、もう少し複雑な例として、公式サポートされている言語をそれ以外の言語と分けてみましょう。

case class SpokenLanguages(
  country:String, 
  officialLanguage: Option[String], 
  otherLanguages:Seq[String]
)

def spokenLanguages(countryCode: String): Option[SpokenLanguages] = {
  val languages: List[(String, String, Boolean)] = SQL(
    """
      select * from Country c 
      join CountryLanguage l on l.CountryCode = c.Code 
      where c.code = {code};
    """
  )
  .on("code" -> countryCode)
  .as {
    str("name") ~ str("language") ~ str("isOfficial") map {
      case n~l~"T" => (n,l,true)
      case n~l~"F" => (n,l,false)
    } *
  }

  languages.headOption.map { f =>
    SpokenLanguages(
      f._1, 
      languages.find(_._3).map(_._2),
      languages.filterNot(_._3).map(_._2)
    )
  }
}

これを MySQL での world サンプルデータベースに対して実行すると、次のような結果が得られます。

$ spokenLanguages("FRA")
> Some(
    SpokenLanguages(France,Some(French),List(
        Arabic, Italian, Portuguese, Spanish, Turkish
    ))
)

§Type compatibility

As already seen in this documentation, Anorm provides builtins JDBC parsing for various JVM types.

Following table describes which JDBC numeric types (getters on java.sql.ResultSet, first column) can be parsed to which Java/Scala types (e.g. integer column can be read as double value).

↓JDBC / JVM➞	BigDecimal¹	BigInteger²	Boolean	Byte	Double	Float	Int	Long	Short
BigDecimal¹	Yes	No	No	No	Yes	No	No	No	No
BigInteger²	No	Yes	No	No	Yes	Yes	No	No	No
Boolean	No	No	Yes	No	No	No	No	No	No
Byte	No	No	No	Yes	Yes	Yes	No	No	Yes
Double	Yes	No	Yes	No	Yes	No	No	No	No
Float	No	No	No	No	Yes	Yes	No	No	No
Int	No	Yes	Yes	No	Yes	Yes	Yes	Yes	No
Long	Yes	Yes	No	No	No	No	No	Yes	No
Short	No	No	No	Yes	Yes	Yes	No	No	Yes

1. Types java.math.BigDecimal and scala.math.BigDecimal.
1. Types java.math.BigInteger and scala.math.BigInt.

Second table shows mapping for other supported types (texts, dates, …).

↓JDBC / JVM➞	Char	Date	String	UUID³
Clob	Yes	No	Yes	No
Date	No	Yes	No	No
Long	No	Yes	No	No
String	Yes	No	Yes	No
UUID	No	No	No	Yes

1. Type java.util.UUID.

Optional column can be parsed as Option[T], as soon as T is supported.

次ページ: その他のデータベースライブラリとの統合

このドキュメントの翻訳は Play チームによってメンテナンスされているものではありません。間違いを見つけた場合、このページのソースコードをここで確認することができます。ドキュメントガイドラインを読んで、お気軽にプルリクエストを送ってください。