orangain flavor

じっくりコトコト煮込んだみかん2。知らないことを知りたい。

JVM系言語のSQLマッパーについてPostgreSQLの型への対応状況を比較検証

kotlin postgres

KotlinでWebアプリケーションを作るにあたり、SQLを直接記述できるタイプのO/Rマッパー（本稿ではSQLマッパーと呼びます）を探し求めました。

SQLマッパーに求める機能

SQLマッパーに求める機能はBindとMapです。この記事ではBindとMapを次のように定義します。

  • Bind
    • SQLに埋め込んだプレースホルダー（名前付きが望ましい）に対応するパラメーター群を渡す機能。
    • 例: SELECT ... WHERE price BETWEEN :minPrice AND :maxPrice のようなSQLに、minPriceとmaxPriceプロパティを持つオブジェクトを渡す。
  • Map
    • 実行結果として得られた行をオブジェクトにマッピングする機能。
    • 例: SELECT id, name, price FROM ... のようなSQLの実行結果を、id, name, price のプロパティを持つオブジェクト（のリスト）として返す。

次のような機能は（あれば嬉しいかもしれませんが）特に求めていません。

  • 動的にSQLを組み立てる機能*1
  • snake_caseのカラムをcamelCaseのプロパティにMapする機能*2

最初はなんとなくsql2oを使っていましたが、プロパティの型によっては思い通りにBind/Mapされないことがあり、各種のSQLマッパーがどのような型に対応しているのか真面目に検証してみようと思いました。

検証対象とバージョン

上記の求める機能を持つSQLマッパーとして、次の5つのライブラリを検証しました。

対象とするデータベースはPostgreSQLです。

基本的な情報

基本的な情報は次の通りです。最新については2019-09-29時点の情報です。

名前 Apache Commons DbUtils Spring JDBC Template MyBatis JDBI sql2o
リンク ドキュメント
リポジトリ 		ドキュメント
リポジトリ 		ドキュメント
リポジトリ 		ドキュメント
リポジトリ 		ドキュメント
リポジトリ
開発者 Apache Software Foundation Pivotal MyBatisチーム JDBI Project Members Lars Aaberg氏
ライセンス Apache License 2.0 Apache License 2.0 Apache License 2.0 Apache License 2.0 MIT License
最新バージョン 1.7 5.1.10 3.5.2 3.10.1 1.6.0
最新版公開日 2017-07-02 2019-09-28 2019-07-15 2019-09-07 2018-10-20
最終コミット日 2019-08-16 2019-09-27 2019-09-28 2019-09-29 2019-05-16
SQLを書く場所 引数 引数 別ファイル or アノテーション 引数 or アノテーション 引数

検証方法

検証対象とする型のカラムを含むテーブルsql_mapper_testを作成し、そこにテストデータを1行INSERTしておきます。

create table sql_mapper_test (
    -- simple
    c_boolean boolean,
    c_integer integer,
    c_decimal decimal,
    c_double double precision,
    c_varchar varchar(100),
    c_text text,
    c_bytea bytea,
    c_uuid uuid,
    -- datetime
    c_date date,
    c_time time,
    c_timetz timetz,
    c_timestamp timestamp,
    c_timestamptz timestamptz,
    -- complex
    c_inet_ipv4 inet,
    c_inet_ipv6 inet,
    c_url text,
    c_integer_array integer[],
    c_varchar_array varchar(10)[]
);

BindとMapについて、それぞれ次のように対応可否を検証します。

  • Bind
    • SELECT COUNT(*) FROM sql_mapper_test WHERE カラム名 = :value のようなSQLでテストデータと同じ値をBindして、1が返ってくれば対応していると判断。
  • Map
    • SELECT カラム名 FROM sql_mapper_test というSQLで、テストデータと同じ値が返ってくれば対応していると判断。

検証に使用したソースコードは次のリポジトリにあります。

https://github.com/orangain/compare-sql-mappers

検証結果

結果は次のスプレッドシートに記載しています。 それぞれのセルは、1列目のカラムを2列目の型でBind/Mapした結果を表します。

  • Success（緑）: 正しくBind/Mapできた
  • Wrong Value（黄）: 期待とは異なる値がBind/Mapされた（配列が文字列としてBindされてしまう、期待とは異なる型にMapされてしまう、型は同じだけど値が異なるなど）
  • 〜Exception（赤）: 発生した例外の名前

