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 Describe transactions Clojure Programming 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"