---
title: ClojureのデータベースアクセスライブラリClojureQLを使う
tags: []
categories: ["Programming", "Lisp", "Clojure", "org", "clojureql"]
date: 2011-12-07T17:33:29Z
updated: 2011-12-14T16:47:24Z
---

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

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

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

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

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文について説明します。


  [1]: http://partake.in/events/393770ce-4637-4f07-bc14-a1f5120eab71
  [2]: http://clojureql.org/