QueryDSL


Introduction


Since version 1.6.0, Query DSL is newly added. It’s much readable and type-safe DSL.

val id = 123
val (m, g) = (GroupMember.syntax("m"), Group.syntax("g"))
val groupMember = sql"""
  select
    ${m.result.*}, ${g.result.*}
  from
    ${GroupMember.as(m)} left join ${Group.as(g)} on ${m.groupId} = ${g.id}
  where
    ${m.id} = ${id}
  """
    .map(GroupMember(m, g)).single.apply()

You can re-write this SQLInterpolation code by using QueryDSL as follow.

We believe this code is much simpler and quite understandable.

val id = 123
val (m, g) = (GroupMember.syntax("m"), Group.syntax("g"))
val groupMember = withSQL {
  select.from(GroupMember as m).leftJoin(Group as g).on(m.groupId, g.id)
    .where.eq(m.id, id)
}.map(GroupMember(m.resultName, g.resultName)).single.apply()

More examples are

val m = Member.syntax("m")
val ids: List[Long] = sql"select ${m.result.id} from ${Member.as(m)} where ${m.groupId} = 1"
val members = sql"select ${m.result.*} from ${Member.as(m)}".map(Member(m)).list.apply()

will be like this:

val m = Member.syntax("m")
val ids: List[Long] = withSQL { select(m.result.id).from(Member as m).where.eq(m.groupId, 1) }
  .map(rs => rs.long(m.resultName.id)).list.apply()
val members = withSQL { select.from(Member as m) }.map(Member(m.resultName)).list.apply()
// or select.all(m).from(Member as m)

QueryDSL Reference


FYI: You can find some example in QueryDSL’s test code:

scalikejdbc-interpolation/src/test/scala/scalikejdbc/QueryInterfaceSpec.scala

sqls is alias for SQLSyntax object. Methods that are defined on object SQLSyntax is available everywhere.

scalikejdbc-core/src/main/scala/scalikejdbc/interpolation/SQLSyntax.scala


And/Or conditions


val o = Order.syntax("o")
val orders = withSQL {
  select
    .from(Order as o)
    .where
    .eq(o.productId, 123)
    .and
    .isNotNull(o.orderedAt)
  }.map(Order(o)).list.apply()

// select o.id as i_on_o, o.customer_id ci_on_o, o.product_id as pi_on_o, o.ordered_at as oa_on_o
// from orders o where o.product_id = ? and o.ordered_at is not null
val orders = withSQL {
  select(o.result.id).from(Order as o)
    .where
    .eq(o.productId, 123)
    .or
    .isNull(o.customerId)
  }.map(Order(o)).list.apply()

// select o.id as i_on_o from orders o where o.product_id = ? or o.customer_id is null

Adding round bracket is like this:

val ids = withSQL {
  select(o.result.id).from(Order as o)
    .where.isNotNull(o.accountId)
    .and.withRoundBracket { _.eq(o.productId, 1).or.eq(o.accountId, 2) }
  }.map(_.int(1)).list.apply()

// select o.id as i_on_o from orders o
// where o.account_id is not null 
// and (o.product_id = ? or o.account_id = ?)

or simply using #append:

select(o.result.id).from(Order as o).where.isNotNull(o.accountId)
  .and.append(sqls"(${o.productId} = ${pid} or ${o.accountId} = ${aid})")

Join queries


val orders: List[Order] = withSQL {
  select
    .from(Order as o)
    .innerJoin(Product as p).on(o.productId, p.id)
    .leftJoin(Account as a).on(o.accountId, a.id)
    .where.eq(o.productId, 123)
    .orderBy(o.id).desc
    .limit(4)
    .offset(0)
  }.map(Order(o, p, a)).list.apply()

// select o.*, p.*, a.* from orders o inner join products p on o.product_id = p.id
// left join accounts a on o.account_id = a.id
// where o.product_id = ? order by o.id desc limit ? offset ?

