Pure Danger Tech


Learning Clojure #11: JDBC metadata in Clojure

03 Mar 2010

Going back a couple weeks I was porting some Java code to Clojure. This Java code was very simply pulling all column names in the database. Here’s the full code in all it’s awesomeness:

package d2rq.validation;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DatabaseMetadataScraper implements MetadataScraper {
    private final String url;
    private final String user;
    private final String pw;
    private final DatabaseMetadataReferences refs;
    public DatabaseMetadataScraper(String driver, String url, String user,
            String pw) {
        try {
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Driver class not found: " + driver);
        this.url = url;
        this.user = user;
        this.pw = pw;        
        this.refs = new DatabaseMetadataReferences("Database: " + url);
    public DatabaseMetadataReferences readMetadata() {
        try {
            Connection conn = DriverManager.getConnection(url, user, pw);
            DatabaseMetaData dbmd = conn.getMetaData();
            ResultSet rs = dbmd.getColumns(null, null, null, "%");
            while(rs.next()) {
                String table = rs.getString("TABLE_NAME");
                String column = rs.getString("COLUMN_NAME");
                String full = table + "." + column;
                refs.addColumn(full, full);
            return refs;
        } catch(SQLException e) {
            throw new RuntimeException(e);

The astute Java reader will note that I’ve cut a lot of corners on error handling and the like here as well, so this isn’t more than a quick hack (but still clocks in at 64 lines).

There is a provided api for JDBC in the clojure.contrib.sql library but the available docs for doing things like accessing DatabaseMetadata are pretty light. I ended up with something like this:

(ns foo
    (:use [clojure.contrib.sql]))
(defn db-spec 
    "Create database specification map from inputs"
    [driver url user pw]
    (let [url-parts (.split #":" url)]
        { :classname driver
          :subprotocol (second url-parts)
          :subname (str-join ":" (rest (rest url-parts)))
          :user user 
          :password pw }))

(defn get-column-names
    "Take database spec, return all column names from the database metadata"
    (with-connection db
        (into #{}
            (map #(str (% :table_name) "." (% :column_name))
                (resultset-seq (->
                    (.getColumns nil nil nil "%")))))))

I did this in my first few days of Clojure and the only thing here that puzzled me was the use of ->. I filed that one and came back to it later. The -> is a macro that (as per the docs) is said to “thread the expression through the forms”. Looking at the code and being familiar with what it’s doing, I conceptually get it but I found this great post was much more helpful.

The -> macro will evaluate the first form, then evaluate each form after that and “stitch” the result of the previous form into the second argument. So this example will:

  1. Evaluate (connection)
  2. Evaluate (.getMetadata <result of #1>)
  3. Evaluate (.getColumns <result of #2> nil nil nil “%”)

That reminds me of the .. macro for Java interop. The .. macro lets you make a chained series of Java calls where the “this” in each call is the result of the previous call. For example: (.. (System/getProperties) (getProperty "java.class.path") (length)) will tell you the length of your classpath, should you have a burning need to know. This is equivalent to (.length (.getProperty (System/getProperties) "java.class.path")). The latter is certainly a more LISP-ish way to say this but the former is more Java-like and also more readable to me. I think the -> macro is similarly useful in increasing readability.