일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | ||
6 | 7 | 8 | 9 | 10 | 11 | 12 |
13 | 14 | 15 | 16 | 17 | 18 | 19 |
20 | 21 | 22 | 23 | 24 | 25 | 26 |
27 | 28 | 29 | 30 | 31 |
- 파이썬 머신러닝
- play2 강좌
- Play2 로 웹 개발
- 주키퍼
- 하이퍼레저 패브릭
- 파이썬 동시성
- 스칼라 동시성
- 이더리움
- 블록체인
- 그라파나
- Hyperledger fabric gossip protocol
- 엔터프라이즈 블록체인
- 스칼라 강좌
- Play2
- play 강좌
- 파이썬 강좌
- hyperledger fabric
- 하이브리드앱
- 파이썬 데이터분석
- 파이썬
- 안드로이드 웹뷰
- akka 강좌
- Actor
- Akka
- 플레이프레임워크
- Adapter 패턴
- 스위프트
- 스칼라
- Golang
- CORDA
- Today
- Total
HAMA 블로그
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 totrue
(e.g.sbt -Danorm.macro.debug=true ...
) to debug the generated parsers.
Anorm 2.5 공식 문서에서 번역했습니다. 원문 바로가기
'PlayFramework2' 카테고리의 다른 글
Anorm 2.5 (4) - Parser API 사용하기 (0) | 2016.08.26 |
---|---|
Anorm 2.5 (3) - Streamming 과 Pattern 매칭 사용하기 (0) | 2016.08.26 |
Anorm 2.5 (1) - Anorm 이란 ? (0) | 2016.08.26 |
play2 에서 Anorm 으로 PostgreSQL 사용하기 (0) | 2016.08.24 |
Deploy Play 2.4 (Scala) on Amazon EC2 [Simple] (0) | 2016.08.11 |