Shantanu Kumar avatar Shantanu Kumar committed eb881ba

First import

Comments (0)

Files changed (7)

+-*- markdown -*-
+
+# Changes and TODO
+
+
+* [TODO] Database transactions
+* [TODO] Declarative transactions (ignore which exceptions, isolation level)
+* [TODO] Stored procedures (without database metadata, StoredProcedure)
+* [TODO] Database vendor specific error codes
+* [TODO] Database metadata assisted Stored Procedures
+
+
+## 0.1 / 2011-Feb-??
+
+* Use Spring 3.0.5
+* Parameterized SQL statements (SimpleJdbcTemplate)
+* Database metadata assisted Inserts
+-*- markdown -*-
+
+# Fountain-JDBC v0.1-SNAPSHOT
+
+Fountain-JDBC is a Clojure wrapper for Spring-JDBC (a component to deal with JDBC
+in Spring framework.) The current scope is:
+
+* Parameterized SQL statements
+* Database metadata assisted Inserts
+
+
+## Usage
+
+Maven/Leiningen dependency details are here: [http://clojars.org/org.bituf/fountain-jdbc](http://clojars.org/org.bituf/fountain-jdbc)
+
+Examples for usage can be found in the tutorial below:
+
+
+## Building/Installation
+
+You will need Maven 2 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 Shantanu Kumar (kumar.shantanu at gmail dot com)
+
+Distributed under the Apache 2 License.
+
+
+# Tutorial
+
+## Parameterized SQL statements
+
+    (use 'org.bituf.fountain.jdbc)
+
+## Database metadata assisted Inserts
+<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
+  <modelVersion>4.0.0</modelVersion>
+  <groupId>org.bituf</groupId>
+  <artifactId>fountain-jdbc</artifactId>
+  <packaging>jar</packaging>
+  <version>0.1-SNAPSHOT</version>
+  <name>fountain-jdbc</name>
+  <description>
+    Fountain-JDBC is a Clojure wrapper for Spring-JDBC component of the Spring framework.
+  </description>
+  <url>http://code.google.com/p/bitumenframework/</url>
+  <repositories>
+    <repository>
+      <id>build.clojure.org</id>
+      <url>http://build.clojure.org/snapshots/</url>
+      <snapshots>
+        <enabled>true</enabled>
+      </snapshots>
+    </repository>
+    <repository>
+      <id>clojars.org</id>
+      <url>http://clojars.org/repo</url>
+    </repository>
+  </repositories>
+  
+  <properties>
+      <org.springframework.version>3.0.5.RELEASE</org.springframework.version>
+  </properties>
+  
+  <dependencies>
+    <dependency>
+      <groupId>org.clojure</groupId>
+      <artifactId>clojure</artifactId>
+      <version>1.2.0</version>
+      <optional>true</optional>
+    </dependency>
+    <dependency>
+        <groupId>org.springframework</groupId>
+        <artifactId>spring-jdbc</artifactId>
+        <version>${org.springframework.version}</version>
+    </dependency>
+    <dependency>
+      <groupId>org.bituf</groupId>
+      <artifactId>clj-miscutil</artifactId>
+      <version>0.2-SNAPSHOT</version>
+    </dependency>
+    <dependency>
+      <groupId>org.bituf</groupId>
+      <artifactId>clj-dbspec</artifactId>
+      <version>0.1-SNAPSHOT</version>
+    </dependency>
+    <!--
+    <dependency>
+      <groupId>org.clojure</groupId>
+      <artifactId>clojure-contrib</artifactId>
+      <version>1.2.0</version>
+      <optional>true</optional>
+    </dependency>
+    <dependency>
+      <groupId>autodoc</groupId>
+      <artifactId>autodoc</artifactId>
+      <version>0.7.1</version>
+      <optional>true</optional>
+    </dependency>
+    -->
+    <!-- Simple-JNDI -->
+    <dependency>
+      <groupId>simple-jndi</groupId>
+      <artifactId>simple-jndi</artifactId>
+      <version>0.11.4</version>
+      <scope>test</scope>
+    </dependency>
+    <!-- Apache DBCP -->
+    <dependency>
+      <groupId>org.bituf</groupId>
+      <artifactId>clj-dbcp</artifactId>
+      <version>0.4</version>
+      <scope>test</scope>
+    </dependency>
+    <!-- JDBC Drivers -->
+    <dependency><!-- All Open Source JDBC Drivers available via Maven -->
+      <groupId>org.bituf</groupId>
+      <artifactId>oss-jdbc</artifactId>
+      <version>0.4</version>
+      <scope>test</scope>
+    </dependency>
+  </dependencies>
+  <build>
+    <plugins>
+      <plugin>
+        <groupId>com.theoryinpractise</groupId>
+        <artifactId>clojure-maven-plugin</artifactId>
+        <version>1.3.6</version>
+        <executions>
+          <execution>
+            <id>compile</id>
+            <phase>compile</phase>
+            <goals>
+              <goal>compile</goal>
+            </goals>
+          </execution>
+          <execution>
+            <id>test-compile</id>
+            <phase>test-compile</phase>
+            <goals>
+              <goal>testCompile</goal>
+            </goals>
+          </execution>
+          <execution>
+            <id>test</id>
+            <phase>test</phase>
+            <goals>
+              <goal>test</goal>
+            </goals>
+          </execution>
+        </executions>
+        <configuration>
+          <warnOnReflection>true</warnOnReflection>
+          <temporaryOutputDirectory>true</temporaryOutputDirectory>
+          <clojureOptions>-Dbasedir=${basedir}</clojureOptions>
+          <compileDeclaredNamespaceOnly>true</compileDeclaredNamespaceOnly>
+          <sourceDirectories>
+            <sourceDirectory>src/main/clj</sourceDirectory>
+          </sourceDirectories>
+          <testSourceDirectories>
+            <testSourceDirectory>src/test/clj</testSourceDirectory>
+          </testSourceDirectories>
+          <testScript>${basedir}/src/test/script/runtests.clj</testScript>
+        </configuration>
+        
+      </plugin>
+    </plugins>
+  </build>
+</project>

src/main/clj/org/bituf/fountain/jdbc.clj

+(ns org.bituf.fountain.jdbc
+  "Functions that deal with SpringJDBC classes and functions. Note that calls
+  related to SimpleJdbcInsert and SimpleJdbcCall use database/JDBC metadata -
+  you can use them only if your database and the JDBC driver support them."
+  (:import
+    (java.util List Map)
+    (javax.sql DataSource)
+    (org.springframework.jdbc.core.simple SimpleJdbcTemplate SimpleJdbcInsert
+                                          SimpleJdbcCall)
+    (org.springframework.jdbc.support     KeyHolder))
+  (:require
+    [org.bituf.clj-miscutil :as mu]
+    [org.bituf.clj-dbspec   :as sp]))
+
+
+(def ^{:doc "Fountain-JDBC version (only major and minor)"}
+      version [0 1])
+
+
+(defn make-context
+  "Return a map with the following key associated to its respective value:
+    :org.bituf.fountain.jdbc.sjt - SimpleJdbcTemplate
+  The return value can be used in 'with-context' as argument.
+  See also:
+    with-context
+    clj-dbspec/*dbspec*"
+  [& {:keys [^DataSource datasource]
+      :or   {datasource  nil}}]
+  (let [ds  (or datasource (:datasource sp/*dbspec*))
+        sjt ^SimpleJdbcTemplate (SimpleJdbcTemplate. datasource)]
+     {:fountain.jdbc.sjt sjt}))
+
+
+(defmacro with-context
+  "Merge context with clj-dbspec/*dbspec* and execute body of code."
+  [context & body]
+  `(sp/with-dbspec ~context ~@body))
+
+
+(defn- ^SimpleJdbcTemplate get-sjt
+  "Get SimpleJdbcTemplate from context"
+  ([context] (:fountain.jdbc.sjt context))
+  ([] (get-sjt sp/*dbspec*)))
+
+
+(defn- ^SimpleJdbcInsert get-sji
+  "Get SimpleJdbcInsert from context"
+  ([context] (:fountain.jdbc.sji context))
+  ([] (get-sji sp/*dbspec*)))
+
+
+(defn show-sql
+  "Print the SQL statement if the *show-sql* flag is true."
+  [sql]
+  (when (:show-sql sp/*dbspec*)
+    (println sql)))
+
+
+(defmacro with-query-args
+  "Transform 'args' as suitable for SimpleJdbcTemplate method calls and bind
+  to 'qargs' with respective type hints, then call body of code in that context.
+  The two kinds of args supported are:
+  1. a map <String key, ? param value> (for SQL with named parameters), or
+  2. an array (for SQL with standard ? placeholders)
+  See also:
+  1. http://kotka.de/blog/2009/12/with-meta_and_the_reader.html
+  2. http://j.mp/bO0nmR (Clojure Google group)"
+  [[qargs args] & body]
+  `(cond
+     (map?      ~args) (let [~qargs (mu/keys-to-str ~args)]  ; Map<String, ?>
+                         ~@body)
+     (coll?     ~args) (let [~(with-meta qargs ; Object[]
+                                {:tag "[Ljava.lang.Object;"}) (into-array ~args)]
+                         ~@body)
+     (mu/array? ~args) (let [~(with-meta qargs ; Object[]
+                                {:tag "[Ljava.lang.Object;"}) ~args]
+                         ~@body)
+     :else (mu/illegal-arg-value "args"
+             "either map, or collection, or array" ~args)))
+
+
+(defn query-for-int
+  "Execute query and return integer value."
+  ([^String sql args]
+    (with-query-args [qargs args]
+      (show-sql sql)
+      (.queryForInt (get-sjt) sql qargs)))
+  ([sql]
+    (query-for-int sql {})))
+
+
+(defn query-for-long
+  "Execute query and return long value."
+  ([^String sql args]
+    (with-query-args [qargs args]
+      (show-sql sql)
+      (.queryForLong (get-sjt) sql qargs)))
+  ([sql]
+    (query-for-long sql {})))
+
+
+(defn query-for-map
+  "Execute query and return a row (expressed as a map)."
+  ([^String sql args]
+    (with-query-args [qargs args]
+      (mu/map-keys (:db-to-clj sp/*dbspec*)
+        (do
+          (show-sql sql)
+          (.queryForMap (get-sjt) sql qargs)))))
+  ([sql]
+    (query-for-map sql {})))
+
+
+(defn query-for-list
+  "Execute query and return a lazy list of rows (each row is a map)."
+  ([^String sql args]
+    (with-query-args [qargs args]
+      (map #(mu/map-keys (:db-to-clj sp/*dbspec*) %)
+        (do
+          (show-sql sql)
+          (.queryForList (get-sjt) sql qargs)))))
+  ([sql]
+    (query-for-list sql {})))
+
+
+(defn update
+  "Execute update-query and return integer result (number of rows affected)."
+  ([^String sql args]
+    (with-query-args [qargs args]
+      (show-sql sql)
+      (.update (get-sjt) sql qargs)))
+  ([sql]
+    (update sql {})))
+
+
+(defn batch-update
+  "Execute update-query in a batch using the query parameters. 'batch-args' is
+  1. either a list of argument lists
+  2. or a list of named param-value maps
+  Return a lazy list of integers, each being the number of rows affected."
+  [^String sql batch-args]
+  (let [args1        (first batch-args)
+        is-map       (map? args1)
+        is-seq       (or (coll? args1) (mu/array? args1))
+        show-sql-fn  #(show-sql (format "(Batch-size: %d) -- %s"
+                                  (count batch-args) sql))
+        result-array (cond ; returns an int array, or throws exception
+                       is-map (let [^"[Ljava.util.Map;" args-array ; Map<String, ?>[]
+                                    (into-array (map mu/keys-to-str batch-args))]
+                                (show-sql-fn)
+                                (.batchUpdate (get-sjt) sql args-array))
+                       is-seq (let [^List args-list  ; List<Object[]>
+                                    (map into-array batch-args)]
+                                (show-sql-fn)
+                                (.batchUpdate (get-sjt) sql args-list))
+                       :else (mu/illegal-arg-value "batch-args"
+                               "either list of lists, or list of maps"
+                               batch-args))]
+    (map identity result-array)))
+
+
+;; -------------------------
+;;    METADATA  functions
+;;
+;; Functions that take advantage of database metadata to limit the amount of
+;; configuration needed - they work with SimpleJdbcInsert and SimpleJdbcCall.
+;; -------------------------
+
+(defn ^SimpleJdbcInsert make-sji
+  "Create an instance of org.springframework.jdbc.core.simple.SimpleJdbcInsert
+  based on given arguments and return the same. The instance is thread-safe and
+  can be re-used across any number of calls.
+  Arguments:
+    table-name  (Clojure form) database table name to insert row(s) into
+  Optional arguments:
+    :datasource (DataSource, default Clj-DBSpec/*dbspec*) data source
+    :gencols    (collection) column names with generated values
+    :catalog    (Clojure form, default Clj-DBSpec/*dbspec*) catalog name
+    :schema     (Clojure form, default Clj-DBSpec/*dbspec*) schema name
+    :use-meta   (Boolean, default true) whether to use database metadata"
+  [table-name
+   & {:keys [^DataSource datasource gencols ^String catalog ^String schema
+             use-meta]
+      :or   {datasource  (:datasource sp/*dbspec*)
+             gencols  []
+             catalog  (:catalog sp/*dbspec*)
+             schema   (:schema  sp/*dbspec*)
+             use-meta true}}]
+  (let [v-gencols (mu/as-vector gencols)]
+    (-> (SimpleJdbcInsert. datasource)
+      (.withTableName (sp/db-iden table-name))
+      (#(if (mu/not-empty? v-gencols)
+          (.usingGeneratedKeyColumns ^SimpleJdbcInsert %
+            ^"[Ljava.lang.String;" (into-array String
+                                     (map sp/db-iden v-gencols))) %))
+      (#(if schema   (.withSchemaName  ^SimpleJdbcInsert % schema)  %))
+      (#(if catalog  (.withCatalogName ^SimpleJdbcInsert % catalog) %))
+      (#(if use-meta %  ; returns SimpleJdbcInsertOperations
+          (.withoutTableColumnMetaDataAccess ^SimpleJdbcInsert %))))))
+
+
+;; -----------------------
+;;    INSERT  functions
+;; -----------------------
+
+
+(defn show-insert-sql
+  "Print the Insert SQL."
+  [msg ^SimpleJdbcInsert sji]
+  (show-sql (str msg " - " (.getInsertString sji))))
+
+
+(defn insert
+  "Insert row and return the number (int) of affected rows. This function is not
+  suitable if you want to retrieve generated column keys.
+  See also: insert-give-id, insert-give-idmap"
+  [^SimpleJdbcInsert sji row]
+  ;; sji.withTableName("tableName")
+  ;; .execute(/* Map<String, Object> */ row); // int
+  (try
+    (.execute sji ^Map (mu/map-keys sp/db-iden row))
+    (finally
+      (mu/maybe (show-insert-sql "Returning affected row count: " sji)))))
+
+
+(defn insert-give-id
+  "Insert row and return generated ID."
+  [^SimpleJdbcInsert sji row]
+  ;; sji.withTableName("tableName")
+  ;; .executeAndReturnKey(/* Map<String, Object> */ row); // Number
+  (try
+    (.executeAndReturnKey sji ^Map (mu/map-keys sp/db-iden row))
+    (finally
+      (mu/maybe (show-insert-sql "Returning generated ID: " sji)))))
+
+
+(defn insert-give-idmap
+  "Insert row and return generated ID map (for multiple columns)."
+  [^SimpleJdbcInsert sji row]
+  ;; sji.withTableName("tableName")
+  ;; .executeAndReturnKeyHolder(/* Map<String, Object> */ row)
+  ;; .getKeys(); // Map<String, Object>
+  (try
+    (mu/map-keys sp/clj-iden
+      (.getKeys ^KeyHolder (.executeAndReturnKeyHolder sji
+                             ^Map (mu/map-keys sp/db-iden row))))
+    (finally
+      (mu/maybe (show-insert-sql "Returning generated ID Map: " sji)))))
+
+
+(defn insert-batch
+  "Insert rows in a batch and return a lazy list containing number of affected
+  rows per insertion."
+  [^SimpleJdbcInsert sji batch-rows]
+  ;; sji.withTableName("tableName")
+  ;; .executeBatch(/* Map<String, Object>[] */ batchRows); // int[]
+  (try
+    (->> batch-rows
+      (map #(mu/map-keys sp/db-iden %))
+      ^"[Ljava.util.Map;" into-array
+      (.executeBatch sji)
+      (map identity))
+    (finally
+      (mu/maybe (show-insert-sql (format "Batch INSERT of size %d"
+                                   (count batch-rows)) sji)))))

src/test/clj/dbconfig.properties

+# Which database to run tests on
+db.mysql=true
+db.pgsql=false
+
+# Other DB connection properties
+db.hostport=localhost
+db.username=root
+db.password=root
+db.name=bituf

src/test/clj/org/bituf/fountain/test_jdbc.clj

+(ns org.bituf.fountain.test-jdbc
+  (:import
+    (java.sql Connection Statement))
+  (:require
+    [clojure.java.io         :as io]
+    [clojure.pprint          :as pp]
+    [org.bituf.fountain.jdbc :as jd]
+    [org.bituf.clj-dbcp      :as cp]
+    [org.bituf.clj-miscutil  :as mu]
+    [org.bituf.clj-dbspec    :as sp])
+  (:use clojure.test))
+
+
+(def ds (cp/h2-memory-datasource))
+
+
+(def dbspec (sp/make-dbspec ds))
+
+
+(def context (merge (jd/make-context :datasource ds) dbspec))
+
+
+(defmacro with-stmt
+  [st & body]
+  `(sp/with-connection
+     (with-open [~st (.createStatement ^Connection (:connection sp/*dbspec*))]
+       ~@body)))
+
+
+(defn setup
+  []
+  (with-stmt ^Statement st
+    (mu/maybe (.execute st "DROP TABLE sample"))
+    (.execute st "CREATE TABLE sample
+                   (sample_id INT         NOT NULL PRIMARY KEY AUTO_INCREMENT,
+                    name      VARCHAR(30) NOT NULL,
+                    age       INT)")
+    (.execute st "INSERT INTO sample (name, age)
+                  VALUES ('Harry', 30)")))
+
+
+(defn setup2
+  []
+  (with-stmt ^Statement st
+    (mu/maybe (.execute st "DROP TABLE sample2"))
+    (.execute st "CREATE TABLE sample2
+                   (sample_id INT         NOT NULL PRIMARY KEY AUTO_INCREMENT,
+                    extra_id  INT         NOT NULL AUTO_INCREMENT,
+                    name      VARCHAR(30) NOT NULL,
+                    age       INT)")
+    (.execute st "INSERT INTO sample2 (name, age)
+                  VALUES ('Harry', 30)")))
+
+
+(defn fail
+  ([msg] (is false msg))
+  ([] (is false)))
+
+
+(deftest test-query-for-int
+  (testing "test-query-for-int"
+    (jd/with-context context
+      (setup)
+      (is (= 30 (jd/query-for-int "SELECT age FROM sample WHERE age = 30")))
+      (is (= 30 (jd/query-for-int "SELECT age FROM sample WHERE age = ?" [30])))
+      (is (= 30 (jd/query-for-int "SELECT age FROM sample WHERE age = :age"
+                  {:age 30}))))))
+
+
+(deftest test-query-for-long
+  (testing "test-query-for-long"
+    (jd/with-context context
+      (setup)
+      (is (= 30 (jd/query-for-long "SELECT age FROM sample WHERE age = 30")))
+      (is (= 30 (jd/query-for-long "SELECT age FROM sample WHERE age = ?" [30])))
+      (is (= 30 (jd/query-for-long "SELECT age FROM sample WHERE age = :age"
+                  {:age 30}))))))
+
+
+(deftest test-query-for-map
+  (testing "test-query-for-map"
+    (jd/with-context context
+      (setup)
+      (is (= {:age 30}
+            (jd/query-for-map "SELECT age FROM sample WHERE age = 30")))
+      (is (= {:age 30}
+            (jd/query-for-map "SELECT age FROM sample WHERE age = ?" [30])))
+      (is (= {:age 30}
+            (jd/query-for-map "SELECT age FROM sample WHERE age = :age"
+              {:age 30}))))))
+
+
+(deftest test-query-for-list
+  (testing "test-query-for-list"
+    (jd/with-context context
+      (setup)
+      (is (= [{:age 30}]
+            (jd/query-for-list "SELECT age FROM sample WHERE age = 30")))
+      (is (= [{:age 30}]
+            (jd/query-for-list "SELECT age FROM sample WHERE age = ?" [30])))
+      (is (= [{:age 30}]
+            (jd/query-for-list "SELECT age FROM sample WHERE age = :age"
+              {:age 30}))))))
+
+
+(deftest test-update
+  (testing "test-update"
+    (jd/with-context context
+      (setup)
+      (is (= 1 (jd/update "UPDATE sample SET age=40 WHERE age=30")))
+      (is (= 1 (jd/update "UPDATE sample SET age=50 WHERE age=?" [40])))
+      (is (= 1 (jd/update "UPDATE sample SET age=60 WHERE age=:age"
+                 {:age 50}))))))
+
+
+(deftest test-batch-update
+  (testing "test-batch-update"
+    (jd/with-context context
+      (setup)
+      (is (= [1 1] (jd/batch-update
+                     "INSERT INTO sample (name, age) VALUES (:name, :age)"
+                     [{:name "Hello" :age 20}
+                      {:name "World" :age 40}]))))))
+
+
+(deftest test-insert
+  (testing "test-insert"
+    (jd/with-context context
+      (setup)
+      (let [sji (jd/make-sji :sample)]
+        (is (= 1 (jd/insert sji {:name "Hello" :age 20})))))))
+
+
+(deftest test-insert-give-id
+  (testing "test-insert-give-id"
+    (jd/with-context context
+      (setup)
+      (let [sji (mu/! (jd/make-sji "sample" :gencols :sample-id))]
+        (is (= 2 (jd/insert-give-id sji {:name "Hello" :age 20})))))))
+
+
+(deftest test-insert-give-idmap
+  (testing "test-insert-give-idmap"
+    (jd/with-context context
+      (setup)
+      (let [sji (jd/make-sji :sample :gencols :sample-id)]
+        (is (= 2 (first (vals (jd/insert-give-idmap sji
+                                {:name "Hello" :age 20}))))))
+      (setup2)
+      (let [sji (jd/make-sji :sample2 :gencols [:sample-id :extra-id])]
+        (is (= 2 (first (vals (jd/insert-give-idmap sji
+                                {:name "Hello" :age 20})))))))))
+
+
+(deftest test-insert-batch
+  (testing "test-insert-batch"
+    (jd/with-context context
+      (setup)
+      (let [sji (jd/make-sji :sample :gencols :sample-id)]
+        (is (= [1 1]
+              (jd/insert-batch sji [{:name "Hello" :age 20}
+                                    {:name "World" :age 30}])))
+        ))))
+
+
+(defn test-ns-hook []
+  (test-query-for-int)
+  (test-query-for-long)
+  (test-query-for-map)
+  (test-query-for-list)
+  (test-update)
+  (test-batch-update)
+  (test-insert)
+  (test-insert-give-id)
+  (test-insert-give-idmap)
+  (test-insert-batch))

src/test/script/runtests.clj

+(ns runtests
+  (:use org.bituf.fountain.test-jdbc)
+  (:use clojure.test))
+
+
+(run-tests
+  'org.bituf.fountain.test-jdbc)
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.