.on(o.productId, p.id) is simply converted to sqls"${o.productId} = ${p.id}". If you need to write more complex condition to join tables, use sqls directly.

  .innerJoin(Product as p).on(sqls"${o.proudctId} = ${p.id} and ${o.deletedAt} is not null")

  // inner join products p on o.product_id = p.id and o.deleted_at is not null

Dynamic query building


def findOrder(id: Long, accountRequired: Boolean) = withSQL {
  select
    .from[Order](Order as o)
    .innerJoin(Product as p).on(o.productId, p.id)
    .map { sql =>
      if (accountRequired) sql.leftJoin(Account as a).on(o.accountId, a.id) else sql
    }.where.eq(o.id, 13)
  }.map { rs =>
    if (accountRequired) Order(o, p, a)(rs) else Order(o, p)(rs)
  }.single.apply()

// or

def findOrder(id: Long, accountRequired: Boolean) = withSQL {
  select
    .from[Order](Order as o)
    .innerJoin(Product as p).on(o.productId, p.id)
    .leftJoin(if (accountRequired) Some(Account as a) else None).on(o.accountId, a.id)
    .where.eq(o.id, 13)
  }.map { rs =>
    if (accountRequired) Order(o, p, a)(rs) else Order(o, p)(rs)
  }.single.apply()


// select o.*, p.*(, a.*) from orders o inner join products p on o.product_id = p.id
// (left join accounts a on o.account_id = a.id)
// where o.id = ?

If you just need to append optional conditions, toAndConditionOpt, and toOrConditionOpt are useful.

val (productId, accountId) = (Some(1), None)
val ids = withSQL {
  select(o.result.id).from(Order as o)
    .where(sqls.toAndConditionOpt(
      productId.map(id => sqls.eq(o.productId, id)),
      accountId.map(id => sqls.eq(o.accountId, id))
    ))
    .orderBy(o.id)
}.map(_.int(1)).list.apply()

// select o.id as i_on_o from orders o
// where o.product_id = ? (and o.account_id = ?)
// order by o.id

// productId: Some, accountId: None -> where o.product_id = ? 
// productId: None, accountId: Some -> where o.account_id = ?

in clause


val inClauseResults = withSQL {
  select.from(Order as o).where.in(o.id, Seq(1, 2, 3))
}.map(Order(o)).list.apply()

// select * from orders o where o.id in (?, ?, ?)

exists/notExists clause


withSQL {
  select(a.id).from(Account as a)
    .where.exists(select.from(Order as o).where.eq(o.accountId, a.id))
}.map(_.int(1)).list.apply()

// select a.id from accounts a
// where exists (select * from orders o where o.account_id = a.id)

It’s also possible to pass sqls values.

withSQL {
  select(a.id).from(Account as a)
    .where.notExists(sqls"select ${o.id} from ${Order as o} where ${o.accountId} = ${a.id}")
}.map(_.int(1)).list.apply()

// select a.id from accounts a
// where not exists (select o.id from orders o where o.account_id = a.id)

between


withSQL {
  select(o.result.id).from(Order as o).where.between(o.id, 13, 22)
}.map(_.int(1)).list.apply()

// select o.id as i_on_o from orders o where o.id between ? and ?

distinct count


import sqls.{ distinct, count }
val productCount = withSQL {
  select(count(distinct(o.productId))).from(Order as o)
}.map(_.int(1)).single.apply().get

// select count(distinct o.product_id) from orders o

group by queries


import sqls.count
withSQL {
  select(o.accountId, count).from(Order as o)
    .where.isNotNull(o.accountId)
    .groupBy(o.accountId)
}.map(rs => (rs.int(1), rs.int(2))).list.apply()

// select o.account_id, count(1) from orders o
// where o.account_id is not null
// group by o.account_id

union, unionAll queries


withSQL {
  select(a.id).from(Account as a)
    .union(select(p.id).from(Product as p))
    //.unionAll(select(p.id).from(Product as p))
}.map(_.int(1)).list.apply()

