Skip to content

Instantly share code, notes, and snippets.

@seratch

seratch/ScalikeJDBC_intoroduction.md

Forked from gakuzzzz/ScalikeJDBC_intoroduction.md
Last active December 14, 2015 10:49
Show Gist options
  • Save seratch/5074852 to your computer and use it in GitHub Desktop.
Save seratch/5074852 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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: WrappedResultSet): 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: WrappedResultSet): 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 i_on_users,
  users.name as n_on_users,
FROM
  users
WHERE
  users.id = ?

Binding

10

IN CLAUSE

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 i_on_users,
  users.name as n_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 i_on_u,
  u.name as n_on_u,
  g.id   as i_on_g,
  g.name as n_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(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
  sq.i_on_u   as i_on_u_on_sq,
  sq.n_on_u   as n_on_u_on_sq,
  g.id        as i_on_g,
  g.name      as n_on_g
FROM
  (
    SELECT
      u.id   as i_on_u,
      u.name as n_on_u
    FROM
      users u
    ORDER BY
      u.id
    LIMIT
      ?
  ) sq
  INNER JOIN user_group ug ON ug.user_id = sc.i_on_u
  INNER JOIN groups g ON g.id = ug.group_id

Bindings

10

seratchs-iMac:~ seratch$ seratchs-iMac:~ seratch$ seratchs-iMac:~ seratch$ seratchs-iMac:~ seratch$ seratchs-iMac:~ seratch$ seratchs-iMac:~ seratch$ cat foo.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: WrappedResultSet): 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: WrappedResultSet): 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 i_on_users,
  users.name as n_on_users,
FROM
  users
WHERE
  users.id = ?

Binding

10

IN CLAUSE

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 i_on_users,
  users.name as n_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 i_on_u,
  u.name as n_on_u,
  g.id   as i_on_g,
  g.name as n_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(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
  sq.i_on_u   as i_on_u_on_sq,
  sq.n_on_u   as n_on_u_on_sq,
  g.id        as i_on_g,
  g.name      as n_on_g
FROM
  (
    SELECT
      u.id   as i_on_u,
      u.name as n_on_u
    FROM
      users u
    ORDER BY
      u.id
    LIMIT
      ?
  ) sq
  INNER JOIN user_group ug ON ug.user_id = sc.i_on_u
  INNER JOIN groups g ON g.id = ug.group_id

Bindings

10
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment