ScalikeJDBC Tutorial for Beginners @seratch Kazuhiro Sera 

Table of contents What’s this? ConnectionPool Implicit Session SQL Syntax Query DSL More Features Get Started Now 

What’s this? 

ScalikeJDBC http://git.io/scalikejdbc A tidy SQL-based DB access library for Scala developers. ...SQL-based? 

SQL-based import scalikejdbc._, SQLInterpolation._ val id = 123 // Anorm-like API for Scala 2.9 val name = SQL(“select name from company where id = {id}”) .bindByName(‘id -> id).map(rs => rs.string(“name”)).single.apply() // SQL Interpolation since 2.10 val name = sql”select name from company where id = ${id}” .map(rs => rs.string(“name”)).single.apply() // Query DSL val name = withSQL { select(c.name).from(Company as c).where.eq(c.id, id) }.map(rs => rs.string(c.resultName.name)).single.apply() 

Everybody knows SQL We don’t need something new and different from SQL. Maybe, you can write the code on the previous slide immediately, right? Because you already know SQL. 

ConnectionPool 

Easy-to-use CP DB block such as DB.readOnly { ... } borrows a connection from ConnectionPool. Since ConnectionPool is a singleton object, you can easily borrow anywhere. 

ConnectionPool import scalikejdbc._ Class.forName(“org.h2.Driver”) // default connection pool val (url, user, password) = (“jdbc:h2:mem:db”, “sa”, “”) ConnectionPool.singleton(url, user, password) val conn: java.sql.Connection = ConnectionPool.borrow() val names: List[String] = DB readOnly { implicit session => sql”select name from company”.map(_.string(“name”).list.apply() } // named connection pool val poolSettings = new ConnectionPoolSettings(maxSize = 50) ConnectionPool.add(‘secondary, url, user, passsword, poolSettings) NamedDB(‘secondary) readOnly { implicit session => 

DBCP by default By default, only Commons DBCP implementation is provided. It’s also possible to implement your preferred connection pool. (e.g. C3P0, BoneCP) 

Implicit Session 

Go implicitly DBSession represents java.sql.Connection with a transaction(if exists). #apply() requires DBSession implicitly. 

Implicit DBSession import scalikejdbc._, SQLInterpolation._ val id = 123 // DBSession usage val name: Option[String] = DB readOnly { session: DBSession => session.single(“select name from company where id = ?”, id) { rs => rs.string(“name”) } } // SQL API requires implicit DBSession val name: Option[String] = DB readOnly { implicit session => sql”select name from company where id = ${id}” .map(_.string(“name”)).single.apply() } 

Automatic AutoSession creates & closes an ad- hoc session if absent. query: read-only session update: auto-commit session 

Flexible Transaction import scalikejdbc._, SQLInterpolation._ implicit val session = AutoSession sql”select name from company”.map(_.string(“name”)).list.apply() def addCompany(name: String)(implicit s: DBSession = AutoSession) { sql”insert into company values (${name})”.update.apply() } def getAllNames()(implicit s: DBSession = AutoSession): List[String] = { sql”select name from company”.map(_.string(“name”)).list.apply() } val names: List[String] = getAllNames() // read-only session DB localTx { implicit session => addCompany(“Typesafe”) // within a transaction getAllNames() // within a transaction, includes “Typesafe” } 

SQL Syntax 

SQLSyntax? // SQLSyntax = a part of SQL object, will be embedded as-is val c: SQLSyntax = sqls”count(*)” val bobby: String = “Bob%” val query = sql”select ${c} from members where name like ${bobby}” // -> “select count(*) from members where name like ?” A part of SQL object which will be embedded as-is. 

SQLSyntaxSupport SQLSyntaxSupport provides DRY and type safe SQL. - CoC but configurable - Write complex join queries - Using type-dynamic(similar to Ruby’s method-missing) but have compile-time check with macro 

CoC Rules // Scala object case class GroupMember(id: Long, fullName: Option[String] = None) obejct GroupMember extends SQLSyntaxSupport[GroupMember] // DDL create table group_member { id bigint not null primary key, full_name varchar(255) } Simply snake_case’d name 

Customize it // Scala object case class GroupMember(id: Long, fullName: Option[String] = None) obejct GroupMember extends SQLSyntaxSupport[GroupMember] { override val tableName = “group_members” override val nameConverters = Map(“fullName” -> “fullname”) } // DDL create table group_members { id bigint not null primary key, fullname varchar(255) } 

Query & Extracting import scalikejdbc._, SQLInterpolation._ // entity class (Plain Old Scala Object) case class Member(id: Long, fullName: Option[String] = None) // companion object for entity obejct Member extends SQLSyntaxSupport[Member] { def apply(m: ResultName[Member])(rs: WrappedResultSet) = new Member( id = rs.long(m.id), name = rs.stringOpt(m.fullName) ) ) val m = Member.syntax(“m”) val id = 123 val memebr: Option[Member] = DB readOnly { implicit s => sql”select ${m.result.*} from ${Member as m} where ${m.id} = ${id}” .map(Member(m.resultName)).single.apply() } 

