Source

Clj-DBCP /

Filename Size Date modified Message
src
230 B
1.8 KB
8.1 KB
4.5 KB
690 B

-- markdown --

Clj-DBCP v0.5

Clj-DBCP is a simple Java-6/Clojure wrapper around the Apache DBCP library for creating database connection pools and for embedding databases in applications. The supported operations are:

  • BasicDataSource with driver classname, JDBC url, username and password
  • Support for validation query, setting connection pool parameters
  • Auto-select driver classname and validation query for supported database
  • API for memory/filesystem/network based data sources for embedded databases
  • Supported and tested: (a) Regular ODBC DSNs (b) Apache Derby, HSQLDB, H2, SQLite (c) CUBRID, Firebird, MySQL, PostgreSQL
  • Supported but not tested: Oracle, IBM DB2, jTDS (SQL Server, Sybase)
  • Create datasource as per configuration in Properties file

Usage

Maven/Leiningen dependency details are here: http://clojars.org/org.bituf/clj-dbcp

Examples for usage can be found in the tutorial below:

Building/Installation

You will need Maven 2 or better to build from sources. Execute the following:

$ mvn clean package  # packages up a JAR in "target" dir
$ mvn install        # to install to your local Maven repo
$ mvn clojure:gendoc # generate Clojure API documentation

License

Copyright (C) 2010-2011 Shantanu Kumar (kumar[dot]shantanu[at]gmail[dot]com)

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this software except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Tutorial

Clj-DBCP can be used after including the following in your namespace:

(ns example.app
  (:use org.bituf.clj-dbcp))

This tutorial does not cover the entire API - feel free to browse the documentation and source code/unit tests.

Important: You must include the JDBC drivers on the CLASSPATH on your own. If you are using an Open Source database you may consider using this for drivers: https://bitbucket.org/kumarshantanu/oss-jdbc

Create data source for Apache Derby database

;; in-memory instance
(db-spec
  (derby-memory-datasource))

;; filesystem instance
(db-spec
  (derby-filesystem-datasource))

Example usage (example/app.clj):

(ns example.app
  (:use org.bituf.clj-dbcp)
  (:use [clojure.contrib.sql :as sql :only ()]))

(def db-derby (db-spec
                (derby-memory-datasource))) ; creates in-memory instance

(defn crud
  []
  (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 db-derby
      ;; drop table if pre-exists
      (try (drop-table)
        (catch Exception _)) ; ignore exception
      ;; create table
      (sql/do-commands
        "CREATE TABLE emp (id INTEGER, name VARCHAR(50), age INTEGER)")
      ;; insert
      (sql/insert-values table (keys orig-record) (vals orig-record))
      ;; retrieve
      (println (retrieve-fn))
      ;; update
      (sql/update-values table ["id=?" 1] updt-record)
      ;; drop table
      (drop-table))))

Create data source for H2 database

;; in-memory instance
(db-spec
  (h2-memory-datasource))

;; filesystem instance
(db-spec
  (h2-filesystem-datasource))

Example usage: see Derby example

Create data source for HSQLDB/HyperSQL database

;; in-memory instance
(db-spec
  (hsql-memory-datasource))

;; filesystem instance
(db-spec
  (hsql-filesystem-datasource))

Create data source for SQLite database

;; in-memory instance
(db-spec
  (sqlite-memory-datasource))

;; filesystem instance
(db-spec
  (sqlite-filesystem-datasource))

Example usage: see Derby example

Create data source for CUBRID database

(db-spec
  (cubrid-datasource "localhost:33000" "exampledb" "dbuser" "dbpassword"))

Example usage: see Derby example

Create data source for Firebird database

(db-spec
  (firebird-datasource "localhost" "exampledb" "dbuser" "dbpassword"))

Example usage: see Derby example

Create data source for MySQL database

(db-spec
  (mysql-datasource "localhost" "exampledb" "dbuser" "dbpassword"))

Example usage: see Derby example

Create data source for PostgreSQL database

(db-spec
  (pgsql-datasource "localhost" "exampledb" "dbuser" "dbpassword"))

Example usage: see Derby example

Create data source for Oracle database

(db-spec
  (oracle-datasource "localhost" "examplesid" "dbuser" "dbpassword"))

Example usage: see Derby example

Create data source for IBM DB2 database

(db-spec
  (db2-datasource "localhost" "exampledb" "dbuser" "dbpassword"))

Example usage: see Derby example

Create data source for Microsoft SQL Server database

(db-spec
  (sqlserver-datasource "localhost" "exampledb" "dbuser" "dbpassword"))

Example usage: see Derby example

Create data source for Sybase database

(db-spec
  (sybase-datasource "localhost" "exampledb" "dbuser" "dbpassword"))

Example usage: see Derby example

Create data source for ODBC DSN

(db-spec
  (odbc-datasource "dsn" "dbuser" "dbpassword"))

Example usage: see Derby example

Get data source from JNDI (only looked up; must be already connection pooled)

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

Example usage: see Derby example

Set custom parameters on the datasource

(db-spec
  (let [ds (mysql-datasource "localhost" "exampledb" "dbuser" "secret")]
    ;; set custom validation query
    (set-validation-query! ds "SELECT 1")
    ;; set maximum number of active connections on the datasource at a time
    (set-max-active! ds 15)
    ;; set minimum and maximum number of idle connections on the datasource
    (set-min-max-idle! ds 3 8)
    ds))

Create DataSource from .properties file

You can create a DataSource from a properties file http://en.wikipedia.org/wiki/Properties_file called clj-dbcp.properties either in classpath or on the filesystem in current directory:

(make-datasource-from-properties) ; reads file clj-dbcp.properties and creates DataSource

The file clj-dbcp.properties can look like the following:

# Database configuration for project `foo`
# There are 5 profiles - dev, dev2, dev3, mydev, weird
# If you need a new profile, create one and set default to that profile

default=dev2

dev.db-type=mysql
dev.hostport=12.34.56.78:3306
dev.database=organisation
dev.username=orguser
dev.password=d0ntsh4re

# In-memory H2 database (very fast, no config required, use for testing)
dev2.db-type=h2-memory

# On-filesystem H2 database (fast, data available across app restarts)
dev3.db-type=h2-filesystem
# `db-path` is optional (if not specified, assumed "default")
dev3.db-path=/home/eddie/sample

# Example of using ODBC DSN for creating datasource
mydev.odbc-dsn=cubrid-bituf
# Username and password may be optional (subject to ODBC driver)
#mydev.username=
#mydev.password=

# Example of unsupported databases (using JDBC URL)
weird.classname=com.weird.MagicDriver
weird.jdbc-url=jdbc:weird://34.56.78.90:1234/orgmaster
weird.username=Magic_user
weird.password=simp1e
# the properties below are optional (and available in all profile types)
weird.max-active=10
weird.min-max-idle=2,4
weird.validation-query=SELECT 1
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.