ScalikeJDBC Tutorial for Beginners

DEPRECATED

The latest guide in 2018:
https://speakerdeck.com/seratch/skinny-orm-beginners-guide

Transcript

1. ScalikeJDBC Tutorial for Beginners @seratch Kazuhiro Sera
2. Table of contents What’s this? ConnectionPool Implicit Session SQL Syntax Query DSL More Features Get Started Now
3. What’s this?
4. ScalikeJDBC http://git.io/scalikejdbc A tidy SQL-based DB access library for Scala developers. ...SQL-based?
5. 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()
6. 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.
7. ConnectionPool
8. 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.
9. 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 =>
10. 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)
11. Implicit Session
12. Go implicitly DBSession represents java.sql.Connection with a transaction(if exists). #apply() requires DBSession implicitly.
13. 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() }
14. Automatic AutoSession creates & closes an ad- hoc session if absent. query: read-only session update: auto-commit session
15. 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” }
16. SQL Syntax
17. 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.
18. 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
19. 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
20. 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) }
21. 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() }
22. 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
23. 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() } <console>:28: error: Member#fullNamee not found. Expected fields are #id, #fullName, #createdAt, #deletedAt. m.fullNamee ^
24. Query DSL
25. Uniqueness - DBSession inspired by Querulous - SQL(String) inspired by Anorm - sql”...” inspired by Slick Query DSL is surely ScalikeJDBC’s original way!
26. 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
27. 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)
28. 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()
29. 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()
30. 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
31. More and More - insert select - groupBy, having - in, exists, like, between - union, unionAll - withRoundBracket { ... } - dynamic(And|Or)Conditions { ... } In detail: QueryDSLFeature.scala QueryInterfaceSpec.scala
32. More Features
33. 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]”
34. 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) } }
35. 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”) } }
36. 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) } }
37. 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
38. Get Started Now
39. More info Examples devteam-app example QueryInterfaceSpec.scala Reference Wiki Pages Users Group ScalikeJDBC Users Group ScalikeJDBC Users Group Japan
40. Enjoy! Just write SQL and get things done! git.io/scalikejdbc