ScalikeJDBC

吴同

2023-12-01

Just write SQL and get things done!

ScalikeJDBC is a tidy SQL-based DB access library for Scala that naturally wraps JDBC and provides easy-to-use APIs.

ScalikeJDBC is practical and production-ready. Use this library for your real projects.

官网：http://scalikejdbc.org/

First example

Put the above dependencies into your build.sbt and run sbt console now.

import scalikejdbc._

// initialize JDBC driver & connection pool
Class.forName("org.h2.Driver")
ConnectionPool.singleton("jdbc:h2:mem:hello", "user", "pass")

// ad-hoc session provider on the REPL
implicit val session = AutoSession

// table creation, you can run DDL by using #execute as same as JDBC
sql"""
create table members (
  id serial not null primary key,
  name varchar(64),
  created_at timestamp not null
)
""".execute.apply()

// insert initial data
Seq("Alice", "Bob", "Chris") foreach { name =>
  sql"insert into members (name, created_at) values (${name}, current_timestamp)".update.apply()
}

// for now, retrieves all data as Map value
val entities: List[Map[String, Any]] = sql"select * from members".map(_.toMap).list.apply()

// defines entity object and extractor
import java.time._
case class Member(id: Long, name: Option[String], createdAt: ZonedDateTime)
object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def apply(rs: WrappedResultSet) = new Member(
    rs.long("id"), rs.stringOpt("name"), rs.zonedDateTime("created_at"))
}

// find all members
val members: List[Member] = sql"select * from members".map(rs => Member(rs)).list.apply()

// use paste mode (:paste) on the Scala REPL
val m = Member.syntax("m")
val name = "Alice"
val alice: Option[Member] = withSQL {
  select.from(Member as m).where.eq(m.name, name)
}.map(rs => Member(rs)).single.apply()

How did it go? If you’d like to know more details or practical examples, see documentation.

Quick Tour

Using only the Scala standard API & SQL

Library users don’t need to learn so many library-specific rules or conventions. If you’re already familiar with Scala’s standard library APIs and basic SQLs, that much should be enough.

val name = "Alice"
// implicit session represents java.sql.Connection
val memberId: Option[Long] = DB readOnly { implicit session =>
  sql"select id from members where name = ${name}" // don't worry, prevents SQL injection
    .map(rs => rs.long("id")) // extracts values from rich java.sql.ResultSet
    .single                   // single, list, traversable
    .apply()                  // Side effect!!! runs the SQL using Connection
}

See in detail: /documentation/operations

Type-safe DSL

Since version 1.6, QueryDSL is available. It’s a SQL-like and type-safe DSL to build DRY SQLs.

Here is an example:

val (p, c) = (Programmer.syntax("p"), Company.syntax("c"))

val programmers: Seq[Programmer] = DB.readOnly { implicit session =>
  withSQL {
    select
      .from(Programmer as p)
      .leftJoin(Company as c).on(p.companyId, c.id)
      .where.eq(p.isDeleted, false)
      .orderBy(p.createdAt)
      .limit(10)
      .offset(0)
  }.map(Programmer(p, c)).list.apply()
}

See in detail: /documentation/query-dsl

Test code: src/test/scala/scalikejdbc/QueryInterfaceSpec.scala

Flexible transaction control

ScalikeJDBC provides several APIs for session/transaction control.

DB autoCommit { implicit session => … }
DB localTx { implicit session => … }
DB withinTx { implicit session => … }
DB readOnly { implicit session => … }

Here is an example which re-uses methods in both simple invocation and transactional operations.

object Product {
  def create(name: String, price: Long)(implicit s: DBSession = AutoSession): Long = {
    sql"insert into products values (${name}, ${price})"
      .updateAndReturnGeneratedKey.apply() // returns auto-incremeneted id
  }

  def findById(id: Long)(implicit s: DBSession = AutoSession): Option[Product] = {
    sql"select id, name, price, created_at from products where id = ${id}"
      .map { rs => Product(rs) }.single.apply()
  }
}

Product.findById(123) // borrows connection from pool and gives it back after execution

DB localTx { implicit session => // transactional session
  val id = Product.create("ScalikeJDBC Cookbook", 200) // within transaction
  val product = Product.findById(id) // within transaction
}

See in detail: /documentation/transaction

Useful Query Inspections

By default, ScalikeJDBC shows you what SQL is executed and where it is. We believe that is quite useful for debugging your apps. Logging only slow queries in production, but it also helps you.

[debug] s.StatementExecutor$$anon$1 - SQL execution completed

  [Executed SQL]
   select id, name from users where email = 'alice@example.com'; (3 ms)

  [Stack Trace]
    ...
    models.User$.findByEmail(User.scala:26)
    controllers.Projects$$anonfun$index$1$$anonfun$apply$1$$anonfun$apply$2.apply(Projects.scala:20)
    controllers.Projects$$anonfun$index$1$$anonfun$apply$1$$anonfun$apply$2.apply(Projects.scala:19)
    controllers.Secured$$anonfun$IsAuthenticated$3$$anonfun$apply$3.apply(Application.scala:88)

See in detail: /documentation/query-inspector

Testing Support

Testing support which provides the following functionalities for ScalaTest and specs2.

Rollback automatically after each test
Testing with fixtures

See in detail: /documentation/testing

Reverse Engineering

You can easily get Scala code from existing database by using ScalikeJDBC’s reverse engineering tool.

sbt "scalikejdbcGen [table-name (class-name)]"

e.g.

sbt "scalikejdbcGen company"
sbt "scalikejdbcGen companies Company"

See in detail: /documentation/reverse-engineering

Play Framework Support

You can use ScalikeJDBC with Play framework 2 seamlessly. We promise you that it becomes more productive when used with scalikejdbc-mapper-generator.

See in detail: /documentation/playframework-support

Reactive Streams Support

ScalikeJDBC provides the Publisher interface of Reactive Streams to subscribe a stream from a database query.

See in detail: /documentation/reactivestreams-support