Clj-DBCP / README.md

Shantanu Kumar f36d529 

Shantanu Kumar 0c2708b 
Shantanu Kumar f36d529 









Shantanu Kumar d65e60c 


Shantanu Kumar f36d529 












Shantanu Kumar 0c2708b 



Shantanu Kumar f36d529 




































Shantanu Kumar 4ae925c 
Shantanu Kumar f36d529 




















































































Shantanu Kumar 0c2708b 













Shantanu Kumar f36d529 







Shantanu Kumar 0c2708b 







Shantanu Kumar f36d529 

















































































Shantanu Kumar 0c2708b 


Shantanu Kumar f36d529 
















































Shantanu Kumar 0c2708b 
-*- markdown -*-

# Clj-DBCP v0.6

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:
    * Regular ODBC DSN, Lite ODBC DSN (eg. MS-Excel workbooks)
    * Apache Derby, HSQLDB, H2, SQLite, Axion
    * CUBRID, Firebird, MySQL, PostgreSQL, MonetDB
* 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](http://clojars.org/org.bituf/clj-dbcp)

Examples for usage can be found in the tutorial below:


## Building/Installation

_Note:_ Due to dependency on ODBC and Excel support, builds may work only on
Windows with respective databases (mentioned in configuration files) configured
and started up.

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](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](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 Axion database

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

    ;; filesystem instance
    (db-spec
      (axion-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 MonetDB database

    (db-spec
      (monetdb-datasource "localhost" "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)
        ;; set connection properties
        (add-connection-property! ds "connectTimeout=1000, socketTimeout=500")
        ;(add-connection-property! ds {:connectTimeout 1000 :socketTimeout 500})
        ds))


## Create DataSource from `.properties` file

You can create a DataSource from a properties file
[http://en.wikipedia.org/wiki/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
    weird.conn-properties=
    weird.conn-properties=connectTimeout=1000, socketTimeout=500
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.