ScalikeJDBC powerful SQLSyntaxSupport example

1_preparing_data.scala

import scalikejdbc._
import scalikejdbc.SQLInterpolation._

// load settings
Class.forName("org.hsqldb.jdbc.JDBCDriver")
ConnectionPool.singleton("jdbc:hsqldb:mem:test", "", "")
GlobalSettings.loggingSQLAndTime = LoggingSQLAndTimeSettings(enabled = true, logLevel = 'info)

implicit val session = AutoSession

// preparing data
sql"create table groups(id bigint primary key not null, name varchar(255), owner_id bigint not null)".execute.apply()
sql"create table members(id bigint primary key not null, name varchar(255), group_id bigint)".execute.apply()

Seq((1,"Japan Scala Users Group", 2),(2,"Japan Java Users Group", 3)).foreach { case (id, name, ownerId) => 
  sql"insert into groups values (${id}, ${name}, ${ownerId})".update.apply()
}
Seq((1,"Alice",1),(2,"Bob",null),(3,"Chris",2),(4,"Denny",1)).foreach { case (id, name, groupId) => 
  sql"insert into members values (${id}, ${name}, ${groupId})".update.apply()
}

2_entities.scala

// pleaese use :paste on REPL

case class Group(id: Long, name: String, members: Seq[Member] = Nil)
case class Member(id: Long, name: String, groupId: Option[Long] = None, group: Option[Group] = None)

object Group extends SQLSyntaxSupport[Group] {
  override val tableName = "groups"
  
  def apply(g: ResultName[Group])(rs: WrappedResultSet) = new Group(id = rs.long(g.id), name = rs.string(g.name))
}

object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"

  // Group object will load column names from database meta data, but it's also possible to define columns
  override val columns = Seq("id", "name", "group_id")

  // using type dynamic, of course it's also possible to pass string value like rs.long(m.c("id"))
  def apply(m: ResultName[Member])(rs: WrappedResultSet) = new Member(
    id = rs.long(m.c("id")), name = rs.string(m.c("name")), groupId = rs.longOpt(m.c("group_id"))
  )
  
  def opt(m: ResultName[Member])(rs: WrappedResultSet) = rs.longOpt(m.id).map(_ => Member(m)(rs))

  def apply(m: ResultName[Member], g: ResultName[Group])(rs: WrappedResultSet) = new Member(
    id = rs.long(m.id),
    name = rs.string(m.name),
    groupId = rs.longOpt(m.groupId),
    group = rs.longOpt(g.id).map(id => new Group(id, rs.string(g.name)))
  )
}

case class OwnedGroup(id: Long, name: String, ownerId: Long, owner: Owner)
case class Owner(id: Long, name: String)

object OwnedGroup extends SQLSyntaxSupport[OwnedGroup] {
  override val tableName = "groups"
  
  def apply(g: ResultName[OwnedGroup], o: ResultName[Owner])(rs: WrappedResultSet) = {
    new OwnedGroup(id = rs.long(g.id), name = rs.string(g.name), ownerId = rs.long(g.ownerId), owner = Owner(o)(rs))
  }
}

object Owner extends SQLSyntaxSupport[Owner] {
  override val tableName = "members"
  override val columns = Seq("id", "name")

  def apply(o: ResultName[Owner])(rs: WrappedResultSet) = new Owner(id = rs.long(o.id), name = rs.string(o.name))
}

3_query_examples.scala

// ----------------------------
// outer join query example
// ----------------------------

val (m, g) = (Member.syntax, Group.syntax)
val members: List[Member] = sql"""
  select 
    ${m.result.*}, ${g.result.*}
  from 
    ${Member.as(m)} left join ${Group.as(g)} on ${m.groupId} = ${g.id}
""".
  map(Member(m.resultName, g.resultName)).
  list.
  apply()

/*
  [Executed SQL]
   select members.id as i_on_members, members.name as n_on_members, members.group_id as gi_on_members, groups.id as i_on_groups, groups.name as n_on_groups, groups.owner_id as oi_on_groups from members left join groups on members.group_id = groups.id;
 */

members.foreach(println)

/*
Member(1,Alice,Some(1),Some(Group(1,Japan Scala Users Group)))
Member(2,Bob,None,None)
Member(3,Chris,Some(2),Some(Group(2,Japan Java Users Group)))
Member(4,Denny,Some(1),Some(Group(1,Japan Scala Users Group)))
 */

// ----------------------------
// one-to-many query example
// ----------------------------

val (g, m) = (Group.syntax("g"), Member.syntax("m"))
val groups: List[Group] = sql"""
  select 
    ${g.result.*}, ${m.result.*}
  from 
    ${Group.as(g)} left join ${Member.as(m)} on ${g.id} = ${m.groupId}
""".
  one(Group(g.resultName)).
  toMany(Member.opt(m.resultName)).
  map { (group, members) => group.copy(members = members) }.
  list.apply()

/*
  [Executed SQL]
   select g.id as i_on_g, g.name as n_on_g, g.owner_id as oi_on_g, m.id as i_on_m, m.name as n_on_m, m.group_id as gi_on_m from groups g left join members m on g.id = m.group_id;
  */

groups.foreach(println)

/*
Group(1,Japan Scala Users Group,Vector(Member(1,Alice,Some(1),None), Member(4,Denny,Some(1),None)))
Group(2,Japan Java Users Group,Vector(Member(3,Chris,Some(2),None)))
 */

// ----------------------------
// inner join query example
// ----------------------------

val (g, o) = (OwnedGroup.syntax("g"), Owner.syntax("o"))
val groups: List[OwnedGroup] = sql"""
  select 
    ${g.result.*}, ${o.result.*}
  from 
    ${OwnedGroup.as(g)} inner join ${Owner.as(o)} on ${g.ownerId} = ${o.id}
""".
  map(OwnedGroup(g.resultName, o.resultName)).
  list.
  apply()

/*
  [Executed SQL]
   select g.id as i_on_g, g.name as n_on_g, g.owner_id as oi_on_g, o.id as i_on_o, o.name as n_on_o from groups g inner join members o on g.owner_id = o.id;
 */

groups.foreach(println)

/*
OwnedGroup(1,Japan Scala Users Group,2,Owner(2,Bob))
OwnedGroup(2,Japan Java Users Group,3,Owner(3,Chris))
 */

build.sbt

resolvers += "sonatype snapshots" at "https://oss.sonatype.org/content/repositories/releases"

scalaVersion := "2.10.0"

libraryDependencies ++= Seq(
  "com.github.seratch" %% "scalikejdbc" % "1.4.7",
  "com.github.seratch" %% "scalikejdbc-interpolation" % "1.4.7",
  "org.hsqldb" % "hsqldb" % "[2,)",
  "org.slf4j" % "slf4j-simple" % "[1.7,)"
)