Skip to content

Instantly share code, notes, and snippets.

@ponkore

ponkore/clojure.java.jdbc.md

Created December 2, 2013 03:52
Show Gist options
  • Save ponkore/7744766 to your computer and use it in GitHub Desktop.
Save ponkore/7744766 to your computer and use it in GitHub Desktop.
Download ZIP
clojure.java.jdbc について (Clojure Contrib Advent Calendar 2013 2日目の記事です)
Raw
clojure.java.jdbc.md

clojure.java.jdbc について

この記事は、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 プロ ジェクトの他にも、

といったものがあります。

今回は、clojure.java.jdbc についての説明になります。

下準備

clojure.java.jdbc 本体と、JDBCドライバ のインストールが必要です。

leiningen project.clj の dependency の設定例を示します。

clojure.java.jdbc 本体

[org.clojure/java.jdbc "0.3.0-beta2"]

JDBCドライバ(必要なものを一つ選択)

;; 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"]

と記述しておきます。

API の使用方法

さて、いよいよ使い方の説明に入ります。

下準備

まずは clojure.java.jdbc を require しておきましょう(JDBCドライバ をrequire する必要はありません)。

(ns jdbc-study.core
  (:require [clojure.java.jdbc :refer :all]))

clojure.java.jdbc API の典型的な使い方

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)
)

(1) DB接続先の定義

DBに接続するには、接続に必要な情報(接続先DB、ユーザ名、パスワード等)が 必要です。接続先の定義の方法は、

  • DriverManager を直接使用する場合
  • Connection Pool を使った DataSource を使用する場合
  • JNDI により定義された DataSource を使用する場合

等、いくつかのパターンが用意されています。上記(1)では PostgreSQL で DriverManager を直接使用するパターンを示しています。 詳細はドキュメント の get-connection の項を参照してください。

(2) db-transaction マクロの使用

db-transaction マクロを使用して、DB接続の確立、処理後の commit/rollback、コネクションのclose、を行います。

(db-transaction binding & body)

上記(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 されます。

(3) 各種データの操作

データの取得(select)

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 "[email protected]" })

この関数の中で PreparedStatement の作成、ResultSet の作成だけでな く、close も面倒見てくれるので、これを使いましょう、ということです。

(a) SQL文本体、およびバインド変数 (Required)

SQL文の本体、および必要な数だけバインド変数を指定した vector を指定し ます。

(b) :result-set-fn (Optional)

queryした結果の ResultSet 全体に対して適用される関数を指定します。デ フォルトは、(e) :as-arrays? の値により変わります。

  • :as-arrays? = true の時は、vec
  • :as-arrays? = false の時は、doall

となります。

(c) :row-fn (Optional)

各行の ResultSet に対して適用される関数を指定します。デフォルトは identity です。

(d) :identifiers (Optional)

取得した列の列名を変換する関数を指定します。下記(d):as-arrays が false の場合の列名の取り扱いに影響します。デフォルトは clojure.string/lower-case です。

(e) :as-arrays? (Optional)

true を指定すると、各行のデータが vector の形で取得できます。 false の場合は、列名と値の map の形で各行のデータが取得できます。

デフォルトは false です。trueの場合の実行例を以下に示します。

(query db ["select name,email from users where id = ?" 1] :as-arrays? true)
;;=> [[ "太郎" "[email protected]"]]

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は数値)を付与して割り振ってくれます。

データの更新系(select以外の)処理(execute!)

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!)

行の追加です。列名のリストにつづいて追加する行をリストの形で複数指定できます。 １回のオペレーションで複数行を登録できます。

(insert! db :foo '(:bar :baz) '(1 10) '(2 20))
;; "insert into foo (bar, baz) values (?, ?)"
;; を、パラメータ (1, 10) 、 (2, 20) で実行する

データの更新(update!)

行の更新です。更新するキーと値の組み合わせを map で指定し、さらに検索 条件を vector で指定します。更新された行数を含むリストを返します。

(update! db :foo {:bar 1 :baz 2} ["bar = ?" 111])
;; "update foo set bar = ?, baz = ? where bar = ?" を、
;; パラメータ 1, 2, 111 で実行する
;;=> (1)

データの削除(delete!)

行の削除です。テーブル名をキーワードで指定できたりしますが、やっている ことは SQL 文とパラメータを組み立てて execute! をしているだけです。 削除した行数を含むリスト返します。

(delete! db :foo ["baz = ?" 111])
;; "delete from foo where baz = ?" を、パラメータ 111 で実行する
;;=> (1)

create-table-ddl

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-ddl

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"

REPLから使いたい

Clojure の REPL から随時関数を定義していきながら開発をすると思いますが、 いちいち (db-transaction [db dbspec] ... ) で動かしたい関数を囲うのは 面倒です。REPL から呼び出す場合には、dbspec は定義しておいた上で、

(def db (add-connection db-spec (get-connection db-spec)))

とすると、query/execute!/insert!/update!/delete! に渡すパラメータ db を作ることができます。

JDBC あるある

CHAR 項目の検索に対する後ろ空白埋め問題

とある仕事(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"})

（ちょっとしたツールだったので、いちいちめんどくさかったけどマクロを定 義するほどのこともないという...）

その他気付いた点

ストアドプロシジャを呼ぶのに Output Parameter 指定がない

ストアドプロシジャに処理をさせてその値を受け取る、といったときに、 outパラメータの指定ができるようになっていません。これはちょっと痛い 感じがします(そこは自作すればよいのですが...)。

Database の型と Java の型の相互変換

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)です。

よろしくお願いします。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment