SQLite from Clojure

I thought this was cute, and it's the entire reason I started this blog a few minutes ago. The first function here is a pretty ditzy port of some Java code to make sure the SQLite JDBC driver is working. The second is some much lispier code that does the same thing.

Here's the Java code (from http://www.zentus.com/sqlitejdbc/)

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();
  }
}

And here's the two Clojure versions of it.

(import '(java.sql DriverManager SQLException))
(use 'clojure.contrib.sql)
(defn direct-port []
(Class/forName "org.sqlite.JDBC")

(let [conn (DriverManager/getConnection "jdbc:sqlite:test.db")
      stat (doto (.createStatement conn)
             (.executeUpdate "drop table if exists people")
             (.executeUpdate "create table people (name, occupation);"))
      prep (doto (.prepareStatement conn "insert into people values (?, ?);")
             (.setString 1 "Gandhi")
             (.setString 2 "politics")
             (.addBatch)
             (.setString 1 "Turing")
             (.setString 2 "computers")
             (.addBatch)
             (.setString 1, "Wittgenstein")
             (.setString 2, "smartypants")
             (.addBatch))]
  (.setAutoCommit conn false)
  (.executeBatch prep)
  (.setAutoCommit conn true)
  (let [results (.executeQuery stat "select * from people")]
    (loop []
      (when (.next results)
        (println "name =" (.getString results "name"))
        (println "job  ="  (.getString results "occupation"))
        (recur))))
  (.close conn)))

(defn contrib-sql []
(with-connection {:classname   "org.sqlite.JDBC"
                  :subprotocol "sqlite"
                  :subname     "test.db"}
  (try
    (drop-table :people)
    (catch SQLException e))
  (create-table :people [:name :text] [:occupation :text])
  (insert-values :people
    [:name          :occupation]
    ["Gandhi"       "politics"]
    ["Turing"       "computers"]
    ["Wittgenstein" "smartypants"])
  (with-results results "select * from people"
    (doseq [record results]
      (println "name =" (:name record))
      (println "job  =" (:occupation record))))))

(println "Java-y version") (direct-port)
(println "Lispy version")  (contrib-sql)

Note that you have to have the SQLite driver installed for these examples to work - you can find some form of it and instructions on how to install it here.