Sql.scala

package zentrope.sql {

  import java.sql.SQLException
  
  class Settings (
    val url : String,
    val user: String,
    val pass: String,
    val driver: String
  )
  
  private object ConnectionPool {

    import java.sql._
    import com.mchange.v2.c3p0.ComboPooledDataSource
    
    private var dataSource: Option[ComboPooledDataSource] = None
    private var settings: Option[Settings] = None

    private def createDataSource(): Unit = {

      settings match {
        case None => {
          throw new SQLException("No connection settings available.")
        }
        
        case Some(props) => {
          val ds = new ComboPooledDataSource()
          
          ds.setDriverClass(props.driver)
          ds.setUser(props.user)
          ds.setPassword(props.pass)
          ds.setJdbcUrl(props.url)
          
          ds.setInitialPoolSize(4)
          ds.setMaxPoolSize(10)
          ds.setMaxIdleTime(7200)
          
          dataSource = Some(ds)
        }
      }
    }
    
    private def getDataSource(): ComboPooledDataSource = {
      dataSource match {
        case None =>
          createDataSource() ;
          return dataSource.get
        
        case Some(ds) =>
          return ds
      }
    }
    
    def setSettings(s: Settings) {
      settings match {
        case None =>
          settings = Some(s)
        case Some(setting) =>
          ;
      }
    }
    
    def getConnection(): Connection =
      getDataSource().getConnection()
  }
  
  // ------------------------------------------------------------------------

  class Row() {
  
    private var data: Map[String, AnyRef] = Map.empty
    
    override def toString(): String = {
      data.toString
    }
    
    private[sql] def +=(pair: Tuple2[String, AnyRef]) {
      data = data + ((pair._1, pair._2))
    }
    
    private def get[T](name: String, kind: T): T = {
      data.get(name) match {
        case None =>
          throw new SQLException("No value for '" + name + "' column.")
        case Some(value) =>
          value.asInstanceOf[T]
      }
    }
    
    def getString(name: String): String = {
      get(name, "")
    }
    
    def getLong(name: String): Long = {
      get(name, 0L)
    }
    
    def getInteger(name: String): Int = {
      get(name, 0)
    }
  }
  
  // ------------------------------------------------------------------------

  class Sql(connection: java.sql.Connection) {
    
    import java.sql._
    
    def this() =
      this(ConnectionPool.getConnection())

    private def warnIfExceptional(func: => Unit) {
      try { func }
      
      catch { 
        case (th: Throwable) =>
          println("WARN: " + th.toString)
      }
    }
    
    private def cleanUp[T](resource: Option[T])(func: (T)=>Unit) = resource match {
      // Method for cleaning up resources. Really, it just makes
      // the cleanup code easier to read.
      
      case None => ;
      case Some(resource) => warnIfExceptional { func(resource) }
    }
    
    private def metadata(rs: ResultSet): Map[String, String] = {
      val metadata = rs.getMetaData()
      val columns = metadata.getColumnCount()
      var columnData = Map[String, String]()
      for (i <- 1.to(columns)) {
        val name = metadata.getColumnName(i)
        val kind = metadata.getColumnTypeName(i)
        columnData = columnData + ((name, kind))
      }
      columnData
    }
    
    private def doQuery(sql: String, params: List[Any], func: Option[(Row) => Any]): Int = {
      
      // var conn: Option[Connection] = None
      var stmt: Option[PreparedStatement] = None
      var rs: Option[ResultSet] = None

      try {
        // conn = Some(ConnectionPool.getConnection())
        // connection.setAutoCommit(true)
      
        stmt = Some(connection.prepareStatement(sql))
      
        for (i <- 1.to(params.size))
          stmt.get.setObject(i, params(i-1))
      
        func match {
          
          // If no function, assume query is an update.

          case None => {
            return stmt.get.executeUpdate()
          }
          
          // If we've got a function, assume the query
          // returns useful results.

          case Some(handler) => {
            rs = Some(stmt.get.executeQuery())
      
            if (rs == None)
              throw new SQLException("No result set.")
      
            val columnData = metadata(rs.get)
      
            while (rs.get.next()) {
      
              var data = new Row()
      
              columnData foreach { case (name, kind) =>
                data += ((name, rs.get.getObject(name)))
              }
      
              handler(data)
            }
            
            return 0
            
          }
        }
      }
      
      catch {
        case (th: Throwable) =>
          throw new SQLException(th)
      }
      
      finally {
        cleanUp(rs) { rs => rs.close }
        cleanUp(stmt) { stmt => stmt.close }
        // cleanUp(conn) { conn => conn.close }
      }
    }
    
    def execute(sql: String): Int = {
      doQuery(sql, List.empty, func = None)
    }
    
    def execute(sql: String, params: List[Any]): Int = {
      doQuery(sql, params, func = None)
    }

    def foreach(sql: String, params: List[Any])(func: (Row) => Unit): Unit = {
      doQuery(sql, params, func = Some(func))
    }

    def foreach(sql: String)(func: (Row) => Unit): Unit = {
      doQuery(sql, List.empty, func = Some(func))
    }
    
    def collect(sql: String)(func: (Row) => Any) : List[Any] = {
      collect(sql, List.empty) { row => func(row) }
    }
    
    def collect(sql: String, params: List[Any])(func: (Row) => Any) : List[Any] = {
      var data: List[Any] = List.empty
      foreach(sql, params) { row =>
        data = func(row) :: data
      }
      data
    }
    
    def rows(sql: String, params: List[Any]): List[Row] = {
      var rows: List[Row] = List.empty
      foreach(sql, params) { row =>
        rows = row :: rows 
      }
      
      rows
    }
    
    private def connAction(name: String)(func: => Unit): Unit = {
      try {
        func
      }
      catch {
        case (th: Throwable) => {
          println("WARN: Unable to " + name + " connection. " + th.toString)
        }
      }
    }
    
    def commit() {
      connAction("commit") { connection.commit() }
    }

    def rollback() {
      connAction("rollback") { connection.rollback() }
    }
    
    def close() {
      connAction("close") { connection.close() }
    }
    
    def setAutoCommit(value: Boolean) {
      connAction("setAutoCommit") { connection.setAutoCommit(value) }
    }
    
    override def finalize() {
      println("finalize on " + this)
      try {
        connection.close()
      }
      
      catch {
        case (th: Throwable) => {
          println("ERROR: [finalize] " + th)
        }
      }
    }
  }
  
  // ------------------------------------------------------------------------
  
  /**
  * The Sql Object is the preferred mechanism for interacting with an SQL
  * Database. The companion Sql Class provides methods for use and are 
  * safest when wrapped by withTransaction and withConnection.
  */
  
  object Sql {
    
    /**
    * Seed the connection pool system with information about
    * how to connect to the database.
    * 
    * @param  settings  A container with settings filled in.
    */
    
    def init(settings: Settings) = {
      val lock: AnyRef = new Object()
      
      lock.synchronized {
        ConnectionPool.setSettings(settings)
      }
    }
    
    /**
    * Provides func with a single autoCommiting JDBC connection wrapper (Sql)
    * such that each method representing an SQL statement will execute in
    * its own transaction (i.e., autocommit == true).
    * 
    * @param  func  A closure which takes an Sql object.
    */
    
    def withConnection (func: (Sql) => Unit): Unit = {
      val sql = new Sql()
      try {
        sql.setAutoCommit(true)

        func(sql)
      }

      catch {
        case (th: Throwable) => {
          throw new SQLException(th)
        }
      }

      finally {
        sql.close()
      }
    }
    
    /**
    * Provides func with a single non-autoCommitting JDBC connection wrapper
    * (Sql) such that all the methods called on the wrapper will be committed
    * in a single transaction, or rolled back if Exceptions are thrown (i.e,
    * autocommit == false).
    * 
    * @param  func  A closure which takes an Sql object.
    */

    def withTransaction (func: (Sql) => Unit): Unit = {
      val sql = new Sql()
      try {
        sql.setAutoCommit(false)
        
        func(sql)
        
        sql.commit()
      }
      
      catch {
        case (th: Throwable) => {
          sql.rollback()
          throw new SQLException(th)
        }
      }
      
      finally {
        sql.close()
      }
    }
  }
 
}

