Clojure wrapper for Apache DBCP to create JDBC connections pools
Clojure Java Other
Latest commit 1a25fec Jul 15, 2016 @kumarshantanu committed on GitHub Merge pull request #6 from NandhithaR/dbcp2
updated example in README.md

README.md

clj-dbcp

Clojure wrapper for Apache DBCP2 to create JDBC connections pools.

Usage

On Clojars: https://clojars.org/clj-dbcp

Leiningen coordinates: [clj-dbcp "0.9.0"] (supports Clojure 1.5 through Clojure 1.9, Java 7 or higher)

The recommended way to create a datasource is to call the clj-dbcp.core/make-datasource function, for example:

(make-datasource {:classname "com.mysql.jdbc.Driver"
                  :jdbc-url "jdbc:mysql://localhost/empdb"
                  :username "empuser"
                  :password "s3cr3t"
                  :test-query "SELECT 1;"})

You can also parse a database URL (Heroku style) and use to create datasource:

(make-datasource (parse-url "postgres://foo:bar@heroku.com:5489/hellodb"))

or,

(make-datasource (parse-url (System/getenv "DATABASE_URL")))

Sections below describe which of the keys are applicable to various databases:

JDBC parameters

Required: :classname (string), :jdbc-url (string) Optional: :test-query (string)

Optional keys for all JDBC connections

Keyword arg Meaning Default/special
:properties Map of property names and values
:user Database username
:username Database username, same as :user
:password Database password
:test-query Validation query As per :target
:init-size Initial size of connection pool (int)
:min-idle Minimum idle connections in pool (int)
:max-idle Maximum idle connections in pool (int)
:max-total Maximum active connections in pool (int) -ve=no limit
:pool-pstmt? Whether to pool prepared statements true
:max-open-pstmt Maximum open prepared statements (int)
:remove-abandoned? Whether to remove abandoned connections true
:remove-abandoned-timeout-seconds Timeout in seconds (int) 300
:log-abandoned? Whether to log abandoned connections true
:lifo-pool? Whether Last-In-First-Out (LIFO) or not false
:test-while-idle? Whether validate the idle connections true
:test-on-borrow? Whether validate connections on borrow true
:test-on-return? Whether validate connections on return true
:test-query-timeout Timeout (seconds) for validation queries
:millis-between-eviction-runs Millis to sleep between evictions -1
:min-evictable-millis Millis a connection may sit idle before eviction 1800000
:tests-per-eviction No. of connections to test in each eviction run 3
:cache-state? Whether to cache state true

Generic JDBC connections

:adapter Required keys Desired keys
:jdbc :classname :jdbc-url :test-query

JNDI connections

You can open a JNDI datasource (unlike the JDBC datasource) as follows:

(make-datasource :jndi {:context "java:comp/env/myDataSource"})

or,

(jndi-datasource "java:comp/env/myDataSource")

Example

A typical CRUD example using Derby database is below.

Leiningen dependencies:

[clj-dbcp "0.9.0"]
[asphalt  "0.4.0"]  ; for JDBC CRUD operations
[mysql/mysql-connector-java "6.0.2"]  ; MySQL JDBC driver 

Example code:

(ns example.app
(ns newproj.core
  (:require 
    [clj-dbcp.core     :as dbcp]
    [asphalt.core :as a]))

(def db-sql  ;; an in-memory database instance
  {:datasource
   (dbcp/make-datasource
     {:classname "com.mysql.jdbc.Driver" 
      :jdbc-url   "jdbc:mysql://localhost:3306/new_db"
      :user "root" 
      :password "root"})})

(defn crud
  []
  (a/update db-sql "CREATE TABLE IF NOT EXISTS EMP (ID int, Name varchar (25), Age int)" [])
  (a/update db-sql
    "INSERT INTO emp (id, name, age) VALUES (?, ?, ?)"
    [1, "Bashir",40])
  (a/update db-sql
    "UPDATE EMP set name=\"Shabir\", age=50 where id=1"
    [])
  (println (a/query a/fetch-rows
             db-sql
             "SELECT id, name, age FROM emp" []))
  (a/update db-sql
    "DROP TABLE EMP" []))

Development Notes

You need Java 7 and Leiningen 2 to build this code. Testing JDBC-ODBC bridge driver requires that you use a Windows machine with ODBC DSNs configured.

Starting up the swank server (if you are going to work using Emacs/Slime):

$ lein2 dev swank

Testing against the dev version:

$ ./run-tests

Testing across several versions of Clojure:

$ ./run-tests.sh all

Contributors

Getting in touch

On GMail: kumar.shantanu(at)gmail.com

On Twitter: @kumarshantanu

License

Copyright © 2012-2016 Shantanu Kumar

Distributed under the Eclipse Public License, the same as Clojure.