Clj-DBCP Clojure Library for Connecting to CUBRID Database

Clj-DBCP is a Java-6/Clojure library to create a database connection pool, which is known to be a more efficient way to connect to multiple databases in a transaction-heavy environment.

Why do we need a connection pool?

Often you encounter with a situation when you have to allow multiple users to connect to a database, extract some data, then insert this data either modified or as it is to another database. Opening and maintaining a database connection for each user, especially when requests made to a dynamic database-driven website application, is costly and wastes resources.

Here comes a connection pool (and here we can benefit from Clj-DBCP), a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

Downloads

Clj-DBCP JARs can be downloaded as a Maven/Leiningen/Cake dependency from Clojars: http://clojars.org/org.bituf/clj-dbcp.

Source Code

The source code for Clj-DBCP can be forked/downloaded at https://bitbucket.org/kumarshantanu/clj-dbcp/src. The source code page also includes a short tutorial on how to use Clj-DBCP - scroll down on the page to find it.

Sample Project

Download a sample Clj-DBCP project which connects to the CUBRID Database and performs basic CRUD (Create, Retrieve, Update, Delete) operations.

Example

• Create a project using Leiningen where cubrid-crud is the name of the project. Then enter the project directory. If you do not have Leiningen, download and install it from https://github.com/technomancy/leiningen.

lein new cubrid-crud
cd cubrid-crud

• Edit the newly generated project.clj file:

(defproject cubrid-crud "1.0.0"
  :description "CRUD example using CUBRID database"
  :dependencies [[org.clojure/clojure "1.2.0"]
                 [org.clojure/clojure-contrib "1.2.0"]
                 [org.bituf/clj-dbcp "0.3"]
                 [org.clojars.cubrid/cubrid-jdbc "8.3.1.0173"]])

• Get the dependencies. This will install all the prerequisites.

lein deps

• Navigate to the /src directory and edit the dbconfig.properties file. We will use the demodb database which is created by default in CUBRID. The username for it is dba which does not require any password. Change the values according to your database settings if needed.

hostport=localhost:33000
database=demodb
username=dba
password=

• Navigate to the /src/cubrid-crud directory and edit the core.clj file:

;; require the necessary dependencies
(ns cubrid-crud.core
  (:require
    [clojure.contrib.java-utils :as ju]
    [clojure.contrib.sql        :as sql]
    [org.bituf.clj-dbcp         :as dbcp]))

;; load the database configurations
(def config (ju/read-properties "src/dbconfig.properties"))

;; create a function which will perform all operations one by one.
(defn test-crud
    [dbspec]
    (let [table :emp
          orig-record {:id 1 :name "Bashir" :age 40}
          updt-record {:id 1 :name "Shabir" :age 50}
          drop-table  #(sql/do-commands "DROP TABLE emp")
          retrieve-fn #(sql/with-query-results
                         rows
                         ["SELECT * FROM emp WHERE id=?" 1]
                         (first rows))]
      (sql/with-connection
        dbspec
        
        ;; drop table if exists
        (try
          (println "Dropping table")
          (drop-table)
          (catch Exception _
            (println "Cannot drop table; probably doesn't exist?")))
        
        ;; create table
        (println "Creating table")
        (sql/do-commands
          "CREATE TABLE emp (id INTEGER, name VARCHAR(50), age INTEGER)")
        
        ;; insert
        (println "Inserting a row" orig-record)
        (sql/insert-values
          table (keys orig-record) (vals orig-record))
        
        ;; retrieve-check
        (println "Retrieving row")
        (let [row (retrieve-fn)]
          (println row))
        
        ;; update
        (println "Updating row" updt-record)
        (sql/update-values
          table ["id=?" 1] updt-record)
        
        ;; retrieve-check
        (println "Retrieving row")
        (let [row (retrieve-fn)]
          (println row))
        
        ;; delete - TODO
        (println "Deleting row")
        (sql/do-commands
          "DELETE FROM emp WHERE id=1")
        
        ;; drop table
        (println "Dropping table")
        (drop-table))))

;; main function which will be called from Leiningen test
(defn perform-crud
  "Use connection properties to create a connection-pooled DataSource"
  []
  (let [hostport (get config "hostport")
        database (get config "database")
        username (get config "username")
        password (get config "password")
        dbspec (dbcp/db-spec
                 (dbcp/cubrid-datasource
                   hostport database username password))]
    (test-crud dbspec)))

• Navigate to the /test/cubrid-crud/test directory and edit the core.clj file:

(ns cubrid-crud.test.core
  (:use [cubrid-crud.core] :reload)
  (:use [clojure.test]))

(deftest replace-me ;; write
         (perform-crud))

• Start the CUBRID Service and the demodb database.

cubrid service start
cubrid server start demodb

• Run this example as Leiningen test.

lein test

If your project displays tons of errors, make sure you have started the CUBRID Service and the demodb database.

Sample Leiningen test output

The code above first will display an error message that the table we try to drop does not exist. Then it will create that table, insert the new record, select that record, update it, select again, delete that row, and finally drop the table.

Testing cubrid-crud.test.core
Dropping table
CUBRIDException:
 Message: Semantic: Class emp does not exist. drop emp
 SQLState: null
 Error Code: -494
Cannot drop table; probably doesn't exist?
Creating table
Inserting a row {:id 1, :name Bashir, :age 40}
Retrieving row
{:id 1, :name Bashir, :age 40}
Updating row {:id 1, :name Shabir, :age 50}
Retrieving row
{:id 1, :name Shabir, :age 50}
Deleting row
Dropping table
Ran 1 tests containing 0 assertions.
0 failures, 0 errors.

See also

• Clj-DBCP Tutorial
• CRUD in Clojure - Bitumen Framework blog
• CUBRID Tutorials

Getting Help

To report bugs, visit https://bitbucket.org/kumarshantanu/clj-dbcp/issues.

If you have any concerns/ideas/comments, post them in the Google Groups at http://groups.google.com/group/bitumenframework.

Alternatively, post your message to the CUBRID Apps & Tools Forum at http://forum.cubrid.org/.