// select a.id from accounts a
// union 
// select p.id from products p

Sub-queries


import SQLSyntax.{ sum, gt }
val x = SubQuery.syntax("x").include(o, p)
val preferredClients: List[(Int, Int)] = withSQL {
  select(sqls"${x(o).accountId} id", sqls"${sum(x(p).price)} as amount")
    .from { select.all(o, p).from(Order as o).innerJoin(Product as p).on(o.productId, p.id).as(x) }
    .groupBy(x(o).accountId)
    .having(gt(sum(x(p).price), 300))
    .orderBy(sqls"amount")
  }.map(rs => (rs.int("id"), rs.int("amount"))).list.apply()

// select x.account_id id, sum(x.price) as amount 
// from (select o.*, p.* from orders o inner join products p on o.product_id = p.id) x
// group by x.account_id
// having sum(x.price) > ?
// order by amount

Insert


import java.time.ZonedDateTime

withSQL {
  insert.into(Member).values(1, "Alice", ZonedDateTime.now)
}.update.apply()

// insert into members values (?, ?, ?)

withSQL {
  val m = Member.column
  insert.into(Member).namedValues(
    m.id -> 1,
    m.name -> "Alice",
    m.createdAt -> ZonedDateTime.now
  )
}.update.apply()

// insert into members (id, name, created_at) values (?, ?, ?)

Or applyUpdate is much simpler. But in some cases, applyUpdate causes compilation errors since Scala 2.10.1. This is not an issue of ScalikeJDBC. If you suffered it, use withSQL { }.update.apply() instead.

import java.time.ZonedDateTime

applyUpdate { insert.into(Member).values(2, "Bob", ZonedDateTime.now) }

// insert into members values (?, ?, ?)

val c = Member.column
applyUpdate {
  insert.into(Member).columns(c.id, c.name, c.createdAt).values(2, "Bob", ZonedDateTime.now)
}

// insert into members (id, name, created_at) values (?, ?, ?)

Insert select


case class Product(id: Int, name: Option[String], price: Int)
object Product extends SQLSyntaxSupport[Product]
case class LegacyProduct(id: Option[Int], name: Option[String], price: Int)
object LegacyProduct extends SQLSyntaxSupport[LegacyProduct]

val lp = LegacyProduct.syntax("lp")
withSQL {
  insert.into(Product).select(_.from(LegacyProduct as lp).where.isNotNull(lp.id))
}.update.apply()

// insert into products select lp.* from legacy_products lp where lp.id is not null

Delete


withSQL {
  delete.from(Member).where.eq(Member.column.id, 123)
}.update.apply()

// delete from members where id = ?

Update


import java.time.ZonedDateTime

withSQL {
  update(Member).set(
    Member.column.name -> "Chris",
    Member.column.updatedAt -> ZonedDateTime.now
  ).where.eq(Member.column.id, 2)
}.update.apply()

// update members set name = ?, updated_at = ?
// where id = ?

Avoiding Method Name Conflict


For example, if your code already uses select, insert or update as method name, name confliction occurs.

def insert(e: Member)(implicit session: DBSession) {
  withSQL {
    insert // compilation error
      .into(Member).values(e.id, e.name)
  }.update.apply()
}

Use QueryDSL prefix or insertInto, deleteFrom.

def insert(e: Member)(implicit session: DBSession) {
  withSQL {
    QueryDSL.insert.into(Member).values(e.id, e.name)
  }.update.apply()
}

def insert(e: Member)(implicit session: DBSession) {
  withSQL {
    insertInto(Member).values(e.id, e.name)
  }.update.apply()
}

def delete(id: Long)(implicit session: DBSession) {
  withSQL {
    deleteFrom(Member).where.eq(Member.column.id, id)
  }.update.apply()
}

If this webpage has a typo or something wrong, Please report or fix it. How?