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, consultUsing 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.
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.
(use'clojure.java.jdbc)(defdb{: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-connectiondb(with-query-resultsrs["select * from sys.objects where type = 'U'"](doseq[rowrs](println(:namerow)))));;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(defdb{: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(.SystemgetProperty"java.library.path");;I suggest reload the shell or the system after the dll is added
Derby supports either client/server or embedded operation. This example uses the embedded mode.
(use'clojure.java.jdbc)(let[db-path"c:/derby/myblog"](defdb{:classname"org.apache.derby.jdbc.EmbeddedDriver":subprotocol"derby":subnamedb-path:createtrue}))
(let[db-protocol"tcp"; "file|mem|tcp"db-host"localhost:9092"; "path|host:port"db-name"Sample"](defdb{: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))))
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-port3306db-name"a_database"](defdb{: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"}))
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-port5432db-name"a_database"](defdb{: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"}))
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)(defdb{: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"})
(use'clojure.java.jdbc)(defdb{:classname"virtuoso.jdbc.Driver":subprotocol"virtuoso":subname"//localhost:1111":user"dba":password"dba"})
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.
(nsexample(:useclojure.java.jdbc)(:importjavax.sql.DataSourcecom.mchange.v2.c3p0.DataSources))(defdb{:datasource(DataSources/pooledDataSource(DataSources/unpooledDataSource"jdbc:oracle:thin:USER/PASS@HOST_IP:PORT:SCHEMA"))})
Example of pooled db connection using PostgreSQL's PGPoolingDataSource class.Note that this is not recommended for production. Use c3p0 or similar instead.
(nsexample(:useclojure.java.jdbc)(:importjavax.sql.DataSourceorg.postgresql.dsPGPoolingDataSource))(let[db-host"localhost"db-name"example"db-user"username"db-pass"notTelling"](defdb{:datasource(doto(newPGPoolingDataSource)(.setServerNamedb-host)(.setDatabaseNamedb-name)(.setUserdb-user)(.setPassworddb-pass)(.setMaxConnections3))}))
Application servers typically bind data sources into JNDI:
(nsexample(:useclojure.java.jdbc))(defdb{:name"jdbc/TestDS"})
In the following examples we'll call the database connectiondb. These examples have been tested with MySQL, Postgres would use "SERIAL" rather than "AUTO_INCREMENT".
We'll start by creating a table calledblogs. This table has three columns.
Adding a timestamp column to show off more DDL.
(defncreate-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"]))
(defncreate-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 methodcreate-blogs that creates a table when called. You can invoke the method as follows
(clojure.java.jdbc/with-connectiondb(clojure.java.jdbc/transaction(create-blogs)))
Create a method to create a table namedcategories. This table has the following columns
Below is a method to drop a table.
(defndrop-blogs"Drop the blogs table"[](try(clojure.java.jdbc/drop-table:blogs)(catchException_)))
To invoke the method call it like this:
(clojure.java.jdbc/with-connectiondb(clojure.java.jdbc/transaction(drop-blogs)))
Create a method to drop the table namedcategories.
(defndrop-all-objects[](do-commands"drop all objects;"))
(clojure.java.jdbc/with-connectiondb(clojure.java.jdbc/transaction(drop-all-objects)))
TO DO
TO DO
Okay, we've got a schema. Bring on the CRUD!
(with-connectiondb(with-query-resultsrs["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.
(defndeclob[clob]"Turn a Derby 10.6.1.0 EmbedClob into a String"(with-open[rdr(java.io.BufferedReader.(.getCharacterStreamclob))](applystr(line-seqrdr))))(with-connectiondb(transaction(with-query-resultsrs["select * from blogs"]; rs will be a sequence of maps,; one for each record in the result set.(doseq[rowrs](println(declob(:bodyrow)))))))
This function inserts an entry into the blog table.
(defninsert-blog-entry"Insert data into the table"[title,body](clojure.java.jdbc/insert-values:blogs[:title:body][titlebody]))
And invoking the function
(clojure.java.jdbc/with-connectiondb(clojure.java.jdbc/transaction(insert-blog-entry"Hello World""Life is awesome in the lisp world.")))
Here's an example updating a blog entry.
(defnupdate-blog"This method updates a blog entry"[idattribute-map](clojure.java.jdbc/update-values:blogs["id=?"id]attribute-map))
Let's update the first blog entry.
(with-connectiondb(clojure.java.jdbc/transaction(update-blog1{:title"Awesome Title"})))
;; the first line allows us to say sql/with-connection instead of; clojure.java.jdbc/with-connection;(require'[clojure.java.jdbc:assql])(defndelete-blog"Deletes a blog entry given the id"[id](sql/with-connectiondb(sql/delete-rows:blogs["id=?"id])))
 | To do: |
Please readhttp://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html.
The order collection must always contain a unique ordering value.
(defnas-str[&s](applystrs))(defncreate-query-paging[{:keys[tblpropertiesorderpredicatefrommax]:or{max100}}]"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-notpredicate""(strpredicate" and "))" rnum >= "from))(create-query-paging{:tbl"mytable":properties["*"]:order["id","ts"]:from10:max20});"SELECT * from (select a.*, ROWNUM() rnum from (select * from mytable order by id,ts ) a WHERE ROWNUM() <= 20) WHERE rnum >= 10"