ClojureのデータベースアクセスライブラリClojureQLを使う

この記事は Clojure Advent Calendar 2011 の7日目の記事です。

ClojureでDBにアクセスするには

Javaのライブラリ(JPA/Hibernate/MyBatisなど)を使う
clojure.contrib.sqlを使う
その他clojure用ライブラリを使う

Javaのライブラリを使うとあんまりClojureっぽくないですし、contribのはネイティヴすぎてちょっと使いにくい。。ということで今回はClojureQLを使ってみます。

ClojureQLはDSLちっくにSQLを組み立てて、実行結果はマップで返ってくる。ClojureでJavaのO/Rマッパーを使っても、結局マップっぽく使いがちなので、元々マップで返ってくるのは良いと個人的に思う。

project.clj

mysqlで試してみます。

(defproject clojureql-sample "1.0.0-SNAPSHOT"
  :description "FIXME: write description"
  :dependencies [[org.clojure/clojure "1.2.1"]
                 [clojureql "1.0.0"]
                 [mysql/mysql-connector-java "5.1.13"]
                 ])

データベースオブジェクトを作成する

(def db {:classname "com.mysql.jdbc.Driver", 
             :subprotocol "mysql",
             :user "root", 
             :subname "//localhost:3306/example"})

テーブルオブジェクトを作成する

(table db :employee)
;; => SELECT employee.* FROM employee

実はこの返り値

(class (table db :employee))
;; => clojureql.core.RTable

clojure.core.RTableクラスという参照クラスで、値を取得するにはdereferenceする必要がありあす。

@(table db :employee)
;; => ({:age 30, :department_id 1, :address_id 1, :name "SMITH", :version 0, :id 1} {:age 28, :department_id 1, :address_id 2, :name "ALLEN", :version 0, :id 2} {:age 38, :department_id 1, :address_id 3, :name "WARD", :version 0, :id 3} {:age 40, :department_id 2, :address_id 4, :name "JONES", :version 0, :id 4})

この段階で初めてSQLが発行されます。

以降の説明では

(def employee (table db :employee))
(def department (table db :department))
(def address (table db :address))

を使います。

ClojureQLではこのテーブルオブジェクトを関係代数っぽい演算（メソッド）を使用して操作します。

選択する

(select employee (where (= :id 1)))
;; => SELECT employee.* FROM employee WHERE (id = 1)

where句がつきます。

@(select employee (where (= :id 1)))
;; => ({:age 30, :department_id 1, :address_id 1, :name "SMITH", :version 0, :id 1})

射影する

(project employee [:id :name])
;; => SELECT employee.id,employee.name FROM employee

指定したフィールドだけ取得します

@(project employee [:id :name])
;; => ({:name "SMITH", :id 1} {:name "ALLEN", :id 2} {:name "WARD", :id 3} {:name "JONES", :id 4})

制限する

(take employee 2)
;; => SELECT employee.* FROM employee LIMIT 2

LIMIT句がつきます。(Oracleは非対応かな)

@(take employee 2)
;; => ({:age 30, :department_id 1, :address_id 1, :name "SMITH", :version 0, :id 1} {:age 28, :department_id 1, :address_id 2, :name "ALLEN", :version 0, :id 2})

スキップする

(drop employee 2)
;; => SELECT employee.* FROM employee OFFSET 2

ソートする

(sort employee [:age :id])
;; => SELECT employee.* FROM employee ORDER BY age asc,id asc

order byがつきます。

@(sort employee [:age :id])
;; => ({:age 28, :department_id 1, :address_id 2, :name "ALLEN", :version 0, :id 2} {:age 30, :department_id 1, :address_id 1, :name "SMITH", :version 0, :id 1} {:age 38, :department_id 1, :address_id 3, :name "WARD", :version 0, :id 3} {:age 40, :department_id 2, :address_id 4, :name "JONES", :version 0, :id 4})

昇順、降順まで指定したいときは

(sort employee [:age#desc :id#asc])
;; => SELECT employee.* FROM employee ORDER BY employee.age desc,employee.id asc

これらの関数をチェインすることでSQLを構築できます。DSLチックになってきました。

(-> employee (project [:id :name :age]) (take 3) (drop 2) (sort [:id]))
;; => SELECT employee.id,employee.name,employee.age FROM employee ORDER BY id asc LIMIT 1 OFFSET 2

結合する

(join employee address (where (= :employee.address_id :address.id)))
;; => SELECT employee.*,address.* FROM employee JOIN address ON (employee.address_id = address.id)

第二引数にJOINの条件を書きます。

@(join employee address (where (= :employee.address_id :address.id)))
;; => ({:age 30, :department_id 1, :address_id 1, :name "SMITH", :version 0, :city "ALBANY", :street "A STREET", :id 1} {:age 28, :department_id 1, :address_id 2, :name "ALLEN", :version 0, :city "ALBANY", :street "B STREET", :id 2} {:age 38, :department_id 1, :address_id 3, :name "WARD", :version 0, :city "TRENTON", :street "C STREET", :id 3} {:age 40, :department_id 2, :address_id 4, :name "JONES", :version 0, :city "AUSTIIN", :street "D STREET", :id 4})

2つ以上JOINする場合はチェインします。

(-> employee
    (join address (where (= :employee.address_id
                            :address.id)))
    (join department (where (= :employee.department_id
                               :department.id)))
    (project [:id :name :age
              :address.city :address.street
              [:department.name :as :department]
              :department.location]))
;; => SELECT employee.id,employee.name,employee.age,address.city,address.street,department.name AS department,department.location FROM employee JOIN address ON (employee.address_id = address.id) JOIN department ON (employee.department_id = department.id)

結合するテーブルでカラム名が被るとエラーになるので、[:xxx :as :yyy]で名前を変えている点に注意。

@(-> employee (join address (where (= :employee.address_id :address.id))) (join department (where (= :employee.department_id :department.id))) (project [:id :name :age :address.city :address.street [:department.name :as :department] :department.location]))
;; => ({:age 30, :name "SMITH", :location "NEW YORK", :city "ALBANY", :street "A STREET", :department "ACCOUNTING", :id 1} {:age 28, :name "ALLEN", :location "NEW YORK", :city "ALBANY", :street "B STREET", :department "ACCOUNTING", :id 2} {:age 38, :name "WARD", :location "NEW YORK", :city "TRENTON", :street "C STREET", :department "ACCOUNTING", :id 3} {:age 40, :name "JONES", :location "DALLAS", :city "AUSTIIN", :street "D STREET", :department "RESEARCH", :id 4})

次回はINSERT/UPDATE/DELETE文について説明します。