この記事は、Clojure Contrib Advent Calendar 2013 ２日目の記事です。
今日のお題は、clojure.java.jdbc です。その名の通り、JDBC の
wrapper library で、Clojureから SQL Database へのアクセスを容易にして
くれます。
このライブラリについては、使うバージョンに気をつける必要があります。 2013/11/30 現在、beta ではありますが、0.3.0-beta2 が最新となります。 特に理由がない限り、これから採用するといった場合には、このバージョンも しくはこれより新しいバージョンを採用するべきです(同じ 0.3.0- でも beta1 と beta2 には 非互換の 変更が入っています)。
機能的には、JDBC の wrapper として単純な SQL 操作のみとなっており、
- データベースへの接続(接続とリソースの解放)
- トランザクション(処理終了時の commit、Exception発生時の rollback)
- query の発行(取得したレコードは map または vector のいずれかの形で取得)
- DML(insert/update/delete) および execute (DML + stored procedure 呼び出し)
といった程度にまで機能を絞り込んでいます。
かつて 0.2.3 までの頃は、簡単な DSL としての機能を一部包含しており、
Clojure の S式の形で簡単な Query なら記述できました。
そうした機能が0.3.0-beta1 では deprecated となり、さらに
-beta2 では、名前空間がclojure.java.sql.deprecated に変わってし
まいました。
ちなみに、0.2.3までに存在していた DSL としての機能は、java-jdbc/dsl
に移動したようです(名前空間はjava-jdbc.ddl、java-jdbc.sql)。
また、SQL を DSL として取り扱う取り組みとしては、上記の java-jdbc/dsl プロ
ジェクトの他にも、
- HoneySQL https://github.com/jkk/honeysql
- SQLingvo https://github.com/r0man/sqlingvo
- Korma http://sqlkorma.com/
といったものがあります。
今回は、clojure.java.jdbc についての説明になります。
clojure.java.jdbc 本体と、JDBCドライバ のインストールが必要です。
leiningen project.clj の dependency の設定例を示します。
[org.clojure/java.jdbc "0.3.0-beta2"];; Apache Derby
[org.apache.derby/derby "10.8.1.2"]
;; HSQLDB
[hsqldb/hsqldb "1.8.0.10"]
;; Microsoft SQL Server using the jTDS driver
[net.sourceforge.jtds/jtds "1.2.4"]
;; MySQL
[mysql/mysql-connector-java "5.1.25"]
;; PostgreSQL
[postgresql/postgresql "9.1-901.jdbc4"]
;; SQLite
[org.xerial/sqlite-jdbc "3.7.2"]Oracle の JDBC ドライバについては、 Oracle のサイト から自分でダウンロードする必要があります(要Oracle Technology Network Account)。
Oracle 11.2.0.4のJDBCドライバ ojdbc6.jar をダウンロードしてから、
$ mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 \
-Dversion=11.2.0.4 -Dpackaging=jar -Dfile=ojdbc6.jar -DgeneratePom=trueで自分のローカルリポジトリに登録し、project.clj には
;; project.clj
[com.oracle/ojdbc6 "11.2.0.4"]と記述しておきます。
さて、いよいよ使い方の説明に入ります。
まずは clojure.java.jdbc を require しておきましょう(JDBCドライバ
をrequire する必要はありません)。
(ns jdbc-study.core
(:require [clojure.java.jdbc :refer :all]))clojure.java.jdbc API のドキュメントは ここ http://clojure.github.io/java.jdbc/
にあります。関数が多数記載されているのですが、実は、ほとんどの場合これ
らの中の一部の高レベル関数のみを使用すれば事足りるはずです。具体的には、
- db-transaction
- query
- execute!
- insert!
- update!
- delete!
- db-set-rollback-only!
です。
以下に典型的な使い方を例示し説明していきます。
;; (1)
;; 以下は postgresql の例です。
(def db-spec {:classname "org.postgresql.Driver"
:subprotocol "postgresql"
:subname "//localhost:5432/mydb"
:user "<username>"
:password "<password>"})
;; :
;; :
;; (2)
(db-transaction [db db-spec]
;; (3)
(insert! db :users '(:id :name) '((1 "user1") (2 "user2")))
;; (4)
)DBに接続するには、接続に必要な情報(接続先DB、ユーザ名、パスワード等)が 必要です。接続先の定義の方法は、
- DriverManager を直接使用する場合
- Connection Pool を使った DataSource を使用する場合
- JNDI により定義された DataSource を使用する場合
等、いくつかのパターンが用意されています。上記(1)では PostgreSQL で
DriverManager を直接使用するパターンを示しています。
詳細はドキュメント の
get-connection の項を参照してください。
db-transaction マクロを使用して、DB接続の確立、処理後の
commit/rollback、コネクションのclose、を行います。
上記(2) の例で言うと、
db-specにもとづいてjava.sql.Connectionオブジェクトを取得db変数の設定(内容はdb-spec+{:connection <Connection>})みたいな感じbody内部ではdbを使って各処理を行う- Exception が発生していれば rollback、そうでなければ commit を行う
- connection を解放する
といった動作になります。ただし、上記 body の中で、
(db-set-rollback-only! db)と指定されると、Exception が発生していない場合でも rollback されます。
clojure.java.sql/query 関数を使用します。
(query db
["select name,email from users where id = ?" 1] ;;(a)
:result-set-fn doall ;;(b)
:row-fn identity ;;(c)
:identifiers clojure.string/lower-case ;;(d)
:as-arrays? false) ;;(e)
;;=> ({ :name "太郎", :email "taro@example.org" })この関数の中で PreparedStatement の作成、ResultSet の作成だけでな
く、close も面倒見てくれるので、これを使いましょう、ということです。
SQL文の本体、および必要な数だけバインド変数を指定した vector を指定し ます。
queryした結果の ResultSet 全体に対して適用される関数を指定します。デ
フォルトは、(e) :as-arrays? の値により変わります。
- :as-arrays? = true の時は、vec
- :as-arrays? = false の時は、doall
となります。
各行の ResultSet に対して適用される関数を指定します。デフォルトは
identity です。
取得した列の列名を変換する関数を指定します。下記(d):as-arrays が
false の場合の列名の取り扱いに影響します。デフォルトは
clojure.string/lower-case です。
true を指定すると、各行のデータが vector の形で取得できます。
false の場合は、列名と値の map の形で各行のデータが取得できます。
デフォルトは false です。trueの場合の実行例を以下に示します。
(query db ["select name,email from users where id = ?" 1] :as-arrays? true)
;;=> [[ "太郎" "taro@example.org"]]SQL文の書き方によっては、SELECT文が返す列名が重複する場合があります。 例えば、
select users.id, users.name, countries.name
from users, countries
where users.country_id = countries.id
and ...のような場合(.name に as で別名を付け忘れるのが悪い、という話はありま
すが...)です。このような場合にも、clojure.java.sql/query は、
;=> ({:id 1, :name "太郎", :name_1 "Japan"})のように列が識別できるよう自動的に_N(Nは数値)を付与して割り振ってくれます。
non-select なSQL(更新系処理)を実行するための関数です。
(execute! db ["update foo set bar = ? where baz = ?" 1 0])
;; "update foo set bar = ? where baz = ?" を、パラメータ bar = 1, baz = 0 で
;; 実行するまた、ストアドプロシージャを呼び出すこともできます(但し、out パラメータを受け取る ことはできません)。
ストアドプロシージャ INSERT_FOO というものがあったとして、
-- Oracle での例です
CREATE OR REPLACE PROCEDURE INSERT_FOO(pID NUMBER, pTITLE VARCHAR2)
IS
BEGIN
INSERT INTO FOO(ID, TITLE) VALUES(pID, pTITLE);
END INSERT_FOO;
/begin ... end; で囲うことにより、ストアドプロシージャを呼び出すことができます。
;; Oracle での例です
(execute! db ["BEGIN INSERT_FOO(?, ?); END;" 1 0])
;=> (0) ;; 実際にはデータは登録されている。行の追加です。列名のリストにつづいて追加する行をリストの形で複数指定できます。 １回のオペレーションで複数行を登録できます。
(insert! db :foo '(:bar :baz) '(1 10) '(2 20))
;; "insert into foo (bar, baz) values (?, ?)"
;; を、パラメータ (1, 10) 、 (2, 20) で実行する行の更新です。更新するキーと値の組み合わせを map で指定し、さらに検索 条件を vector で指定します。更新された行数を含むリストを返します。
(update! db :foo {:bar 1 :baz 2} ["bar = ?" 111])
;; "update foo set bar = ?, baz = ? where bar = ?" を、
;; パラメータ 1, 2, 111 で実行する
;;=> (1)行の削除です。テーブル名をキーワードで指定できたりしますが、やっている
ことは SQL 文とパラメータを組み立てて execute! をしているだけです。
削除した行数を含むリスト返します。
(delete! db :foo ["baz = ?" 111])
;; "delete from foo where baz = ?" を、パラメータ 111 で実行する
;;=> (1)CREATE TABLE 相当のSQL文の文字列を返します。ただし、テーブルを定義 するには機能的に貧弱な感じなので、あまり使い出がないでしょう。
(create-table-ddl :foo '(:a "int") '(:b "char(2)"))
;=> "CREATE TABLE foo (a int, b char(2))"
;
;; Oracle だと upper-case っぽいので upper-case にしてみる
(create-table-ddl :foo '(:a "number(8)") '(:b "char(2)") :entities clojure.string/upper-case)
;=> "CREATE TABLE FOO (A NUMBER(8), B CHAR(2))"DROP TABLE 相当のSQL文の文字列を返します。テーブルを削除する分には これで良いのかもしれませんが、あまり必要性を感じません。
(drop-table-ddl :foo)
;=> "DROP TABLE foo"
;
;; create-table-ddl と同様に upper-case にできる
(drop-table-ddl :foo :entities clojure.string/upper-case)
;=> "DROP TABLE FOO"Clojure の REPL から随時関数を定義していきながら開発をすると思いますが、
いちいち (db-transaction [db dbspec] ... ) で動かしたい関数を囲うのは
面倒です。REPL から呼び出す場合には、dbspec は定義しておいた上で、
(def db (add-connection db-spec (get-connection db-spec)))とすると、query/execute!/insert!/update!/delete! に渡すパラメータ db
を作ることができます。
とある仕事(Oracle)で、「CHAR(8) とかで定義されているが中身が "111△△△△△" (△は空白)」 のように DB 上に登録された項目を検索したい。こういう時に
(query ["select * from table1 where column1 = ?" "111"])などとするとヒットしない。パラメータを "111△△△△△" (△は空白)のように後ろに 空白を埋めるとヒットしました...という話。まあ、よくある話ですが、Oracle だとVARCHAR2 使え、ってことですかね。でもテーブル設計今更変更できないので...
これまた Oracle ですが、（プロジェクトのルール等で）日本語の「全角英数 字」の列名を含むテーブル定義をした場合、例えばワークを表す「ＷＫ」が、 全角小文字の「ｗｋ」に変換されてしまい、
(query ["select ＷＫ＿列１, ＷＫ＿列２ from テーブル１"])
;=> ({:ｗｋ＿列１ 1, :ｗｋ＿列２ "aaa"})みたいになって気持ち悪かった、という話。さすがにテーブル定義で「ＷＫ」
と言っておきながら、取得してみたら「ｗｋ」は気持ち悪い。ので、仕方なし
に :identifiers を identity に設定しました。
(query ["select ＷＫ＿列１, ＷＫ＿列２ from テーブル１"] :identifiers identity)
;=> ({:ＷＫ＿列１ 1, :ＷＫ＿列２ "aaa"})（ちょっとしたツールだったので、いちいちめんどくさかったけどマクロを定 義するほどのこともないという...）
ストアドプロシジャに処理をさせてその値を受け取る、といったときに、
outパラメータの指定ができるようになっていません。これはちょっと痛い
感じがします(そこは自作すればよいのですが...)。
Query を発行してデータを取得する際、ResultSet の型に応じて Java の適
切な型に変換する必要があります。変換の仕組みは IResultSetReadColumn
という protocol の result-set-read-column 関数で定義されています。
今のところその実装として ResultSet#getObject した値をそのまま
Object として取得、もしくは null の場合 nil にする、といったこと
しか行っていません。
DBの列の型に応じた変換を定義したい場合、protocol を拡張する方向で対応 していく、ものと思われます(がユーザに委ねられているのかな？)。
駆け足気味でしたが、ざっと clojure.java.jdbc のイメージは伝わりまし
たでしょうか。
JDBC経由で取得したデータ、あるいは JDBC経由でデータベースに更新するデー タは、Clojure の場合あまりJava の型を意識せずとも取り扱うことができま す。
データベースへのアクセスをするレイヤーを「副作用のレイヤー」としてしま えば、あとは Clojure お得意の immutable なデータ構造と collection/sequence 等々でビジネスロジックを記述できるので、うまく プログラムの構造を設計できれば、ビジネスロジック層およびそれより 上位層はテストをしやすい構造をとれるはずです。
いかがでしたでしょうか。JDBC は Clojure をエンタープライズ用途で使うに は避けて通れない道なので、少しでも皆様のお役に立てればと思っています。
明日はまた athos さんの担当(data.json)です。
よろしくお願いします。