Play 2.xからMySQLに接続してAnormでCRUD操作するには：Scala＋Play 2.0でWebアプリ開発入門（7）（3/3 ページ）

% play
[gyro] $ console
Welcome to Scala version 2.10.0 (Java HotSpot(TM) 64-Bit Server VM, Java 1.7.0_04).
Type in expressions to have them evaluated.
Type :help for more information.
scala>

scala> import play.core.StaticApplication
scala> new StaticApplication(new java.io.File("."))
・
・
[info] play - Application started (Prod)
res1: play.core.StaticApplication = play.core.StaticApplication@193d69e

scala> import play.api.db._
scala> import play.api.Play.current
scala> import anorm._ 
scala> DB.withConnection { implicit c => 
 val result: Boolean = SQL("Select 1").execute()    
 println("result =" + result)
} 
[debug] c.j.b.PreparedStatementHandle - Select 1
result =true

//Userテーブルにレコードを1件登録
scala> DB.withConnection { implicit c =>
        val count = SQL(
          """
            insert into User(name,email,password)
            values({name},{email},{password})
          """
        ).on('name -> "taro",
          'email -> "taro@classmethod.jp",
          'password -> "taropass").executeInsert()
        println("count = " + count)
      }
[debug] c.j.b.PreparedStatementHandle - insert into User(name) values('taro')
count = Some(1)

//id:1のレコードを更新
scala> DB.withConnection { implicit c =>
        val count = SQL(
          """
            update User set name={name} where id={id} 
          """
        ).on('name -> "hanako", 'id -> 6).executeUpdate()
        println("updateCount = " + count)
      }
[debug] c.j.b.PreparedStatementHandle - update User set name='hanako' where id=1
updateCount = 1 

//id:1のレコードを削除
scala> DB.withConnection { implicit c =>
        val count: Int = SQL(
          """
           delete from User where id = {id}
          """
          ).on('id -> 1).executeUpdate()
        println("deleteCount = " + count)
      }
[debug] c.j.b.PreparedStatementHandle - delete from User where id = 1
deleteCount = 1

% mysql -u<ユーザー名> -p<パスワード>
mysql> use gyro;
mysql>  insert into User values(1,'taro','taro@classmethod.jp','taropass',now());
        insert into User values(2,'hanako','hanako@classmethod.jp','hanakopass',now());
        insert into User values(3,'mike','mike@mike.com','mikepass',now());
        insert into Post values(100,1,'title1','body1',now());
        insert into Post values(101,1,'title2','body2',now());
        insert into Post values(102,2,'title3','body3',now());

scala> val selectQuery = SQL("select * from User")
       val result = DB.withConnection { implicit c =>
       //Stream[Row] からList[(Long,(String,String))]に変換
       selectQuery().map(row => 
       row[Long]("id") -> (row[String]("name") , row[String]("email"))).toList
     }
[debug] c.j.b.PreparedStatementHandle - Select * from User
result: List[(Long, (String, String))] = List(
(1,(taro,taro@classmethod.jp)),
(2,(hanako,hanako@classmethod.jp)),
(3,(mike,mike@mike.com)))

scala> import anorm.SqlParser._ 
scala> val result:List[Int~String~String] = DB.withConnection { implicit c =>
  SQL("select * from User").as( int("id") ~ str("name") ~ str("email") * )
}
[debug] c.j.b.PreparedStatementHandle - select * from User
result: List[anorm.~[anorm.~[Int,String],String]] = List(
~(~(1,taro),taro@classmethod.jp), 
~(~(2,hanako),hanako@classmethod.jp), 
~(~(3,mike),mike@mike.com))

scala> val result = DB.withConnection { implicit c =>
  SQL("select * from User").as( int("id") ~ str("name") ~ str("email") map(flatten) * )
}
[debug] c.j.b.PreparedStatementHandle - select * from User
result: List[(Int, String, String)] = List(
(1,taro,taro@classmethod.jp), 
(2,hanako,hanako@classmethod.jp), 
(3,mike,mike@mike.com))

scala> case class CmEmp(name:String)
scala> case class Person(name:String)
scala> val result = DB.withConnection { implicit c =>
  SQL("select * from User").as( int("id") ~ str("name") ~ str("email") map {
    //メールアドレスに@classmethod.jpが含まれていたら、CmEmpオブジェクトにする
    case id~name~email if(email.contains("@classmethod.jp")) => CmEmp(name)
    case id~name~email => Person(name)
  } *)
}
[debug] c.j.b.PreparedStatementHandle - select * from User
result: List[Product with Serializable] = 
List(CmEmp(taro), CmEmp(hanako), Person(mike))