Operations


Query API


There are various query APIs. All of them (single, first, list and foreach) will execute java.sql.PreparedStatement#executeQuery().


Single / Optional Result for Query


single returns matched single row as an Option value. If unexpectedly multiple rows are returned, runtime exception will be thrown.

import scalikejdbc._

val id = 123

// simple example
val name: Option[String] = DB readOnly { implicit session =>
  sql"select name from emp where id = ${id}".map(rs => rs.string("name")).single.apply()
}

// defined mapper as a function
val nameOnly = (rs: WrappedResultSet) => rs.string("name")
val name: Option[String] = DB readOnly { implicit session =>
  sql"select name from emp where id = ${id}".map(nameOnly).single.apply()
}

// define a class to map the result
case class Emp(id: String, name: String)
val emp: Option[Emp] = DB readOnly { implicit session =>
  sql"select id, name from emp where id = ${id}"
    .map(rs => Emp(rs.string("id"), rs.string("name"))).single.apply()
}

// QueryDSL
object Emp extends SQLSyntaxSupport[Emp] {
  def apply(e: ResultName[Emp])(rs: WrappedResultSet): Emp = new Emp(id = rs.get(e.id), name = rs.get(e.name))
}
val e = Emp.syntax("e")
val emp: Option[Emp] = DB readOnly { implicit session =>
  withSQL { select.from(Emp as e).where.eq(e.id, id) }.map(Emp(e.resultName)).single.apply()
}

You can learn about QueryDSL in detail here:

/documentation/query-dsl


First Result from Multiple Results


first returns the first row of matched rows as an Option value.

val name: Option[String] = DB readOnly { implicit session =>
  sql"select name from emp".map(rs => rs.string("name")).first.apply()
}

val e = Emp.syntax("e")
val name: Option[String] = DB readOnly { implicit session =>
  withSQL { select(e.result.name).from(Emp as e) }.map(_.string(e.name)).first.apply()
}

List Results


list returns matched multiple rows as scala.collection.immutable.List.

val name: List[String] = DB readOnly { implicit session =>
  sql"select name from emp".map(rs => rs.string("name")).list.apply()
}

val e = Emp.syntax("e")
val name: Option[String] = DB readOnly { implicit session =>
  withSQL { select(e.result.name).from(Emp as e) }.map(_.string(e.name)).list.apply()
}

Foreach Operation


foreach allows you to make some side-effect in iterations. This API is useful for handling large ResultSet.

DB readOnly { implicit session =>
  sql"select name from emp".foreach { rs =>
    out.write(rs.string("name"))
  }
}

val e = Emp.syntax("e")
DB readOnly { implicit session =>
  withSQL { select(e.name).from(Emp as e) }.foreach { rs =>
    out.write(rs.string(e.name))
  }
}

Setting JDBC fetchSize


For instance, the PostgreSQL JDBC driver does infinite(!) caching for result sets if fetchSize is set to 0 (the default) and this causes memory problems.

http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#setFetchSize-int-

You can specify JDBC fetchSize as follows since version 2.0.5.

val e = Emp.syntax("e")
DB readOnly { implicit session =>
  sql"select name from emp"
    .fetchSize(1000)
    .foreach { rs => out.write(rs.string("name")) }
}

Or it’s also fine to set fetchSize to scalikejdbc.DBSession.

val (e, c) = (Emp.syntax("e"), Cmp.syntax("c"))

DB readOnly { implicit session =>
  session.fetchSize(1000)

  withSQL { select(e.name).from(Emp as e) }.foreach { rs =>
    out.write(rs.string(e.name)
  }
  withSQL { select(c.name).from(Cmp as c) }.foreach { rs =>
    out.write(rs.string(c.name))
  }
}

Implementing Custom Extractor


In some cases, you might need to implement your own custom extractor. This can be useful for testing out queries.

This example shows how to preserve null values in a result set.

def toMap(rs: WrappedResultSet): Map[String, Any] =  {
  (1 to rs.metaData.getColumnCount).foldLeft(Map[String, Any]()) { (result, i) =>
    val label = rs.metaData.getColumnLabel(i)
    Some(rs.any(label)).map { nullableValue => result + (label -> nullableValue) }.getOrElse(result)
  }
}

sql"select * from emp".map(rs => toMap(rs)).single.apply()

Using ParameterBinder


ParameterBinder allows you to customize how parameters should be bound to PreparedStatement object inside ScalikeJDBC.

The following example shows how to use PreparedStatement#setBinaryStream when binding InputStream value to PreparedStatement.

sql"create table blob_example (id bigint, data blob)").execute.apply()