result? resultName? val m = Member.syntax(“m”) m.fullName == “m.full_name” m.result.fullName == “m.full_name as fn_on_m” m.resultName.fullName == “fn_on_m” Scala: sql”select ${m.result.fullName} from ${Member as m} where ${m.id} = 123” SQL: “select m.full_name as fn_on_m from member m where m.id = 123” ResultSet extractor: val name = sql”...”.map(rs => rs.string(m.resultName.fullName)).single.apply() // extracting “fn_on_m” from ResultSet 

Type safe dynamic import scalikejdbc._, SQLInterpolation._ val m = Member.syntax(“m”) val id = 123 val memebr: Option[Member] = DB readOnly { implicit s => sql”select ${m.result.fullNamee} from ${Member as m} where ${m.id} = ${id}” .map(Member(m.resultName)).single.apply() } :28: error: Member#fullNamee not found. Expected fields are #id, #fullName, #createdAt, #deletedAt. m.fullNamee ^ 

Query DSL 

Uniqueness - DBSession inspired by Querulous - SQL(String) inspired by Anorm - sql”...” inspired by Slick Query DSL is surely ScalikeJDBC’s original way! 

What’s Query DSL - Just appends SQL parts - Type safe and pragmatic - Easy to understand - Not composable but reusable - Parts from sqls object - Append sqls”...” when needed 

Query DSL examples import scalikejdbc._, SQLInterpolation._ implicit val session = AutoSession val c = Company.syntax(“c”) val id = 123 val company: Option[Company] = withSQL { select.from(Company as c).where.eq(c.id, id) }.map(Company(c.resultName)).single.apply() insert.into(Company).values(123, “Typesafe”) val column = Company.column update(Company).set(column.name -> “Oracle”).where.eq(column.id, 123) delete.from(Company).where.eq(column.id, 123) 

Joins, one-to-x API val programmerWithSkills = withSQL { select .from(Programmer as p) .leftJoin(Company as c).on(p.companyId, c.id) .leftJoin(ProgrammerSkill as ps).on(ps.programmerId, p.id) .leftJoin(Skill as s).(ps.skillId, s.id) .where .eq(p.id, id) .and .isNull(p.deletedAt) } .one(Programmer(p, c)) .toMany(SKill.opt(s)) .map { (pg, skills) => pg.copy(skills = skills) } .single.apply() 

Sub query, Paging val noSkillProgrammers = withSQL { select .from(Programmer as p) .leftJoin(Company as c).on(p.companyId, c.id) .where .notIn(p.id, select(sqls.distinct(ps.programmerId)).from(ProgrammerSkill as ps)) .isNull(p.deletedAt) .limit(10).offset(0) .orderBy(p.id).desc } .map(Programmer(p, c)) .list.apply() 

sqls.xxx, sqls”...” val userId = val orderCount: Long = withSQL { select(sqls.count(sqls.distinct(o.id))) // sqls object .from(Order as o) .innerJoin(Product as p).on(p.id,o.productId) .where .append(sqls”${o.userId} = ${userId}”) // direct SQL embedding } .map(rs => rs.long(1)) .single.apply().get 

More and More - insert select - groupBy, having - in, exists, like, between - union, unionAll - withRoundBracket { ... } - dynamic(And|Or)Conditions { ... } In detail: QueryDSLFeature.scala QueryInterfaceSpec.scala 

More Features 

Code Generator - sbt plugin - Code from existing tables - project/scalikejdbc.properties - Play2 style models and tests In detail: Wiki Page sbt “scalikejdbc-gen [table-name]” 

Testing with ScalaTest import scalikejdbc._, scalatest.AutoRollback import org.scalatest.fixture.FlatSpec class MemberSpec extends FlatSpec with AutoRollback { override def fixture(implicit s: DBSession) { sql”delete from members”.update.apply() Member.create(1, “Alice”) } behavior of “Member” it should “create a new record” in { implicit s => val beforeCount = Member.count Member.create(123, “Brian”) Member.count should equal(before + 1) } } 

Testing with specs2 (1) import scalikejdbc._, specs2.mutable.AutoRollback import org.specs2.mutable.Specification object MemberSpec extends Specification { “Member should create a new record” in new MyAutoRollback { val beforeCount = Member.count Member.create(123, “Brian”) Member.count should equal(before + 1) } } trait MyAutoRollback extends AutoRollback { override def fixture(implicit s: DBSession) { sql”delete from members”.update.apply() Member.create(1, “Alice”) } } 

Testing with specs2 (2) import scalikejdbc._, specs2.AutoRollback import org.specs2.Specification object MemberSpec extends Specification { def is = “Member should create a new record” ! autoRollback().create end } case class autoRollback() extends AutoRollback { override def fixture(implicit s: DBSession) { sql”delete from members”.update.apply() Member.create(1, “Alice”) } def create = this { val beforeCount = Member.count Member.create(123, “Brian”) Member.count should equal(before + 1) } } 

Play2 Support - Integrates with Play2 seamlessly - Read conf/application.conf - Add plugin to conf/play.plugins - FixturePlugin is also available - Great contribution by @tototoshi In detail: Wiki Page 

Get Started Now 

More info Examples devteam-app example QueryInterfaceSpec.scala Reference Wiki Pages Users Group ScalikeJDBC Users Group ScalikeJDBC Users Group Japan 

Enjoy! Just write SQL and get things done! git.io/scalikejdbc