Simplifying the JDBC

The title of this post may raise some brows, but I mean what I say. You see, I do a lot of little batch scripting with sqlite databases, and since Scala is my scripting language of choice (as of this writing), I interact with the JDBC API quite a bit. In my situation, it would be silly to import all the 3rd party libraries that make it simple (the big ones being Spring JDBC and Hibernate). Allow me to show you what I mean...

For those of you who haven't ever interacted with JDBC, or forgotten completely, allow me to enlighten you. The following was taken from sqlite JDBC homepage:

import java.sql.*;

public class Test {
  public static void main(String[] args) throws Exception {
      Class.forName("org.sqlite.JDBC");
      Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
      Statement stat = conn.createStatement();
      stat.executeUpdate("drop table if exists people;");
      stat.executeUpdate("create table people (name, occupation);");
      PreparedStatement prep = conn.prepareStatement(
          "insert into people values (?, ?);");

      prep.setString(1, "Gandhi");
      prep.setString(2, "politics");
      prep.addBatch();
      prep.setString(1, "Turing");
      prep.setString(2, "computers");
      prep.addBatch();
      prep.setString(1, "Wittgenstein");
      prep.setString(2, "smartypants");
      prep.addBatch();

      conn.setAutoCommit(false);
      prep.executeBatch();
      conn.setAutoCommit(true);

      ResultSet rs = stat.executeQuery("select * from people;");
      while (rs.next()) {
          System.out.println("name = " + rs.getString("name"));
          System.out.println("job = " + rs.getString("occupation"));
      }
      rs.close();
      conn.close();
  }
}

They do a little bit of everything here. Create a table, drop tables, inserts, and querying. Now let me show the the same program in question rewritten in Scala using my DB API. The actual code behind the API will come last.

import calicodb.Adapter

object Main extends Application {
  val sqlitedb = Adapter("org.sqlite.JDBC", "jdbc:sqlite:test.db")
  sqlitedb.exec("drop table if exists people;")(_.execute())
  sqlitedb.exec("create table people (name, occupation);")(_.execute())

  // Get our people ready
  val people = List(("Gandhi", "politcs"), ("Turing", "computers"), ("Wittgenstein", "smartypants"))

  sqlitedb.exec("insert into people values (?, ?);") { prep =>
    for(p <- people; val (name, occupation) = p) {
      prep.setString(1, name)
      prep.setString(2, occupation)
      prep.addBatch()
    }
    prep.executeBatch()
  }

  val rtn = sqlite.query("select * from people;")(_.executeQuery())
  println(rtn)
  // List[Map[String, Any]](Map[String, Any](name -> Gandhi, occupation -> politics) , ...
}

In my version, you should notice a few things different. I'm providing some space between us, because I want you to try to find as many of them as you can before I tell you. There's one in particular, that should catch your eye ;)

Okay. Ready? Let's start from the top. The big one: there's no close() statments anywhere! This is surely a recipe for disaster. Don't worry, it's not as bad as you think. You'll see in a second.

Other than that, I'm using an Adpater to interact with the sqlite database, but it seems like I'm messing around with a PreparedStatment directly. Because I am! I guess now is as good a time as any to look at the underlying code.

/** 
* For an update, insert, create, delete.
*/  
def exec(execute: String)(fun: PreparedStatement => Any) = { 
    val conn = getConnection
    try {
        val stm = conn.prepareStatement(execute)
        conn.setAutoCommit(false)
        fun(stm)
        conn.commit()
        stm.close()
    } catch {
        case _ => conn.rollback()
    } finally {
        conn.close()
    }
}

From my last post, you should be able to tell that I'm a big fan of general functions being passed around, and allowing the user to say what's up. Now it's clearer that my second parameter to this method is a function that takes a PreparedStatement, and does some work. Closure support allowed me to do batch commits based on a List of data. Fair enough. Good enough for me. Now there's this thing of queries returning a List of Map's. Must be some kind of magic. Let's take a look at that one now.

def query(q: String) (fun: PreparedStatement => ResultSet): List[Map[String, Any]] = {
    val conn = getConnection

    try {
        val stm = conn.prepareStatement(q)
        val results: List[Map[String, Any]] = fun(stm)
        stm.close()
        results
    } finally {
        conn.close()
    }
}

Ah, same as before... except you're assiging a ResultSet to a List[Map[String, Any]] !? That compiles!? Yes. I really left that in there to show off another feature of Scala: implicit conversions. There's another function that does the bulk of the conversion work.

/** Convert an ugly jdbc result set to a collection that can be used in code*/
implicit def rs2friendly(rs: ResultSet): List[Map[String, Any]] = {
 val meta = rs.getMetaData
 val names = for(i <- 1 to meta.getColumnCount) yield (meta.getColumnLabel(i))

 def buildr(names: List[String], rs: ResultSet): List[Map[String, Any]] = {
   rs.next match {
     case true => {
       val tuples = for(name <- names) yield (name, rs.getObject(name))
       val map = Map() ++ tuples
       map :: buildr(names, rs)
     }
     case _ => Nil
   }
 }

 val rtn = buildr(names.toList, rs)
 // Safe: close the result set since we're through
 rs.close()
 rtn
}

And there you have it. The Adapter does some nice things. It does all of it's nice things without relying on 3rd party warez. And it's low level and general enough to use for a lot of JDBC drivers out there. Heck making this script compatible with MySQL is easy enough.

// MySQL
val mysqldb = Adapter("com.mysql.jdbc.Driver", "jdbc:mysql://host:port/db")

That's all I have for today. I hope it was interesting enough :P

-- Philip