ScalikeJDBC sample

ScalikeJDBC_intoroduction.md

Schema

CREATE TABLE users (
    id    INTEGER       PRIMARY KEY,
    name  VARCHAR(256)  NOT NULL
)
CREATE TABLE groups (
    id    INTEGER       PRIMARY KEY,
    name  VARCHAR(256)  NOT NULL
)
CREATE TABLE user_group (
    user_id   INTEGER NOT NULL,
    gourp_id  INTEGER NOT NULL,
    PRIMARY KEY(user_id, group_id)
)

Prepare

import scalikejdbc._
import scalikejdbc.SQLInterpolation._

Models

case class User(id: Int, name: String)

object User extends SQLSyntaxSupport[User] {
  val tableName = "users"
  def apply(u: ResultName[User])(rs: WrappedResultSer): User = User(
    id = rs.int(u.id), 
    name = rs.string(u.name)
  )
}

case class Group(id: Int, name: String)

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

object UserGroup extends SQLSyntaxSupport[Nothing] {
  val tableName = "user_group"
}

Simple Example

code

val id = 10
val u = User.syntax
val user: Option[User] = 
  sql"""
    SELECT
      ${u.result.*}
    FROM 
      ${User as u}
    WHERE
      ${u.id} = ${id}
  """
    .map(User(u.resultName))
    .single
    .apply()

query

SELECT
  users.id as id__on__users,
  users.name as name__on__users,
FROM
  users
WHERE
  users.id = ?

Binding

10

IN CASE

code

val ids = Seq(10, 20, 30, 40)
val u = User.syntax
val users: Seq[User] = 
  sql"""
    SELECT
      ${u.result.*}
    FROM
      ${User as u}
    WHERE
      ${u.id} IN (${ids})
  """
    .map(User(u.resultName))
    .list
    .apply()

query

SELECT
  users.id as id__on__users,
  users.name as name__on__users,
FROM
  users
WHERE
  users.id IN (?, ?, ?, ?)

Binding

10, 20, 30, 40

Join Example

code

val (u, g, ug) = (User.syntax("u"), Group.syntax("g"), UserGroup.syntax("ug"))
val users: Seq[(User, Seq[Group])] = 
  sql"""
    SELECT
      ${u.result.*},
      ${g.result.*}
    FROM
      ${User as u}
      INNER JOIN ${UserGroup as ug} ON ${ug.userId} = ${u.id}
      INNER JOIN ${Group as g} ON ${g.id} = ${ug.groupId}
  """
    .one(User(u.resultName))
    .toMany(rs => Some(Group(g.resultName)(rs)))
    .list
    .apply()

query

SELECT
  u.id   as id__on__u,
  u.name as name__on__u,
  g.id   as id__on__g,
  g.name as name__on__g
FROM
  users u
  INNER JOIN user_group ug ON ug.user_id = u.id
  INNER JOIN groups g ON g.id = ug.group_id

SubQuery Example

val (u, g, ug) = (User.syntax("u"), Group.syntax("g"), UserGroup.syntax("ug"))
val sq = SubQuery.suntax("sq", u.resultName)
val limit = 10
val users: Seq[(User, Seq[Group])] = 
  sql"""
    SELECT
      ${sq.result.*},
      ${g.result.*}
    FROM
      (
        SELECT 
          ${u.result.*}
        FROM
          ${User as u}
        ORDER BY
          ${u.id}
        LIMIT
          ${limit}
      ) ${SubQuery as sq}
      INNER JOIN ${UserGroup as ug} ON ${ug.userId} = ${sq.id}
      INNER JOIN ${Group as g} ON ${g.id} = ${ug.groupId}
  """
    .one(User(u.resultName))
    .toMany(rs => Some(Group(g.resultName)(rs)))
    .list
    .apply()

query

SELECT
  sq.id__on__u   as id__on__u__on__sq,
  sq.name__on__u as name__on__u__on__sq,
  g.id           as id__on__g,
  g.name         as name__on__g
FROM
  (
    SELECT
      u.id   as id__on__u,
      u.name as name__on__u
    FROM
      users u
    ORDER BY
      u.id
    LIMIT
      ?
  ) sq
  INNER JOIN user_group ug ON ug.user_id = sc.id__on__u
  INNER JOIN groups g ON g.id = ug.group_id

Bindings

10