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])))

Transactions

To do:
Describe transactions

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"