Clojure Programming/Examples/JDBC Examples

From Wikibooks, the open-content textbooks collection

Jump to: navigation, search

This page is intended to be a reference for using JDBC with Clojure. We'll create a simple blog database to see the basic methods clojure.contrib.sql.

Contents

[edit] Connection Examples

Below are several examples of connecting to a database of JDBC through clojure. They all depend on the Clojure Contrib library. Also, you need to have the proper JDBC jar in the class path.

[edit] Apache Derby

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

(use 'clojure.contrib.sql)
 
(let [db-path "c:/derby/myblog"]
 
  (def db {:classname "org.apache.derby.jdbc.EmbeddedDriver"
           :subprotocol "derby"
           :subname db-path
           :create true}))

[edit] 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 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.contrib.sql :as sql]) 
;(sql/with-connection db
;  (sql/with-query-results rs ["select * from customer"]
;    (dorun (map #(println (:lastname %)) rs))))

[edit] 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.contrib.sql)
 
(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"}))

[edit] Postgres

The Postgres connection is virtually the same as the MySQL version. The classname and subprotocol attributes are set for their appropriate Postgres values. db-port is set to 5432, as this is the default Postgres port.

(use 'clojure.contrib.sql)
 
(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"}))

[edit] 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.contrib.sql)
 
  (def db {:classname "oracle.jdbc.driver.OracleDriver" ; must be in classpath
           :subprotocol "oracle:thin"
           :subname "@localhost:1521"
           ; Any additional keys are passed to the driver
           ; as driver-specific properties.
           :user "???"
           :password "???" })

[edit] DataSource

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.contrib.sql)
  (: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"))})

[edit] 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".

[edit] Creating a Table

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

  • id (Primary Key)
  • title
  • body

[edit] Derby

Adding a timestamp column to show off more DDL.

(defn create-blogs
  "Create a table to store blog entries"
  []
  (clojure.contrib.sql/create-table
    :blogs
    [:id :int "PRIMARY KEY" "GENERATED ALWAYS AS IDENTITY"]
    [:title "varchar(255)"]
    [:body :clob]
    [:created_at :timestamp "NOT NULL" "DEFAULT CURRENT_TIMESTAMP"]))

[edit] MySQL

(defn create-blogs
  "Create a table to store blog entries"
  []
  (clojure.contrib.sql/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.contrib.sql/with-connection
   db
   (clojure.contrib.sql/transaction
     (create-blogs)))

[edit] Exercise

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

  • id (Primary Key)
  • name

[edit] Dropping a Table

Below is a method to drop a table.

(defn drop-blogs
  "Drop the blogs table"
  []
  (try
   (clojure.contrib.sql/drop-table :blogs)
   (catch Exception _)))

To invoke the method call it like this:

 (clojure.contrib.sql/with-connection
   db
   (clojure.contrib.sql/transaction
     (drop-blogs)))

[edit] Exercise

Create a method to drop the table named categories.

[edit] Adding Columns

TO DO

[edit] Removing Columns

TO DO

[edit] DML Examples

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

[edit] 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))))

[edit] INSERT

This function inserts and entry into the blog database.

(defn insert-blog-entry
  "Insert data into the table"
  [title,body]
  (clojure.contrib.sql/insert-values
   :blogs
   [:title :body]
   [title body]))

And invoking the function

 (clojure.contrib.sql/with-connection
   db
   (clojure.contrib.sql/transaction
    (insert-blog-entry "Hello World" "Life is awesome in the lisp world.") ))

[edit] UPDATE

Here's an example updating a blog entry.

(defn update-blog
  "This method updates a blog entry"
  [id attribute-map]
  (clojure.contrib.sql/update-values
   :blogs
   ["id=?" id]
   attribute-map))

Let's update the first blog entry.

(with-connection db 
  (clojure.contrib.sql/transaction
    (update-blog 1 {:title "Awesome Title"})))

[edit] DELETE

;
; this lines allows us to say sql/with-connection instead of
; clojure.contrib.sql/with-connection
;
(require '[clojure.contrib.sql :as sql])
(defn delete-blog
  "Deletes a blog entry given the id"
  [id]
  (sql/with-connection db
    (sql/delete-rows :blogs ["id=?" id])))

[edit] Transactions

TO DO

Retrieved from "http://en.wikibooks.org/wiki/Clojure_Programming/Examples/JDBC_Examples"