TestSql.scala

package zentrope.testsql {
  
  import zentrope.sql._
  
  object Main {
    
    def getSettings(): Settings = {
      new Settings(
        user="root",
        pass="",
        url="jdbc:mysql://localhost:3306",
        driver="com.mysql.jdbc.Driver")
    }
    
    def main(args: Array[String]) = {
      println("hello.sql")
      
      // Only once per application.
      Sql.init(getSettings())

      Sql.withConnection { sql =>
        sql.execute("drop database if exists scala")
        sql.execute("create database if not exists scala")

        sql.execute("""
          create table if not exists scala.data (
            id bigint auto_increment primary key,
            name varchar(50),
            value int
          )
          """)

        1.to(100) foreach { i =>
          val query = "insert into scala.data (name, value) values (?, ?)"
          val params = List("name." + i, i)
          sql.execute(query, params)
  			}

        sql.foreach("select * from scala.data") { row => 
          println(row)
        }

        val data = sql.collect("select * from scala.data") { row => 
          row.getLong("id") 
        }
        println(data)
        
      }
    }
  }
  
}

More playing around with Scala. This time, an attempt at a simple Sql library interface. This is something you could drop in your app, but that isn't really strong enough to be a full fledged Scala library, mainly because I think you'd want to use the various iterable traits and so on. (Then again, maybe not, because SQL is all about side effects.)

Personally, I like the above approach because it doesn't hide the SQL domain specific language, doesn't use some O/R mapping paradigm (which all seem way more troublesome to me than straight-ahead SQL).

Anyway, the TestSql.scala file is the main example of what it would look like to use such a library.