https://docs.google.com/spreadsheets/d/1Tf3uB5xFIZnP1ieSu9JQdKaGDY_XfJyGJYleAXI3328/edit?usp=sharing

検証結果のサムネイル

解説

まず最初に、timetz⇔OffsetTime、url⇔URLの変換は試した全ライブラリでできませんでした。

Commons DbUtilsとSpring JDBC Templateは同じような結果となりました。 基本的にはJDBCドライバー任せで、Bind時はJDBCドライバーが対応していればBindできます。 Map時はJDBCドライバーが返す値をそのままキャストして返すので、期待している型とは異なる型が返ってくる場合があります。 例えば、日時に関する型はjava.sql.Dateなどで返ってくるので、LocalDateなどのDate and Time APIの型で受け取ることはできません。 また、integer型のカラムをBigDecimalで受け取りたいと思っても、返ってくるのはIntegerです。

Spring JDBC Templateの追加の特徴として、List<T>をBindした時に、個数分のカンマ区切りのプレースホルダーに展開してくれるので、 IN (:list) のような記述が可能です。

MyBatisはMap時に期待する型に変換してくれるので、インターフェイスの戻り値の型に応じてDate and Time APIの型で受け取ることもできます。 PostgreSQLの配列型に関しても、 Integer[] や String[] などの配列型であれば受け取れました。 ただし、Integer[]をパラメーターとしてBindすることはできなかったので、注意が必要です。 MyBatisの場合、（本稿の狙いとはズレますが）動的なSQL組み立て機能が強みなので、<foreach>を使えばINなどのパラメーターを展開できます。

JDBIはPostgresプラグインを有効にするとほぼ全ての型をBind/Mapでき、一番良い結果となりました。 他の全ライブラリが対応しているjava.sql.Dateとjava.sql.TimeをMapできないのは意外でしたが、LocalDateやLocalTimeを使えば問題ありません。 普通のプレースホルダーは :name のような形式ですが、<ids>という形式のプレースホルダーを使ってbindList()でList<T>をBindすると、個数分のカンマ区切りのプレースホルダーに展開してくれるので、IN (<list>) のような記述が可能です。

Sql2oはMap時に期待する型に変換してくれますが、Date and Time APIには対応していません。 細かいですが、integerをBigDecimalで受け取る際は、一度doubleに変換されて、桁数がずれてしまいました。 List<T>や配列をBindした時に、個数分のカンマ区切りのプレースホルダーに展開してくれるので、 IN (:list) のような記述が可能です。

大まかにまとめると次のようになります。

Apache Commons DbUtils Spring JDBC Template MyBatis JDBI sql2o
基本的な型への対応 ○ ○ ○ ○ ○
Date and Time APIへの対応 △Mapのみ △Mapのみ ○ ○ △Mapのみ
ArrayやListへの対応 × × △数値の配列はダメ ○ ×
Bind時のコレクションの展開 × △配列はダメ △ <foreach> で対応 △配列はダメ*3 ○

結論

比較したSQLマッパーのPostgreSQLの型への対応度合いは、次の順で良い結果となりました。

  1. JDBI
  2. MyBatis
  3. sql2o
  4. Spring JDBC Template
  5. Commons DbUtils

JDBIは、ドキュメントに対応している型が明確に記述されていたのも好感が持てました。試しに使ってみようと思います。

なお、各種ライブラリの検証結果で対応していない型についても、Bind時にSQLの書き方を工夫したり、コンバーターを自作したりすれば対応できるので、ライブラリの評価はこれだけで決まるものではありません。 むしろ、対応している型を把握した上でライブラリを使うことが大切だと思うので、その助けになれば幸いです。 また、この結果はJDBCドライバーの実装に大きく依存するので、他のDBやJDBCドライバーを使う場合は、この結果をそのまま使えないことに注意してください。

なるべく注意を払いましたが、すべてのライブラリにおいてドキュメントやソースコードを完全に読んだわけではないので、検証方法がおかしいところなどありましたら、ぜひお知らせください。

参考文献

*1:とは言いながら、INのパラメーターを展開する機能はあると嬉しいので、追加で検証してます。

*2:チームメンバーに聞いたら、気持ち悪いからasを書きたいという声が多数でした。

*3:正確には配列も bindList("name", ...array) とすれば展開できるが、 List<T> の場合と同じ bindList("name", array) という記法を使うと動作しないので、他のライブラリとの公平性を考慮してダメ扱いとした。