build.sbt

scalaVersion := "2.10.1"

libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick"  % "1.0.1-RC1",
  "com.h2database"      % "h2"     % "1.3.171"
)

Main.scala

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
  }
}