Clojure Programming/Examples/JDBC Examples

This page is intended to be a reference for using JDBC with Clojure. We'll create a simple blog database to see the basic functions in clojure.java.jdbc.

For the latest, most up-to-date community-managed documentation for the clojure.java.jdbc library, consult Using java.jdbc on Clojure Documentation. This WikiBooks page is written around a very old version of the library and most of the examples here will not work with newer versions.

Connection Examples

Below are several examples of connecting to a database of JDBC through Clojure. They all depend on the Clojure Contrib library org.clojure/java.jdbc. Also, you need to have the proper JDBC jar in the class path.

Microsoft SQL Server

(use 'clojure.java.jdbc) (def db {:classname "com.microsoft.jdbc.sqlserver.SQLServerDriver"  :subprotocol "sqlserver"  :subname "//server-name:port;database=database-name;user=sql-authentication-user-name;password=password" }) ;Add Classpath to your C:\Program Files\Java\JDBC\sqljdbc_3.0\enu\sqljdbc4.jar ;Below code demos how to execute a simple sql select query and print it to console ;This query will print all the user tables in your MS SQL Server Database (with-connection db   (with-query-results rs ["select * from sys.objects where type = 'U'"]   (doseq [row rs] (println (:name row))) )) ;;Instead of passing user and password, you can authenticate yourself using current system user (es. current windows user) ;;To do this you have to add the string "integratedSecurity=true", removing user and password (def db {:classname "com.microsoft.jdbc.sqlserver.SQLServerDriver"  :subprotocol "sqlserver"  :subname "//server-name:port;database=database-name;integratedSecurity=true"}) ;;You have also to add the "sqljdc_auth.dll" file to your java.library.path (you can find the dll file into the JDBCDriver folder /enu/auth/platform you probably download before) ;;An easy way to check what is your current java.library.path is calling this from leiningen repl (. System getProperty "java.library.path") ;;I suggest reload the shell or the system after the dll is added 

Apache Derby

Derby supports either client/server or embedded operation. This example uses the embedded mode.

(use 'clojure.java.jdbc) (let [db-path "c:/derby/myblog"]    (def db {:classname "org.apache.derby.jdbc.EmbeddedDriver"  :subprotocol "derby"  :subname db-path  :create true})) 

H2Database

(let [db-protocol "tcp" ; "file|mem|tcp"  db-host "localhost:9092" ; "path|host:port"  db-name "Sample"]    (def db {:classname "org.h2.Driver" ; must be in classpath  :subprotocol "h2"  :subname (str "jdbc:h2:" db-protocol "://" db-host "/" db-name)  ; Any additional keys are passed to the driver  ; as driver-specific properties.  :user "sa"  :password ""})) ; ; specify the path to your database driver ;  (add-classpath "file:///c:/Installation/h2/bin/h2.jar") ;; ;; Here is an example of creating a symbol in the  ;; existing namespace as an alias to a namespace ;; ;(require '[clojure.java.jdbc :as sql])  ;(sql/with-connection db ; (sql/with-query-results rs ["select * from customer"] ; (dorun (map #(println (:lastname %)) rs)))) 

MySQL

The MySQL connector is fairly straightforward to set up. The classname and subprotocol are set to the values for MySQL. The db-port is set to 3306, as this is the default port for MySQL.

(use 'clojure.java.jdbc)   (let [db-host "localhost"  db-port 3306  db-name "a_database"]    (def db {:classname "com.mysql.jdbc.Driver" ; must be in classpath  :subprotocol "mysql"  :subname (str "//" db-host ":" db-port "/" db-name)  ; Any additional keys are passed to the driver  ; as driver-specific properties.  :user "a_user"  :password "secret"})) 

PostgreSQL

The PostgreSQL connection is virtually the same as the MySQL version. The classname and subprotocol attributes are set for their appropriate PostgreSQL values. db-port is set to 5432, as this is the default PostgreSQL port.

(use 'clojure.java.jdbc) (let [db-host "localhost"  db-port 5432  db-name "a_database"]  (def db {:classname "org.postgresql.Driver" ; must be in classpath  :subprotocol "postgresql"  :subname (str "//" db-host ":" db-port "/" db-name)  ; Any additional keys are passed to the driver  ; as driver-specific properties.  :user "a_user"  :password "secret"})) 

Oracle

The Oracle connector is fairly straightforward to set up. The classname and subprotocol are set to the values for Oracle. The db-port is set to 1521, as this is the default port for Oracle XE.

(use 'clojure.java.jdbc)  (def db {:classname "oracle.jdbc.OracleDriver" ; must be in classpath  :subprotocol "oracle"  :subname "thin:@172.27.1.7:1521:SID" ; If that does not work try: thin:@172.27.1.7:1521/SID  :user "user"  :password "pwd"}) 

Virtuoso

(use 'clojure.java.jdbc)  (def db { :classname "virtuoso.jdbc.Driver"  :subprotocol "virtuoso"  :subname "//localhost:1111"  :user "dba" :password "dba" }) 

DataSource - Oracle

Here's an example of pooled db connections using the c3p0 library on top of oracle. Make sure c3p0 jars and oracle driver jar is in the classpath.

(ns example  (:use clojure.java.jdbc)  (:import javax.sql.DataSource  com.mchange.v2.c3p0.DataSources)) (def db {:datasource (DataSources/pooledDataSource   (DataSources/unpooledDataSource "jdbc:oracle:thin:USER/PASS@HOST_IP:PORT:SCHEMA"))}) 

DataSource - PostgreSQL

Example of pooled db connection using PostgreSQL's PGPoolingDataSource class. Note that this is not recommended for production. Use c3p0 or similar instead.

(ns example  (:use clojure.java.jdbc)  (:import javax.sql.DataSource  org.postgresql.ds PGPoolingDataSource)) (let [db-host "localhost"  db-name "example"  db-user "username"  db-pass "notTelling"]  (def db {:datasource (doto (new PGPoolingDataSource) (.setServerName db-host) (.setDatabaseName db-name) (.setUser db-user) (.setPassword db-pass) (.setMaxConnections 3))})) 

DataSource - JNDI

Application servers typically bind data sources into JNDI:

(ns example  (:use clojure.java.jdbc)) (def db {:name "jdbc/TestDS"}) 

DDL Examples

In the following examples we'll call the database connection db. These examples have been tested with MySQL, Postgres would use "SERIAL" rather than "AUTO_INCREMENT".

Creating a Table

We'll start by creating a table called blogs. This table has three columns.

• id (Primary Key)
• title
• body

Derby

Adding a timestamp column to show off more DDL.

(defn create-blogs  "Create a table to store blog entries"  []  (clojure.java.jdbc/create-table  :blogs  [:id :int "PRIMARY KEY" "GENERATED ALWAYS AS IDENTITY"]  [:title "varchar(255)"]  [:body :clob]  [:created_at :timestamp "NOT NULL" "DEFAULT CURRENT_TIMESTAMP"])) 

MySQL

(defn create-blogs  "Create a table to store blog entries"  []  (clojure.java.jdbc/create-table  :blogs  [:id :integer "PRIMARY KEY" "AUTO_INCREMENT"]  [:title "varchar(255)"]  [:body :text])) 

This method will create a method create-blogs that creates a table when called. You can invoke the method as follows

 (clojure.java.jdbc/with-connection  db  (clojure.java.jdbc/transaction  (create-blogs))) 

Exercise

Create a method to create a table named categories. This table has the following columns

• id (Primary Key)
• name

Dropping a Table

Below is a method to drop a table.

(defn drop-blogs  "Drop the blogs table"  []  (try  (clojure.java.jdbc/drop-table :blogs)  (catch Exception _))) 

To invoke the method call it like this:

 (clojure.java.jdbc/with-connection  db  (clojure.java.jdbc/transaction  (drop-blogs))) 

Exercise

Create a method to drop the table named categories.

Dropping All Object Using do-commands

(defn drop-all-objects  []  (do-commands "drop all objects;")) 

(clojure.java.jdbc/with-connection  db  (clojure.java.jdbc/transaction  (drop-all-objects))) 

Adding Columns

TO DO

Removing Columns

TO DO

DML Examples

Okay, we've got a schema. Bring on the CRUD!

SELECT

(with-connection db   (with-query-results rs ["select * from blogs"]   ; rs will be a sequence of maps,   ; one for each record in the result set.   (dorun (map #(println (:title %)) rs)))) 

To retrieve the CLOB column with Derby, you can convert the returned object to a String, and you must be inside of a transaction to do that.

(defn declob [clob]  "Turn a Derby 10.6.1.0 EmbedClob into a String"  (with-open [rdr (java.io.BufferedReader. (.getCharacterStream clob))]  (apply str (line-seq rdr)))) (with-connection db  (transaction  (with-query-results rs ["select * from blogs"]   ; rs will be a sequence of maps,   ; one for each record in the result set.   (doseq [row rs] (println (declob (:body row))))))) 

INSERT

This function inserts an entry into the blog table.

(defn insert-blog-entry  "Insert data into the table"  [title,body]  (clojure.java.jdbc/insert-values  :blogs  [:title :body]  [title body])) 

And invoking the function

 (clojure.java.jdbc/with-connection  db  (clojure.java.jdbc/transaction  (insert-blog-entry "Hello World" "Life is awesome in the lisp world.") )) 

UPDATE

Here's an example updating a blog entry.

(defn update-blog  "This method updates a blog entry"  [id attribute-map]  (clojure.java.jdbc/update-values  :blogs  ["id=?" id]  attribute-map)) 

Let's update the first blog entry.

(with-connection db   (clojure.java.jdbc/transaction  (update-blog 1 {:title "Awesome Title"}))) 

DELETE

; ; the first line allows us to say sql/with-connection instead of ; clojure.java.jdbc/with-connection ; (require '[clojure.java.jdbc :as sql]) (defn delete-blog  "Deletes a blog entry given the id"  [id]  (sql/with-connection db  (sql/delete-rows :blogs ["id=?" id]))) 

Paging

Oracle and HSQLDB

Please read http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html.

The order collection must always contain a unique ordering value.

(defn as-str [& s] (apply str s)) (defn create-query-paging [{:keys [tbl properties order predicate from max] :or {max 100} }]  "Creates a SQL query using paging and ROWNUM()"  (str "SELECT * from (select " (clojure.string/join "," (map #(str "a." %) properties))   ", ROWNUM() rnum from (select " (clojure.string/join "/" properties)   " from " tbl   " order by " (clojure.string/join "," order) " ) a "  " WHERE ROWNUM() <= " max  ") WHERE " (if-not predicate "" (str predicate " and ")) " rnum >= " from)) (create-query-paging {:tbl "mytable" :properties ["*"] :order ["id", "ts"] :from 10 :max 20} ) ;"SELECT * from (select a.*, ROWNUM() rnum from (select * from mytable order by id,ts ) a WHERE ROWNUM() <= 20) WHERE rnum >= 10"