takezoe / scala-jdbc

Better JDBC wrapper for Scala

Github

scala-jdbc

Better JDBC wrapper for Scala.

libraryDependencies += "com.github.takezoe" %% "scala-jdbc" % "1.0.5"

You can use better-jdbc by adding a following import statements:

import com.github.takezoe.scala.jdbc._

Select

Extract values from ResultSet:

val users: Seq[(Int, String)] = DB.autoClose(conn) { db =>
  db.select("SELECT * FROM USERS"){ rs =>
    (rs.getInt("USER_ID"), rs.getString("USER_NAME"))
  }
}

Retrieve a first record:

val user: Option[(Int, String)] = DB.autoClose(conn) { db =>
  db.selectFirst(sql"SELECT * FROM USERS WHERE USER_ID = $userId"){ rs =>
    (rs.getInt("USER_ID"), rs.getString("USER_NAME"))
  }
}

Map ResultSet to the case class:

case class User(userId: Int, userName: String)

val users: Seq[User] = DB.autoClose(conn) { db =>
  db.select("SELECT USER_ID, USER_NAME FROM USERS", User.apply _)
}

Update

DB.autoClose(conn) { db =>
  db.update(sql"INSERT INTO USERS (USER_ID, USER_NAME) VALUES ($userId, $userName)")
}

Transaction

DB.autoClose(conn) { db =>
  db.transaction {
    db.update(sql"DELETE FROM GROUP WHERE USER_ID = $userId")
    db.update(sql"DELETE FROM USERS WHERE USER_ID = $userId")
  }
}

Large data

Process large data using scan method:

DB.autoClose(conn) { db =>
  db.scan("SELECT * FROM USERS"){ rs =>
    println(rs.getString("USER_NAME"))
  }
}

SQL Validation (Experimental)

scala-jdbc provides sqlc macro that validates a given SQL. You can use it instead of sql string interpolation.

db.selectFirst(sqlc(s"SELECT * FROM USERS WHERE USER_ID = $userId")){ rs =>
  (rs.getInt("USER_ID"), rs.getString("USER_NAME"))
}

This macro checks the sql syntax using JsqlParser. When a given SQL is invalid, errors are reported in compile time.