Created
January 19, 2015 08:28
-
-
Save 1206yaya/e79be02ed0da02129861 to your computer and use it in GitHub Desktop.
Play 2.xからPostgresqlに接続してAnormでCRUD操作するには
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# --- !Ups | |
create table users( | |
id SERIAL, | |
name varchar(100), | |
email varchar(100), | |
password varchar(100), | |
createDate timestamp default current_timestamp, | |
primary key(id)); | |
# --- !Downs | |
drop table users; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# --- !Ups | |
create table post ( | |
id SERIAL, | |
userId INTEGER not null, | |
title varchar(100) not null, | |
body text, | |
createDate timestamp default current_timestamp, | |
primary key(id), | |
foreign key(userId) references users(id) | |
); | |
# --- !Downs | |
drop table post; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import play.core.StaticApplication | |
new StaticApplication(new java.io.File(".")) | |
import play.api.db._ | |
import play.api.Play.current | |
import anorm._ | |
DB.withConnection { implicit c => | |
val result: Boolean = SQL("Select 1").execute() | |
println("result =" + result) | |
} | |
//usersテーブルにレコードを1件登録 | |
DB.withConnection { implicit c => | |
val count = SQL( | |
""" | |
insert into users(name,email,password) | |
values({name},{email},{password}) | |
""" | |
).on('name -> "taro", | |
'email -> "taro@classmethod.jp", | |
'password -> "taropass").executeInsert() | |
println("count = " + count) | |
} | |
//id:1のレコードを更新 | |
DB.withConnection { implicit c => | |
val count = SQL( | |
""" | |
update users set name={name} where id={id} | |
""" | |
).on('name -> "hanako", 'id -> 6).executeUpdate() | |
println("updateCount = " + count) | |
} | |
//id:1のレコードを削除 | |
DB.withConnection { implicit c => | |
val count: Int = SQL( | |
""" | |
delete from users where id = {id} | |
""" | |
).on('id -> 1).executeUpdate() | |
println("deleteCount = " + count) | |
} | |
// Postgresのplaydbにinsert | |
insert into users values(1,'taro','taro@classmethod.jp','taropass',now()); | |
insert into users values(2,'hanako','hanako@classmethod.jp','hanakopass',now()); | |
insert into users 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()); | |
// Stream APIを使用してユーザーの一覧を取得してリストとして返す | |
val selectQuery = SQL("select * from users") | |
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 | |
} | |
import anorm.SqlParser._ | |
// 取得できた値の型が「List[Int~String~String]」となっており、このままでは使いにくい | |
val result:List[Int~String~String] = DB.withConnection { implicit c => | |
SQL("select * from users").as( int("id") ~ str("name") ~ str("email") * ) | |
} | |
// flatten関数を適用し、タプル(Tuple)型のリストに変換 | |
val result = DB.withConnection { implicit c => | |
SQL("select * from users").as( int("id") ~ str("name") ~ str("email") map(flatten) * ) | |
} | |
// 取得結果を任意のクラスのリストにする | |
// emailの値に応じてクラスの型を切り替えています | |
case class CmEmp(name:String) | |
case class Person(name:String) | |
val result = DB.withConnection { implicit c => | |
SQL("select * from users").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) | |
} *) | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment