Wikibooks

Clojure Programming/Examples/JDBC Examples

< Clojure Programming | 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

edit

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

edit 
(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

edit

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

edit 
(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

edit

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

edit

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

edit

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

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

DataSource - Oracle

edit

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

edit

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

edit

Application servers typically bind data sources into JNDI: 

(ns example
  (:use clojure.java.jdbc))

(def db {:name "jdbc/TestDS"})

DDL Examples

edit

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

edit

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

  • id (Primary Key)
  • title
  • body

Derby

edit

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

edit 
(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

edit

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

  • id (Primary Key)
  • name

Dropping a Table

edit

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

edit

Create a method to drop the table named categories.

Dropping All Object Using do-commands

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

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

Adding Columns

edit

TO DO

Removing Columns

edit

TO DO

DML Examples

edit

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

SELECT

edit 
(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

edit

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

edit

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

edit 
;
; 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

edit
 

To do:
Describe transactions

Paging

edit

Oracle and HSQLDB

edit

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"
Retrieved from "https://en.wikibooks.org/w/index.php?title=Clojure_Programming/Examples/JDBC_Examples&oldid=3676447"