JDBC with Scala

Prerequisites

import java.sql.Connection
case class Book(title: String)

Fixed queries

val getBooks: Connection => List[Book] =
  { c =>
    val q = "SELECT TITLE FROM BOOK"
    val stmt = c.createStatement
    val rs = stmt.executeQuery(q)

    def _books(acc: List[Book]): List[Book] =
      if (rs.next()) {
        _books(Book(rs.getString("TITLE")) :: acc)
      } else {
        stmt.close
        acc
      }

    _books(Nil)
  }

Prepared statements

val initDb: Connection => Unit =
  { c =>
    val s = "CREATE TABLE IF NOT EXISTS BOOK(TITLE VARCHAR(256) NOT NULL)"
    val stmt = c.createStatement
    stmt.executeUpdate(s)
    stmt.close
  }

def addBook(b: Book): Connection => Unit =
  { c =>
    val s = "INSERT INTO BOOK (TITLE) VALUES (?)"
    val stmt = c.prepareStatement(s)
    stmt.setString(1, b.title)
    stmt.executeUpdate
    stmt.close
  }

Composition

implicit class Queries[A](val g: Connection => A) {

  def map[B](f: A => B): Connection => B =
    { c => f(g(c)) }

  def flatMap[B](f: A => Connection => B): Connection => B =
    { c => f(g(c)).g(c) }

}

Example

val booksQ =
  for {
    _     <- initDb
    _     <- addBook(Book("Surely You're Joking, Mr. Feynman!"))
    books <- getBooks
  } yield books

Class.forName("org.h2.Driver")

val c = java.sql.DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "")

val books = booksQ(c)
c.close

println(s"Books: ${books}") // Books: List(Book(Surely You're Joking, Mr. Feynman!))

Demo

This file is literate Scala, and can be run using Codedown:

/***
libraryDependencies += "com.h2database" % "h2" % "1.4.178"
*/
$ curl https://earldouglas.com/posts/scala/jdbc.md |
  codedown scala > scala-jdbc.scala
$ sbt -Dsbt.main.class=sbt.ScriptMain scala-jdbc.scala
Books: List(Book(Surely You're Joking, Mr. Feynman!))