val bytes = scala.Array[Byte](1, 2, 3, 4, 5, 6, 7)
val in = new ByteArrayInputStream(bytes)
val bytesBinder = ParameterBinder(
  value = in,
  binder = (stmt: PreparedStatement, idx: Int) => stmt.setBinaryStream(idx, in, bytes.length)
)

sql"insert into blob_example (data) values (${bytesBinder})").update.apply()

If you need to enable some ParameterBinder implicitly, you need to define ParameterBinderFactory for it.

case class MemberId(value: Int)
case class Member(id: MemberId, name: String)
object Member extends SQLSyntaxSupport[Member] {}

implicit val memberIdPbf = ParameterBinderFactory[MemberId] {
  value => (stmt, idx) => stmt.setInt(idx, value.value)
}

val m = Member.syntax("m")
val memberId = MemberId(123)
withSQL {
  select.from(Member as m).where.eq(m.id, memberId) // `memberIdPbf` properly affects here
}.map(Member(_)).single.apply()

// CAUTION:
// The SQL Interpolation style doesn't work with ParameterBinderFactory while it works with ParameterBinder
sql"select id from member where id = $memberId" // will bind `memberId` as an object

Using TypeBinder


TypeBinder[A] is a type class to extract type A values from ResultSet. To extract a value as a specific type value, you need to define the type class for it.

Let’s say you need to bind a long value as MemberId type as below.

import scalikejdbc._

implicit val session = AutoSession

// prepare data
sql"create table member (id bigint not null primary key, name varchar(100))".execute.apply()
sql"insert into member values (1, 'Alice')".update.apply()
sql"insert into member values (2, 'Bob')  ".update.apply()
sql"insert into member values (3, 'Chris')".update.apply()

case class MemberId(id: Long)
case class Member(id: MemberId, name: String)

// compilation error here
val ids: Seq[MemberId] = sql"select * from member".map(_.get[MemberId]("id")).list.apply()

// <console>:24: error: could not find implicit value for evidence parameter of type scalikejdbc.TypeBinder[MemberId]
//         val ids: Seq[MemberId] = sql"select * from member".map(_.get[MemberId]("id")).list.apply()
//                                                                               ^

Since your MemberId type is not available by default, you’ve got a compilation error. You can see all predef types by default here.

As such, you need to define your own TypeBinder for MemberId type as below.

// define TypeBinder[MemberId]
implicit val memberIdTypeBinder: TypeBinder[MemberId] = new TypeBinder[MemberId] {
  def apply(rs: ResultSet, label: String): MemberId = MemberId(rs.getLong(label))
  def apply(rs: ResultSet, index: Int): MemberId = MemberId(rs.getLong(index))
}

// WrappedResultSet#get[MemberId]("id") works with above implicit definition
val ids: Seq[MemberId] = sql"select id from member".map(_.get[MemberId]("id")).list.apply()
// ids: Seq[MemberId] = List(MemberId(1), MemberId(1), MemberId(2), MemberId(3))

Of course, it’s also possible to simply deal with this case without TypeBinder.

val ids: Seq[MemberId] = sql"select id from member".map(rs => MemberId(rs.get("id"))).list.apply()

Binders to define both ParameterBinderFactory and TypeBinder


Using Binders is very convenient when you define both ParameterBinderFactory and TypeBinder.

// object Binders {
//   def of[A](f: Any => A)(g: A => (PreparedStatement, Int) => Unit): Binders[A]
// }

import scalikejdbc._

case class MemberId(value: Int)

