takezoe/scala-jdbc

Better JDBC wrapper for Scala

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"))
  }
}

scala-jdbc's Issues

Run SQL with LIMIT 0 in compile time for validation

Check table and column existence in ON clause of JOIN

Update error

Table in postgres:

CREATE TABLE articles (
id serial PRIMARY KEY,
published bigint NOT NULL,
author varchar (50) NOT NULL,
title varchar (100) NOT NULL,
text varchar (5000) NOT NULL
);

java.lang.UnsupportedOperationException: Unsupported type: scala.collection.mutable.WrappedArray$ofRef
at com.github.takezoe.scala.jdbc.TypeMapper.set(TypeMapper.scala:19)
at com.github.takezoe.scala.jdbc.DB.$anonfun$execute$2(DB.scala:291)
at com.github.takezoe.scala.jdbc.DB.$anonfun$execute$2$adapted(DB.scala:290)
at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:59)
at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:52)
at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48)
at com.github.takezoe.scala.jdbc.DB.$anonfun$execute$1(DB.scala:290)
at com.github.takezoe.scala.jdbc.IOUtils$.using(IOUtils.scala:28)
at com.github.takezoe.scala.jdbc.DB.execute(DB.scala:289)
at com.github.takezoe.scala.jdbc.DB.update(DB.scala:20)
at app.model.Article$.$anonfun$add$1(Articles.scala:21)
at app.model.Article$.$anonfun$add$1$adapted(Articles.scala:20)
at com.github.takezoe.scala.jdbc.DB.autoClose(DB.scala:267)
at com.github.takezoe.scala.jdbc.DB$.autoClose(DB.scala:13)
at app.model.Article$.add(Articles.scala:20)

case class Article(val id: Long, val ms: Long, val author: String, val title: String, val text: String)

def add(a: Article) = DB.autoClose(getConnection) { db =>
db.update(sql"INSERT INTO articles (published,author,title,text) VALUES (${a.ms},${a.author},${a.title},${a.text})")
}

scala> val a = Article(0l, 1321313l, "author", "title", "text")
a: app.model.Article = Article(0,1321313,author,title,text)

scala> sql"INSERT INTO articles (published,author,title,text) VALUES (${a.ms},${a.author},${a.title},${a.text})"
res0: com.github.takezoe.scala.jdbc.package.SqlTemplate = SqlTemplate(INSERT INTO articles (published,author,title,text) VALUES (?,?,?,?),WrappedArray(WrappedArray(1321313, author, title, text)))