ClojureによるDBアクセス

clojure

SQLiteを使用して、ClojureによるDBアクセス処理を行う。
処理の流れは、以下の通り。

  1. テーブル削除
  2. テーブル作成
  3. レコード登録
  4. レコード更新
  5. レコード削除
  6. レコード削除後、例外を発生させてロールバック

実行結果にある(1 1)、(1)、(2)は更新件数。更新処理後に検索して、レコード状態を表示するようにしている。

project.cljの中身

(defproject sqlite-sample "1.0.0-SNAPSHOT"
  :description "FIXME: write"
  :dependencies [[org.clojure/clojure "1.2.0"]
                 [org.clojure/clojure-contrib "1.2.0"]
                 [org.xerial/sqlite-jdbc "3.6.16"]]
  :dev-dependencies [[swank-clojure "1.2.0-SNAPSHOT"]]
  :main-class sqlite-sample.core)

サンプルコード

(ns sqlite-sample.core
  (:import [java.sql SQLException])
  (:use [clojure.contrib.sql])
  (:gen-class))

(Class/forName "org.sqlite.JDBC")
(defn contrib-sql
  []
  (with-connection {:classname "org.sqlite.JDBC"
                    :subprotocol "sqlite"
                    :subname "test.db"}
    ;; テーブル削除
    (try
      (drop-table :program)
      (catch SQLException e (.toString e)))

    ;; テーブル作成
    (create-table :program [:name :text] [:extension :text])

    ;; レコード登録
    (try
      (transaction
       (println  (insert-rows :program ["Clojure" "clj"] ["Java" "java"]))
       ;; (println (insert-values :program
       ;;                [:name :extension]
       ;;                ["Clojure" "clj"]
       ;;                ["Java" "java"]))
       ;; (println  (insert-records :program {:name "clojure" :extension "clj"}))
       )
       (catch Exception e (.toString e)))

    ;; 結果表示
    (with-query-results results ["select * from program"]
      (doseq [record results]
        (println "name = " (:name record))
        (println "extension  = " (:extension record))))

    ;; レコード更新
    (try
      (transaction
       (println)
       (println  (update-values :program ["name=? or name=?" "Clojure" "Java"] {:extension "clj"})))
       (catch Exception e (.toString e)))

    ;; 結果表示
    (with-query-results results ["select * from program"]
      (doseq [record results]
        (println "name = " (:name record))
        (println "extension  = " (:extension record))))

    ;; レコード削除
    (try
      (transaction
       (println)
       (println  (delete-rows :program ["name=?" "Clojure"])))
       (catch Exception e (.toString e)))

    ;; 結果表示
    (with-query-results results ["select * from program"]
      (doseq [record results]
        (println "name = " (:name record))
        (println "extension  = " (:extension record))))

    ;; レコード削除
    (try
      (transaction
       (println)
       (println  (delete-rows :program ["name=?" "Java"]))
       (throw (SQLException. "ERROR")))
       (catch Exception e (.toString e)))

    ;; 結果表示
    (with-query-results results ["select * from program"]
      (doseq [record results]
        (println "name = " (:name record))
        (println "extension  = " (:extension record))))

    ))

実行結果

user>  (load-file "src/sqlite_sample/core.clj")
#'sqlite-sample.core/contrib-sql
user> (sqlite-sample.core/contrib-sql)
(1 1)
name =  Clojure
extension  =  clj
name =  Java
extension  =  java

(2)
name =  Clojure
extension  =  clj
name =  Java
extension  =  clj

(1)
name =  Java
extension  =  clj

(1)
SQLException:
 Message: ERROR
 SQLState: null
 Error Code: 0
name =  Java
extension  =  clj
nil

参考