Anorm 2.5 (2) - Anorm 시작하기

프로젝트에 Anorm 을 추가하자.

Anorm 과 JDBC 플러그인을 당신의 디펜던시에 추가해야합니다.

libraryDependencies ++= Seq(
  jdbc,
  "com.typesafe.play" %% "anorm" % "2.5.0"
)

SQL 쿼리 실행하기 

먼저 어떻게 SQL 쿼리를 실행하는지 알아 봅시다.

첫번째로 anorm._  를 임포트 하시고 SQL object 를 쿼리를 만들기 위해 사용합니다.
쿼리를 실행하기 위해서는 .  Connection 이 필요한데 play.api.db.DB 헬퍼로 얻을 수 있습니다.
(play.api.db.DB 는 Play 2.5 에서 deprecated 되었으며 아직 정확한 사용방법에 대해서 나중에 학습하게 되면 블로깅 하죵) 

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

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

execute() 메소드는 Boolean 값을 리턴 받습니다. 실행의 성공 여부에 따라서 말이죠.

To execute an update, you can use executeUpdate(), which returns the number of rows updated.

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

If you are inserting data that has an auto-generated Long primary key, you can callexecuteInsert().

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

When key generated on insertion is not a single Long, executeInsert can be passed aResultSetParser to return the correct key.

import anorm.SqlParser.str

val id: List[String] = 
  SQL("insert into City(name, country) values ({name}, {country})")
  .on('name -> "Cambridge", 'country -> "New Zealand")
  .executeInsert(str.+) // insertion returns a list of at least one string keys

Since Scala supports multi-line strings, feel free to use them for complex SQL statements:

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

If your SQL query needs dynamic parameters, you can declare placeholders like{name} in the query string, and later assign a value to them:

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

You can also use string interpolation to pass parameters (see details thereafter).

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 examplecode 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"

When a column is aliased, typically using SQL AS, its value can also be resolved. Following example parses column with country_lang alias.

import anorm.{ SQL, SqlParser }

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

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

import anorm.SqlParser.{ str, float }
// Parsing column by name or position
val parser = 
  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)

If the columns are not strictly defined (e.g. with types that can vary), theSqlParser.folder can be used to fold each row in a custom way.

import anorm.{ RowParser, SqlParser }

val parser: RowParser[Map[String, Any]] = 
  SqlParser.folder(Map.empty[String, Any]) { (map, value, meta) => 
    Right(map + (meta.column.qualified -> value))
  }

val result: List[Map[String, Any]] = SQL"SELECT * FROM dyn_table".as(parser.*)

If the columns are not strictly defined (e.g. with types that can vary), theSqlParser.folder can be used to fold each row in a custom way.

import anorm.{ RowParser, SqlParser }

val parser: RowParser[Map[String, Any]] = 
  SqlParser.folder(Map.empty[String, Any]) { (map, value, meta) => 
    Right(map + (meta.column.qualified -> value))
  }

val result: List[Map[String, Any]] = SQL"SELECT * FROM dyn_table".as(parser.*)

Table alias

With some databases, it’s possible to define aliases for table (or for sub-query), as in the following example.

=> SELECT * FROM test t1 JOIN (SELECT * FROM test WHERE parent_id ISNULL) t2 ON t1.parent_id=t2.id WHERE t1.id='bar';
 id  | value  | parent_id | id  | value  | parent_id 
-----+--------+-----------+-----+--------+-----------
 bar | value2 | foo       | foo | value1 | 
(1 row)

Unfortunately, such aliases are not supported in JDBC, so Anorm introduces theColumnAliaser to be able to define user aliases over columns.

import anorm._

val parser: RowParser[(String, String, String, Option[String])] = SqlParser.str("id") ~ SqlParser.str("value") ~ SqlParser.str("parent.value") ~ SqlParser.str("parent.parent_id").? map(SqlParser.flatten)

val aliaser: ColumnAliaser = ColumnAliaser.withPattern((3 to 6).toSet, "parent.")

val res: Try[(String, String, String, Option[String])] = SQL"""SELECT * FROM test t1 JOIN (SELECT * FROM test WHERE parent_id ISNULL) t2 ON t1.parent_id=t2.id WHERE t1.id=${"bar"}""".asTry(parser.single, aliaser)

res.foreach {
  case (id, value, parentVal, grandPaId) => ???
}

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 ofSQL().on() functions (or even an only SQL() without parameters).

By using #$value instead of $value, interpolated value will be part of the prepared statement, rather being passed as a parameter when executing this SQL statement (e.g. #$cmd and #$table in example bellow).

val cmd = "SELECT"
val table = "Test"

SQL"""#$cmd * FROM #$table WHERE id = ${"id1"} AND code IN (${Seq(2, 5)})"""

// prepare the SQL statement, with 1 string and 2 integer parameters:
// SELECT * FROM Test WHERE id = ? AND code IN (?, ?)

Generated parsers

The macro namedParser[T] can be used to create a RowParser[T] at compile-time, for any case class T.

import anorm.{ Macro, RowParser }

case class Info(name: String, year: Option[Int])

val parser: RowParser[Info] = Macro.namedParser[Info]
/* Generated as:
get[String]("name") ~ get[Option[Int]]("year") map {
  case name ~ year => Info(name, year)
}
*/

val result: List[Info] = SQL"SELECT * FROM list".as(parser.*)

The similar macros indexedParser[T] and offsetParser[T] are available to get column values by positions instead of names.

import anorm.{ Macro, RowParser }

case class Info(name: String, year: Option[Int])

val parser1: RowParser[Info] = Macro.indexedParser[Info]
/* Generated as:
get[String](1) ~ get[Option[Int]](2) map {
  case name ~ year => Info(name, year)
}
*/

val result1: List[Info] = SQL"SELECT * FROM list".as(parser1.*)

// With offset
val parser2: RowParser[Info] = Macro.offsetParser[Info](2)
/* Generated as:
get[String](2 + 1) ~ get[Option[Int]](2 + 2) map {
  case name ~ year => Info(name, year)
}
*/

val result2: List[Info] = SQL"SELECT * FROM list".as(parser2.*)

To indicate custom names for the columns to be parsed, the macro parser[T](names)can be used.

import anorm.{ Macro, RowParser }

case class Info(name: String, year: Option[Int])

val parser: RowParser[Info] = Macro.parser[Info]("a_name", "creation")
/* Generated as:
get[String]("a_name") ~ get[Option[Int]]("creation") map {
  case name ~ year => Info(name, year)
}
*/

val result: List[Info] = SQL"SELECT * FROM list".as(parser.*)

The RowParser exposed in the implicit scope can be used as nested one generated by the macros.

case class Bar(lorem: Float, ipsum: Long)
case class Foo(name: String, bar: Bar, age: Int)

import anorm._

// nested parser
implicit val barParser = Macro.parser[Bar]("bar_lorem", "bar_ipsum")

val fooBar = Macro.namedParser[Foo] /* generated as:
  get[String]("name") ~ barParser ~ get[Int]("age") map {
    case name ~ bar ~ age => Foo(name, bar, age)
  }
*/

val result: Foo = SQL"""SELECT f.name, age, bar_lorem, bar_ipsum 
  FROM foo f JOIN bar b ON f.name=b.name WHERE f.name=${"Foo"}""".
  as(fooBar.single)

The anorm.macro.debug system property can be set to true (e.g. sbt -Danorm.macro.debug=true ...) to debug the generated parsers.

 Anorm 2.5 공식 문서에서 번역했습니다.  원문 바로가기