Tuesday, December 23, 2008

Using Clojure with groovy.sql.Sql

Clojure can use the groovy.sql.Sql class for easy access to databases. Here is an example:

(import '(groovy.sql Sql))

(defn main []
(let [db (Sql/newInstance "jdbc:oracle:thin:@host:1521:sid"
"username" "password" "oracle.jdbc.driver.OracleDriver")

sql "select * from accounthistory
where entrydate > sysdate - 1
and amount <> 0"]

(dorun (map #(println (format "%13s\t%12s"
(. % (get "ACCOUNTID"))
(. % (get "AMOUNT"))))
(. db (rows sql))))))

(main)

Make sure groovy-all-1.5.4.jar and your database driver (e.g., ojdbc14.jar) are on the Clojure classpath.

"(dorun (map function collection))" is an idiom to prevent unsightly nils from being included in the printing of the rows. map executes the anonymous function once for each element in the collection returned by (. db (rows sql)). The rows method on the Sql class returns an ArrayList of results from the query.

No comments: