DATA ACCESS 270

9.2 Data Access

Stephen C. Gilardi’s clojure.contrib.sql provides convenience wrappers for accessing a database using Java Database Connectivity (JDBC).1 You do not need any prior experience with JDBC to work through the examples in this section. However, sql is a thin wrapper, and on a real project you would need to learn JDBC.

The example in this section is a database of code snippets. To store the snippets, you will use the HSQLDB database. You will need to add lib/hsqldb.jar to your classpath when launching Clojure. (The REPL launch scripts bin/repl.sh and bin\repl.bat include lib/hsqldb.jar for you.)

The entry point for sql is the with-connection macro:

(clojure.contrib.sql/with-connection db-spec & body)

The db-spec contains connection information, which is then passed to JDBC’s DriverManager. The body is a set of forms. with-connection will open a JDBC Connection, execute the forms in body, and then close the connection.

Define a db var to hold the database connection specification, which you will need throughout this example:

Download examples/snippet.clj

; replace "snippet-db" with a full path! (def db {:classname "org.hsqldb.jdbcDriver"

:subprotocol "hsqldb"

:subname "file:snippet-db" })

The :subname tells HSQLDB to store the database in filenames based on snippet-db. Make sure you change the name snippet-db to a valid full path on your local filesystem so that various examples in this chapter will all access the same database.

Now you are ready to create the snippets table. The create-table function creates a table:

(clojure.contrib.sql/create-table name & column-specs)

The name names the table, and a column-spec is an array describing a column in the table. Create a function named create-snippets that

1. For years, JDBC was officially a trademark, not an acronym. As of this writing, both Sun and Wikipedia have bowed to common sense and acknowledge that it stands for Java Database Connectivity.

Prepared exclusively for WG Custom Motorcycles

Report erratum

this copy is (P1.0 printing, May 2009)

DATA ACCESS 271

creates a snippets table with a primary key id, a string body, and a datetime created_at:

Download examples/snippet.clj

(use 'clojure.contrib.sql) (defn create-snippets []

(create-table :snippets

[:id :int "IDENTITY" "PRIMARY KEY" ] [:body :varchar "NOT NULL" ] [:created_at :datetime]))

Try to create the snippets table from the REPL:

(create-snippets)

java.lang.Exception: no current database connection

This is where with-connection comes into play. In order to create the table, call create-snippets from within with-connection, specifying db as your database:

(with-connection db (create-snippets))

(0)

The return value of (0) indicates success. Failure would cause an exception, which you can see by trying to create the same table again:

(with-connection db (create-snippets))

java.lang.Exception: transaction rolled back: failed batch

Calling create-snippets a second time fails because the table already exists.

Now you are ready to add some rows to the snippets table. The insertvalues function adds rows:

(clojure.contrib.sql/insert-values table column-names & values)

column-names is a vector of column names, and each value is a vector of column values.

Create an insert-snippets function that adds a few sample snippets, specifying a body and setting the created_at to the current time.

Download examples/snippet.clj

(defn now [] (java.sql.Timestamp. (.getTime (java.util.Date.)))) (defn insert-snippets []

(let [timestamp (now)] (seq

(insert-values :snippets [:body :created_at] ["(println :boo)" timestamp]

["(defn foo [] 1)" timestamp]))))

Prepared exclusively for WG Custom Motorcycles

Report erratum

this copy is (P1.0 printing, May 2009)

DATA ACCESS 272

The now function gets the current time in a JDBC-friendly format. The let calls now only once, guaranteeing that both records have the same created_at value.

The entire insert-values call is wrapped in a seq to convert the return value (a Java array of insertion counts) into a REPL-friendly Clojure sequence. Insert the records:

(with-connection db (insert-snippets))

(1 1)

The (1 1) indicates that insert-snippets issued two SQL statements, each of which successfully updated one row.

Now that you have some rows in the table, you are ready to issue a query. The with-query-results macro issues some sql and then executes the forms in body with results bound to a sequence of the results:

(with-query-results results sql & body)

Use with-query-results to create a print-snippets function that simply prints all the snippets:

Download examples/snippet.clj

(defn print-snippets []

(with-query-results res ["select * from snippets" ] (println res)))

Use print-snippets to print the contents of the snippets table:

(with-connection db (print-snippets)) | ({:id 0, :body (println :boo),

|	:created_at #<Timestamp 2009-01-03	11:40:19.985>}
|	{:id 1, :body (defn foo [] 1),
|	:created_at #<Timestamp 2009-01-03	11:40:19.985>})

What if you wanted to hold on to the snippets, such as to use them as the model to back some kind of user interface? with-query-results returns the last form in its body, so you might expect to write select-snippets like this:

Download examples/snippet.clj

; Broken!

(defn select-snippets []

(with-query-results res ["select * from snippets" ] res))

But it won’t work:

(with-connection db (select-snippets))

java.sql.SQLException: No data is available

Prepared exclusively for WG Custom Motorcycles

Report erratum

this copy is (P1.0 printing, May 2009)

DATA ACCESS 273

The problem is ordering of execution. select-snippets returns a lazy sequence over the JDBC ResultSet. But JDBC results can themselves be lazy. So, neither Clojure nor JDBC realizes the result set inside withconnection. By the time the REPL tries to print the snippets, both the connection and the ResultSet are closed.

To get access to the snippets outside of a database connection, realize them with a doall. This will spin through the results, caching them in the Clojure sequence. The connection and result set can then safely close, freeing precious database resources. Everyone is happy:

Download examples/snippet.clj

(defn select-snippets [] (with-connection db

(with-query-results res ["select * from snippets" ] (doall res))))

Verify that the improved select-snippets works:

(with-connection db (select-snippets))

({:id 0, :body (println :boo),

:created_at #<Timestamp 2009-01-03 11:40:19.985>} {:id 1, :body (defn foo [] 1),

:created_at #<Timestamp 2009-01-03 11:40:19.985>})

The idiom of realizing a result set is common enough to deserve its own function. Create a sql-query function that runs a query and then realizes its result set with doall:

Download examples/snippet.clj

(defn sql-query [q]

(with-query-results res q (doall res)))

Verify that sql-query works by selecting the body of all snippets:

(with-connection db (sql-query ["select body from snippets"]))

({:body "(println :boo)"} {:body "(defn foo [] 1)"})

You now have almost everything you need for the data layer of a simple code snippet application. The one remaining detail is a function that can insert a new snippet and return the ID of the new snippet. This code is HSQLDB-specific, and I will spare you the exploration required. Implement the last-created-id method as follows:

Download examples/snippet.clj

(defn last-created-id

"Extract the last created id. Must be called in a transaction that performed an insert. Expects HSQLDB return structure of the form [{:@p0 id}]."

[]

(first (vals (first (sql-query ["CALL IDENTITY()" ])))))

Prepared exclusively for WG Custom Motorcycles

Report erratum

this copy is (P1.0 printing, May 2009)

DATA ACCESS 274

To use last-created-id, place it at the end of a transaction that inserts a snippet. sql provides the transaction macro for this purpose:

(clojure.contrib.sql/transaction & body)

Use transaction, plus your previously defined now and last-created-id functions, to implement insert-snippet:

Download examples/snippet.clj

(defn insert-snippet [body] (with-connection db

(transaction (insert-values :snippets

[:body :created_at] [body (now)])

(last-created-id))))

If you call insert-snippet multiple times, you will see the returned ID increase:

(insert-snippet "(+ 1 1)")

4

(insert-snippet "(ref true)")

5

You now have a minimal library for adding and reviewing snippets.

Other Library Options

If you are accustomed to using a persistence framework such as Hibernate,2 iBATIS,3 or ActiveRecord,4 then sql will probably seem very lowlevel. That’s because it is. If you want a higher-level tool, you have two options:

•Use a Java framework such a Hibernate or iBatis from Clojure.

•Use one of the emerging Clojure-specific persistence libraries.

The Java frameworks are well-documented elsewhere. Be careful in choosing one of these frameworks for a Clojure app. Most of them will bring a lot of Java flavor with them. In particular, they will tend to use Java classes to represent database tables, instead of simple maps. They will also bring in XML and/or Java annotations for configuration. None of these are idiomatic Clojure.

2.http://www.hibernate.org/

3.http://ibatis.apache.org/

4.http://ar.rubyonrails.com/

Prepared exclusively for WG Custom Motorcycles

Report erratum

this copy is (P1.0 printing, May 2009)