// If you paste this code on the REPL, use :paste
{
  Binders.of[MemberId]
  // For TypeBinder (f: Any => A)
  {
    case null => null
    case v: Float => MemberId(v.toInt)
    case v: Double => MemberId(v.toInt)
    case n: Number => MemberId(n.intValue)
    case v => MemberId(v.toString.toInt)
  }
  // For ParameterBinderFactory (A => (PreparedStatement, Int) => Unit)
  { v =>
    (ps, idx) =>
    ps.setInt(idx, v.value)
  }
}

Using joda-time library


You need to add the optional module which supports joda-time library.

libraryDependencies += "org.scalikejdbc" %% "scalikejdbc-joda-time" % "3.3.0"

Also, you explicitly need to import ParameterBinderFatory and TypeBinder.

import scalikejdbc._
// If you need ParameterBinderFactory for joda-time classes
import scalikejdbc.jodatime.JodaParameterBinderFactory._
// If you need TypeBinder for joda-time classes
import scalikejdbc.jodatime.JodaTypeBinder._
import java.time._

case class Group(
  id: Long,
  name: Option[String],
  createdAt: ZonedDateTime)

object Group extends SQLSyntaxSupport[Group] {
  def apply(g: SyntaxProvider[Group])(rs: WrappedResultSet): Group = apply(g.resultName)(rs)
  def apply(g: ResultName[Group])(rs: WrappedResultSet): Group = new Group(
    id = rs.get(g.id),
    name = rs.get(g.name),
    createdAt = rs.get(g.createdAt)
    // (or) createdAt = rs.get[DateTime](g.createdAt)
    // (or) createdAt = rs.jodaDateTime(g.createdAt)
  )
}

Update API


update executes java.sql.PreparedStatement#executeUpdate().

import scalikejdbc._

DB localTx { implicit session =>
  sql"""insert into emp (id, name, created_at) values (${id}, ${name}, current_timestamp)"""
    .update.apply()
  val newId = sql"insert into emp (name, created_at) values (${name}, current_timestamp)"
    .updateAndReturnGeneratedKey.apply()
  sql"update emp set name = ${newName} where id = ${newId}".update.apply()
  sql"delete emp where id = ${newId}".update.apply()
}

val column = Emp.column
DB localTx { implicit s =>
  withSQL {
    insert.into(Emp).namedValues(
      column.id -> id,
      column.name -> name,
      column.createdAt -> sqls.currentTimestamp)
   }.update.apply()

  val newId: Long = withSQL {
    insert.into(Emp).namedValues(column.name -> name, column.createdAt -> sqls.currentTimestamp)
  }.updateAndReturnGeneratedKey.apply()

  withSQL { update(Emp).set(column.name -> newName).where.eq(column.id, newId) }.update.apply()
  withSQL { delete.from(Emp).where.eq(column.id, newId) }.update.apply()
}


Execute API


execute executes java.sql.PreparedStatement#execute().

DB autoCommit { implicit session =>
  sql"create table emp (id integer primary key, name varchar(30))".execute.apply()
}

// QueryDSL doesn't support DDL yet.

Batch API


batch and batchByName executes java.sql.PreparedStatement#executeBatch().

import scalikejdbc._

DB localTx { implicit session =>
  val batchParams: Seq[Seq[Any]] = (2001 to 3000).map(i => Seq(i, "name" + i))
  sql"insert into emp (id, name) values (?, ?)".batch(batchParams: _*).apply()
}

DB localTx { implicit session =>
  sql"insert into emp (id, name) values ({id}, {name})"
    .batchByName(Seq(Seq('id -> 1, 'name -> "Alice"), Seq('id -> 2, 'name -> "Bob")):_*)
    .apply()
}

val column = Emp.column
DB localTx { implicit session =>
  val batchParams: Seq[Seq[Any]] = (2001 to 3000).map(i => Seq(i, "name" + i))
  withSQL {
    insert.into(Emp).namedValues(column.id -> sqls.?, column.name -> sqls.?)
  }.batch(batchParams: _*).apply()
}

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