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

### DELETE
## 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.