This branch (master) is for Clojure 1.3 compatability. For Clojure 1.2 please see the clojure-1.2 branch in this repository.
Purified Database Connection Pool Management
clojure.contrib.sql, and clojureql are libraries for abstracting away the JDBC api making it easier to work with SQL databases by simplifying the mechanics of working with queries and their data.
Both libraries have functions for creating connections to your database (e.g. with-connection), but do not help you in managing those connections. You can use connection pool libraries (such as the Apache Commons DBCP) with clojure.contrib.sql by specifying it as a :datasource but you must still take care when creating or pulling those connections out of the pool. If you have a concurrent application where a thread of execution grabs more than 1 connection from the same pool, the pool can become exhausted and deadlock can occur. Which is what happened to us and lead to the creation of this library.
Clorine introduces a registry of database connection configurations and pools. These pools are then managed by Clorine’s with-connection macro (which can be used as clojure.contrib.sql’s is used) – where you specify a database connection by its registered name instead of a map of parameters. Clorine then ensures that a given connection to a given database is a singleton within the context of a given thread. Threads are protected from accidentally sharing a specific connection and a given thread can not establish (or pull from the pool) more than once to a given database (based on the registered name). On subsequent requests for a database connection, the same database connection is returned. This is true even if your code has nested calls to with-connection. In one sense this can free you to a certain extent from having to carefully decide where to place with-connection – the outermost instance will be the one that pulls the connection from the pool while any inner ones will be re-using that connection. Not quite dependency injection, though the connection you need will be available to you when you need it.
Clorine uses the Apache Commons DBCP org.apache.commons.dbcp.BasicDataSource as its pooling implementation.
(ns your-namespace
(require [rn.clorine :as cl]
[clojure.contrib.sql :as sql]))
(defn myfunc [skunum storenum]
(cl/with-connection :inventory
(sql/with-query-results rs ["SELECT * FROM store_inventory where sku = ? and store_num = ?" skunum storenum]
(doseq [rec *rs*]
(prn (format "Item: %s" rec))))))
(defn main []
(cl/register-connection! :inventory
{:driver-class-name "org.postgresql.Driver"
:user "db-username"
:password "db-password"
:url "jdbc:postgresql://your-database-host:5432/your-database-name"
:max-active 8})
(myfunc "1" "PA19101-A7"))
the :max-active parameter in the database connection info map will be passed to the BasicDataSource to control the maximum number of connections the pool will create.
The connection pools are created at the time the connection is registered with Clorine. In addition a connection is requested from the pool in order to validate that the connection’s configuration is valid and the database can be connected to. In the event the database can not be connected to, an exception is thrown and the registry is not updated.
Once a connection has been registered and its pool has been established, any outermost call to with-connection will pull a connection from the appropriate pool based on the registered connection name. This outermost with-connection takes on responsibility for closing the connection (which will place the connection back in the pool). Any with-connection calls that are nested within the outer with-connection will be utilizing the same connection that was established in the outermost binding, and will not close the connection when their scope has exited.
(defn get-remaining-inventory [item-sku store-number]
;; In the example code, this is a nested with-connection, which will
;; return the same :inventory database connection instance the outer
;; with-connection did in the record-restock-orders function
(with-connection :inventory
(sql/with-query-results rs ["SELECT count FROM store_inventory where sku = ? and store_num = ?" skunum storenum]
(:count (first rs)))))
(defn get-items-sold [item-sku store-number date]
;; In the example code, this is a nested with-connection, which will
;; return the same :inventory database connection instance the outer
;; with-connection did in the record-restock-orders function
(with-connection :inventory
(sql/with-query-results rs ["SELECT count(*) FROM store_items_sold where sku = ? and store_num = ? and date = ?" skunum storenum date]
(:count (first rs)))))
(defn record-restock-orders [item-sku store-number date]
;; This is the outermost with-connection. It is where the connection to
;; the inventory database is established (pulled from the pool) and defines
;; the scope of the connection being open (it is closed when this scope is
;; left, placing the connection back into the pool).
(with-connection :inventory
(let [items-sold (get-items-sold item-sku store-number date)
items-remaining (get-remaining-inventory item-sku store-number)]
(sql/do-prepared "INSERT INTO restock_items (sku,store_num,order_date) values (?,?,current_date)"
[item-sku (if (> items-sold items-remaining)
(* items-sold 2)
items-sold]))))
(defn main []
;; a test connection will be established when the :inventory connection is registered
(cl/register-connection! :inventory
{:driver-class-name "org.postgresql.Driver"
:user "db-username"
:password "db-password"
:url "jdbc:postgresql://your-database-host:5432/your-database-name"
:max-active 8})
(record-restock-orders "1" "PA19101-A7" "2011-01-21")
<dependencies>
<dependency>
<groupId>com.relaynetwork</groupId>
<artifactId>clorine</artifactId>
<version>1.3.12</version>
</dependency>
...
</dependencies>
[com.relaynetwork/clorine "1.3.12"]
Copyright © 2011 RelayNetwork
Distributed under the Eclipse Public License, the same as Clojure.