diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/clojure/contrib/sql/sql.clj | 55 | ||||
| -rw-r--r-- | src/clojure/contrib/sql/test/test.clj | 61 |
2 files changed, 71 insertions, 45 deletions
diff --git a/src/clojure/contrib/sql/sql.clj b/src/clojure/contrib/sql/sql.clj index 467e6e16..e1becf56 100644 --- a/src/clojure/contrib/sql/sql.clj +++ b/src/clojure/contrib/sql/sql.clj @@ -1,10 +1,10 @@ -;; Copyright (c) Stephen C. Gilardi. All rights reserved. -;; The use and distribution terms for this software are covered by the -;; Common Public License 1.0 (http://opensource.org/licenses/cpl.php) -;; which can be found in the file CPL.TXT at the root of this distribution. -;; By using this software in any fashion, you are agreeing to be bound by -;; the terms of this license. -;; You must not remove this notice, or any other, from this software. +;; Copyright (c) Stephen C. Gilardi. All rights reserved. The use and +;; distribution terms for this software are covered by the Common Public +;; License 1.0 (http://opensource.org/licenses/cpl.php) which can be found +;; in the file CPL.TXT at the root of this distribution. By using this +;; software in any fashion, you are agreeing to be bound by the terms of +;; this license. You must not remove this notice, or any other, from this +;; software. ;; ;; sql.clj ;; @@ -13,17 +13,16 @@ ;; See clojure.contrib.sql.test for an example ;; ;; scgilardi (gmail) -;; 23 April 2008 +;; Created 2 April 2008 (ns clojure.contrib.sql (:import (java.sql DriverManager Connection PreparedStatement ResultSet))) -(defn get-connection +(defn connection "Attempts to get a connection to a database via a jdbc URL" [subprotocol db-name] - (let [url (str "jdbc:" subprotocol ":" db-name)] - (DriverManager/getConnection url))) + (DriverManager/getConnection (format "jdbc:%s:%s" subprotocol db-name))) (defmacro with-connection "Evaluates body in the context of a connection to a database. Any updates @@ -32,24 +31,24 @@ [con init & body] `(with-open ~con ~init (try - (.setAutoCommit ~con false)) + (.setAutoCommit ~con false) ~@body (.commit ~con) (catch Exception e# - (.rollback ~con) - (throw (Exception. "transaction rolled back" e#))))) + (.rollback ~con) + (throw (Exception. "transaction rolled back" e#)))))) -(defn execute-commands - "Executes a sequence of SQL commands that do not return results" - [con commands] +(defn do-commands + "Executes SQL commands that do not return results" + [con & commands] (with-open stmt (.createStatement con) (doseq cmd commands (.addBatch stmt cmd)) (.executeBatch stmt))) -(defn execute-prepared-statement - "Executes a prepared statement with a sequence of parameter sets" - [con sql sets] +(defn do-prepared + "Executes a prepared statement with parameter sets" + [con sql & sets] (with-open stmt (.prepareStatement con sql) (doseq set sets (doseq [index value] (map vector (iterate inc 1) set) @@ -57,7 +56,21 @@ (.addBatch stmt )) (.executeBatch stmt))) -(defmacro with-query-results +(defn create-table + "Creates a table given a name and column specifications" + [con name & cols] + (do-commands con + (format "create table %s (%s)" + name + (apply str (interpose "," cols))))) + +(defn drop-table + "Drops a table give its name" + [con name] + (do-commands con + (format "drop table %s" name))) + +(defmacro with-results "Executes a query and then evaluates body repeatedly with rec bound to each of the generated results in turn" [rec con sql & body] diff --git a/src/clojure/contrib/sql/test/test.clj b/src/clojure/contrib/sql/test/test.clj index f098c9aa..90edd457 100644 --- a/src/clojure/contrib/sql/test/test.clj +++ b/src/clojure/contrib/sql/test/test.clj @@ -1,47 +1,60 @@ +;; Copyright (c) Stephen C. Gilardi. All rights reserved. The use and +;; distribution terms for this software are covered by the Common Public +;; License 1.0 (http://opensource.org/licenses/cpl.php) which can be found +;; in the file CPL.TXT at the root of this distribution. By using this +;; software in any fashion, you are agreeing to be bound by the terms of +;; this license. You must not remove this notice, or any other, from this +;; software. +;; +;; test.clj +;; +;; test/example for clojure.contrib.sql.test +;; +;; scgilardi (gmail) +;; Created 13 September 2008 + (ns clojure.contrib.sql.test (:use clojure.contrib.sql)) (Class/forName "org.apache.derby.jdbc.EmbeddedDriver") (defn db [] - (get-connection "derby" "/tmp/clojure.contrib.sql.test.db;create=true")) - -(defn db-drop [] - (with-connection con (db) - (try - (execute-commands con - ["drop table fruit"]) - (catch Exception e)))) + (connection "derby" "/tmp/clojure.contrib.sql.test.db;create=true")) (defn db-write [] - (db-drop) (with-connection con (db) - (execute-commands con - ["create table fruit (name varchar(32), appearance varchar(32), cost int, grade real)"]) - (seq - (execute-prepared-statement con - "insert into fruit values (?, ?, ?, ?)" - [["Apple" "red" 59 87] - ["Banana" "yellow" 29 92.2] - ["Peach" "fuzzy" 139 90.0] - ["Orange" "juicy" 89 88.6]])))) - + (try + (drop-table con "fruit") + (catch Exception e)) + (create-table con + "fruit" + "name varchar(32)" + "appearance varchar(32)" + "cost int" + "grade real") + (do-prepared con + "insert into fruit values (?, ?, ?, ?)" + ["Apple" "red" 59 87] + ["Banana" "yellow" 29 92.2] + ["Peach" "fuzzy" 139 90.0] + ["Orange" "juicy" 89 88.6]))) + (defn db-read [] (with-connection con (db) - (with-query-results rec con + (with-results rec con "select * from fruit" (println rec)))) (defn db-grade-a [] (with-connection con (db) - (with-query-results rec con + (with-results rec con "select name, cost from fruit where grade >= 90" (println rec)))) (defn db-exception [] (with-connection con (db) - (execute-prepared-statement con + (do-prepared con "insert into fruit (name, appearance) values (?, ?)" - [["Grape" "yummy"] - ["Pear" "bruised"]]) + ["Grape" "yummy"] + ["Pear" "bruised"]) (throw (Exception. "an exception")))) |