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.

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

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.

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.

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.

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.

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.

DataSource - JNDI
Application servers typically bind data sources into JNDI:

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.

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

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.

To invoke the method call it like this:

Exercise
Create a method to drop the table named categories.

Adding Columns
TO DO

Removing Columns
TO DO

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

SELECT
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.

INSERT
This function inserts an entry into the blog table.

And invoking the function

UPDATE
Here's an example updating a blog entry.

Let's update the first blog entry.

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.