Skip to content

Instantly share code, notes, and snippets.

@kxbmap
Last active December 17, 2015 17:49
Show Gist options
  • Save kxbmap/5648473 to your computer and use it in GitHub Desktop.
Save kxbmap/5648473 to your computer and use it in GitHub Desktop.
色んなSQLをSlickで書くと?
scalaVersion := "2.10.1"
libraryDependencies ++= Seq(
"com.typesafe.slick" %% "slick" % "1.0.1-RC1",
"com.h2database" % "h2" % "1.3.171"
)
import scala.slick.session.Database
import scala.slick.driver.H2Driver.simple._
case class Club(name: String, id: Option[Long] = None)
case class Student(name: String, classroom: String, clubId: Option[Long], id: Option[Long] = None)
trait DAO {
def ddl = Students.ddl ++ Clubs.ddl
object Clubs extends Table[Club]("CLUBS") {
def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
def name = column[String]("NAME")
def * = name ~ id.? <> (Club.apply _, Club.unapply _)
def forInsert = name returning id
def ?? = id.? ~ name.?
}
object Students extends Table[Student]("STUDENTS") {
def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
def name = column[String]("NAME")
def classroom = column[String]("CLASSROOM")
def clubId = column[Option[Long]]("CLUB_ID")
def * = name ~ classroom ~ clubId ~ id.? <> (Student.apply _, Student.unapply _)
def forInsert = name ~ classroom ~ clubId returning id
}
}
object Main extends App {
val dao = new DAO {}
import dao._
// LEFT JOIN + IS NULL
val q1 = for {
(s, c) <- Students leftJoin Clubs.map(_.??) on (_.clubId === _._1)
if c._1.isNull
} yield (s, c)
val q1b = for {
(s, ci) <- Students leftJoin Clubs.map(_.id.?) on (_.clubId === _)
if ci.isNull
} yield s
import scala.slick.jdbc.GetResult
import scala.slick.jdbc.StaticQuery.interpolation
implicit val getStudentResult = GetResult[Student] { r => Student(r.<<, r.<<, r.<<?, r.<<?) }
val q1p = sql"""
SELECT *
FROM Students s
LEFT JOIN Clubs c on (s.club_id = c.id)
WHERE c.id IS NULL
""".as[(Student, Option[String], Option[Long])]
// 相関サブクエリー
val q2 = for {
s <- Students
if (for {
c <- Clubs if s.clubId === c.id
} yield c.id).exists
} yield s
// 複数のカラムでgroup by
val q3 = for {
((ci, cr), ss) <- Students.groupBy(s => (s.clubId, s.classroom))
} yield (ci, cr, ss.length)
Class.forName("org.h2.Driver")
val db = Database.forURL("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1")
db withSession { implicit session: Session =>
println("-" * 80)
ddl.createStatements foreach println
ddl.create
val Seq(c1, c2) =
Clubs.forInsert.insertAll(
"情報処理部",
"学級委員会"
)
// q3のためゆずこは別の組に編入してもらう
Students.forInsert.insertAll(
("櫟井唯", "Hoge組", Some(c1)),
("日向縁", "Hoge組", Some(c1)),
("野々原ゆずこ", "Fuga組", Some(c1)),
("相川千穂", "Hoge組", Some(c2)),
("岡野桂", "Hoge組", None),
("長谷川ふみ", "Hoge組", Some(100))
)
println("-" * 80)
Query(Clubs) foreach println
Query(Students) foreach println
println("-" * 80)
println(q1.selectStatement)
val r1: List[(Student, (Option[Long], Option[String]))] = q1.list()
r1 foreach println
println("-" * 80)
println(q1b.selectStatement)
val r1b: List[Student] = q1b.list()
r1b foreach println
println("-" * 80)
println(q1p.getStatement)
val r1p: List[(Student, Option[String], Option[Long])] = q1p.list()
r1p foreach println
println("-" * 80)
println(q2.selectStatement)
val r2: List[Student] = q2.list()
r2 foreach println
println("-" * 80)
println(q3.selectStatement)
val r3: List[(Option[Long], String, Int)] = q3.list()
r3 foreach println
println("-" * 80)
ddl.dropStatements foreach println
ddl.drop
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment