JDBC from Clojure

[Rich Hickey]

I did some playing around with JDBC which led to some improvements to
structs, as well as new functions to-set, distinct, and resultset-seq.
The latter creates a seq of structmaps corresponding to the returned
rows. Here's a session with a Derby demo database:

(import '(java.sql DriverManager Connection PreparedStatement
ResultSet))
(class org.apache.derby.jdbc.EmbeddedDriver)
(def conn (. DriverManager (getConnection
"jdbc:derby:/Users/rich/dev/derby/demo/databases/toursdb")))
(def rs (.. conn (prepareStatement "select * from cities")
(executeQuery)))
(def rset (resultset-seq rs))

(count rset)
-> 87

(. rs (close))

(take 2 rset)

-> ({:city_id 1, :city_name "Amsterdam", :country
"Netherlands", :airport "AMS", :language "Dutch", :country_iso_code
"NL"} {:city_id 2, :city_name "Athens", :country "Greece", :airport
"ATH", :language "Greek", :country_iso_code "GR"})

(distinct (map :country rset))

-> ("Australia" "Philippines" "New Zealand" "Hungary" "Italy"
"Switzerland" "Indonesia" "Singapore" "Afghanistan" "Japan" "Ireland"
"Argentina" "Czech Republic" "Spain" "Poland" "Sweden" "Brazil" "South
Africa" "Greece" "United Kingdom" "Turkey" "Canada" "Morocco"
"Netherlands" "Nigeria" "Lebanon" "Mexico" "Iceland" "Iran" "India"
"China" "Peru" "Venezuela" "Denmark" "France" "Egypt" "Russia" "Kenya"
"Colombia" "Chile" "Pakistan" "Korea, Republic of" "Portugal" "Norway"
"United States")

Now structs maintain the order of their base keys, the struct function
now takes values (but no keys) for the base keys in order, and the old
struct function (which takes key/value pairs, is renamed struct-map.
Here's resultset-seq, which dynamically defines a struct based on the
resultset metadata:

(defn resultset-seq [#^java.sql.ResultSet rs]
(let [rsmeta (. rs (getMetaData))
idxs (range 1 (inc (. rsmeta (getColumnCount))))
keys (map (comp keyword (memfn toLowerCase))
(map (fn [i] (. rsmeta (getColumnName i))) idxs))
row-struct (apply create-struct keys)
row-values (fn [] (map (fn [#^Integer i] (. rs (getObject i))) idxs))
rows (fn []
(when (. rs (next))
(fnseq (apply struct row-struct (row-values)) thisfn)))]
(rows)))

All in SVN rev 615. Have fun!

Rich