S2JDBCで動的SQLを書いてた人がMybatisを使ってみたお話し

この記事はリクルートライフスタイル Advent Calendar 2015 - Qiita の25日目の記事です。

メリークリスマス

ホットペッパービューティーで開発を担当している高工勇樹です。

自分もAdvent Calendarに参加できて光栄です！
しかし、今更ながらオオトリのプレッシャーを(自分で選んだのに)感じつつ執筆しております

はじめに

まずS2JDBCとMybatisですが、どちらも動的SQLをSQLライクに書ける機能を有したOR Mapperです。

今までS2JDBCをメインに使っていたのですが、それに変わる
動的SQLを作成できる ＆ 外部ファイル化ができる
OR Mapperを求めてMybatisをフィジビリする機会を得ました。

この記事ではその際に感じた事を記載しています。
※主に動的SQLについての記載が多いです。

MybatisとS2JDBCを比較してみた

Mybatisのメリット

率直な感想としてはS2JDBCに比べて機能が豊富だと思いました。
特に便利だと思ったのは以下の機能

resultMap要素による自由度の高いマッピング

Mybatisでは、SQlとマッピング情報を紐付けてXMLファイルに記載できます。
これをMapper XMLと言います。

マッピング情報はこのXML内にresultMapという要素で定義できます。
このresultMap要素がとても強力で便利だと感じました。

• 単純な(id と resultによる)マッピングはもちろん簡単にできる
• ネストされたオブジェクトに値をマッピングできる
• 複数のselect結果を一つの結果にマッピングするができる
• typeHandlerを自作することでもっともっとマッピングが自由自在

という点がそう感じた点です。
※具体例が乗っているので、良かったら公式サイトも覗いて見てください。

動的SQLでループが書ける

個人的に一番嬉しい機能はこれ！ちょうど欲しかった！
S2JDBCではできなかったループが可能なのです！

このループ機能を利用できれば動的SQLでできる事が増えると思います。
ちょっとした例を書いていきますね。

下記のようなPERSONテーブルがあったとします。

	物理名	型	論理名	補足
PK	MY_NUMBER	CHAR(10)	マイナンバー
	NAME	VARCHAR2(20)	名前	例:Tanaka Taro
	AGE	NUMBER(3)	年齢
	GENDER	CHAR(1)	性別	M:男性 F:女性

このPERSONテーブルに登録された人物レコードに対して
年齢範囲(FROM,TO)のリストを指定して、各年齢範囲に該当する人数を集計するSQLを書いてみます。

年齢範囲毎の人数を集計するSQL

SELECT
  PAR.AGE_RANGE, -- 指定された年齢範囲 or OTHER
  COUNT(*) AS COUNT, -- 人数
  SUM(CASE WHEN PAR.GENDER = 'M' THEN 1 ELSE 0 END) AS MASCULINE_COUNT, -- 男性の人数
  SUM(CASE WHEN PAR.GENDER = 'F' THEN 1 ELSE 0 END) AS FEMININE_COUNT -- 女性の人数
FROM (
  SELECT
    P.GENDER,
    CASE
    <!-- ここで1件以上指定された年齢範囲のリストをループしてCASE文を作成するよー -->
    <!-- ※CASE文なので範囲が被っちゃだめだよー -->
    <foreach item="ageRange" index="index" collection="ageRangeList" open="" close="">
      WHEN P.AGE BETWEEN #{ageRange.from} AND #{ageRange.to} THEN CONCAT(#{ageRange.from} , '-' , #{ageRange.to})
    </foreach>
      ELSE 'OTHER' -- 範囲外はOTHERになる
    END AGE_RANGE
  FROM PERSON P
) PAR
GROUP BY PAR.AGE_RANGE

foreachを使うことで、年齢範囲のリストは1件以上であれば動的に
件数が変更できるように書くことができました。
このようにループが利用できることで書ける動的SQLの幅が広がると感じます。

あとはIN句で指定した要素が1000件を超えても、エラーにならないように対策したりもできますね。

IN句に1001件以上要素が指定されてもエラーにならないSQL

SELECT
  MY_NUMBER,
  NAME,
  AGE,
  GENDER
FROM
  PERSON
WHERE
  MY_NUMBER IN
  <foreach item="myNumber" index="index" collection="myNumberList" open="(" close=")">
    <if test="index%1000 == 0 and index!=0">) OR MY_NUMBER IN (</if>
    <if test="index%1000 != 0">,</if>
    #{myNumber}
  </foreach>
ORDER BY MY_NUMBER ASC

動的SQLでbind要素によるOGNL 式の結果取得ができる

単純なgetterによる値の取得だけでなく、メソッドを呼び出せる！
有りそうで無かった機能です。

例

<select id="selectPersonsLikeLastName" resultType="Person">
  <bind name="lastName" value="param.extractLastName() + ' %'" />
  SELECT * FROM PERSON WHERE NAME LIKE #{lastName}  
</select>

Enumなどの要素を呼び出せる

Enumに切り出した定数などがSQLで呼び出せます。
定数値が変わった際にSQLだけ反映がもれた。。とかが無くなるので良いかと思います。

こんな感じで呼び出せます

'${@jp.hoge.hoge.enums.BloodType@typeO}'

Mybatisのデメリット

ここまで、Mybatisのメリットを書いてきましたがS2JDBCに比べて感じたデメリットもあります。

2WaySQLのお手軽感が無くなる

S2JDBCの最も素晴らしいことは2WaySQLでSQLを書けることだと思います。
SQLをぱぱっと発行できなくなるのは最も痛いデメリットだと感じます。

とりあえず、簡単にできる対策としてはSQLをログ出力するようにして実行して出力させるとかかな。。
log4jdbcと組み合わせると良いと思います。

機能フル活用で書くと発行されるSQLの原型が無くなる

単純なSQLであれば何の問題も無いです。

しかし複雑な処理や集計を行うものを機能をフル活用して書くとカオスになりがち。。。
SQLライクを良しとしてMybatisを選んだのに、SQLの原型が無くなるのは避けたいところです。

バグが混入する危険性が増えるかもしれません。
また、もし問題が発生した際には切り分けが大変になると思います。

SQL自身に問題があるのかな?
Mybatisに適用した際に問題が生じたのかな?

問題の発見に時間がかかるかもしれません。
そうならないように、あえてSQLの原型が残るように書くのもありだと思います。

多機能故に変なハマリ方をする。。。かも

例えば、メリット「動的SQLでループが書ける」で例に上げた「年齢範囲毎の集計を行うSQL」を少し変えてみましょう。
bind要素を利用してみます。

パターン1

SELECT
  PAR.AGE_RANGE, -- 指定された年齢範囲 or OTHER
  COUNT(*) AS COUNT, -- 人数
  SUM(CASE WHEN PAR.GENDER = 'M' THEN 1 ELSE 0 END) AS MASCULINE_COUNT, -- 男性の人数
  SUM(CASE WHEN PAR.GENDER = 'F' THEN 1 ELSE 0 END) AS FEMININE_COUNT -- 女性の人数
FROM (
  SELECT
    P.GENDER,
    CASE
    <!-- ここで1件以上指定された年齢範囲のリストをループしてCASE文を作成するよー -->
    <!-- ※CASE文なので範囲が被っちゃだめだよー -->
    <foreach item="ageRange" index="index" collection="ageRangeList" open="" close="">
      <bind name="from" value="ageRange.getFrom()" />
      <bind name="to" value="ageRange.getTo()" />
      WHEN P.AGE BETWEEN #{from} AND #{to} THEN CONCAT(#{from} , '-' , #{to})
    </foreach>
      ELSE 'OTHER' -- 範囲外はOTHERになる
    END AGE_RANGE
  FROM PERSON P
) PAR
GROUP BY PAR.AGE_RANGE

パターン2

SELECT
  PAR.AGE_RANGE, -- 指定された年齢範囲 or OTHER
  COUNT(*) AS COUNT, -- 人数
  SUM(CASE WHEN PAR.GENDER = 'M' THEN 1 ELSE 0 END) AS MASCULINE_COUNT, -- 男性の人数
  SUM(CASE WHEN PAR.GENDER = 'F' THEN 1 ELSE 0 END) AS FEMININE_COUNT -- 女性の人数
FROM (
  SELECT
    P.GENDER,
    CASE
    <!-- ここで1件以上指定された年齢範囲のリストをループしてCASE文を作成するよー -->
    <!-- ※CASE文なので範囲が被っちゃだめだよー -->
    <foreach item="ageRange" index="index" collection="ageRangeList" open="" close="">
      <bind name="from" value="ageRange.getFrom()" />
      <bind name="to" value="ageRange.getTo()" />
      WHEN P.AGE BETWEEN ${from} AND ${to} THEN CONCAT(${from} , '-' , ${to})
    </foreach>
      ELSE 'OTHER' -- 範囲外はOTHERになる
    END AGE_RANGE
  FROM PERSON P
) PAR
GROUP BY PAR.AGE_RANGE

注目すべきは箇所は

パターン1

    <foreach item="ageRange" index="index" collection="ageRangeList" open="" close="">
      <bind name="from" value="ageRange.getFrom()" />
      <bind name="to" value="ageRange.getTo()" />
      WHEN P.AGE BETWEEN #{from} AND #{to} THEN CONCAT(#{from} , '-' , #{to})
    </foreach>

と

パターン2

    <foreach item="ageRange" index="index" collection="ageRangeList" open="" close="">
      <bind name="from" value="ageRange.getFrom()" />
      <bind name="to" value="ageRange.getTo()" />
      WHEN P.AGE BETWEEN ${from} AND ${to} THEN CONCAT(${from} , '-' , ${to})
    </foreach>

bind要素で定義したfromとtoを
パターン1ではバインドパラメータ(#{・・・})として
パターン2では文字列代入(\${・・・})として値を設定しています。

文字列代入はSQLインジェクションに注意が必要なので
使う機会は少ないかもしれませんがS2JDBCにもある機能です。

もし、ループ対象のageRangeListに下記のような要素が設定されていた場合。。
1要素目 : from=0 to=9
2要素目 : from=10 to=19
3要素目 : from=20 to=29

パターン1とパターン2、それぞれどのような値が設定され、SQLが発行されるか分かりますか？

答えは。。
↓
↓
↓
↓
↓
↓
↓
↓
パターン1はこんな感じです。

パターン1

SELECT
  PAR.AGE_RANGE, -- 指定された年齢範囲 or OTHER
  COUNT(*) AS COUNT, -- 人数
  SUM(CASE WHEN PAR.GENDER = 'M' THEN 1 ELSE 0 END) AS MASCULINE_COUNT, -- 男性の人数
  SUM(CASE WHEN PAR.GENDER = 'F' THEN 1 ELSE 0 END) AS FEMININE_COUNT -- 女性の人数
FROM ( 
  SELECT
    P.GENDER,
    CASE
      WHEN P.AGE BETWEEN ? AND ? THEN CONCAT(? , '-' , ?)
      WHEN P.AGE BETWEEN ? AND ? THEN CONCAT(? , '-' , ?)
      WHEN P.AGE BETWEEN ? AND ? THEN CONCAT(? , '-' , ?)
      ELSE 'OTHER' -- 範囲外はOTHERになる
    END AGE_RANGE
  FROM PERSON P 
) PAR GROUP BY PAR.AGE_RANGE

↓さらにバインドパラメータを実際の値に置き換えると。。。

パターン1(実際の値に置き換え)

SELECT
  PAR.AGE_RANGE, -- 指定された年齢範囲 or OTHER
  COUNT(*) AS COUNT, -- 人数
  SUM(CASE WHEN PAR.GENDER = 'M' THEN 1 ELSE 0 END) AS MASCULINE_COUNT, -- 男性の人数
  SUM(CASE WHEN PAR.GENDER = 'F' THEN 1 ELSE 0 END) AS FEMININE_COUNT -- 女性の人数
FROM ( 
  SELECT
    P.GENDER,
    CASE
      WHEN P.AGE BETWEEN 20 AND 29 THEN CONCAT(20 , '-' , 29)
      WHEN P.AGE BETWEEN 20 AND 29 THEN CONCAT(20 , '-' , 29)
      WHEN P.AGE BETWEEN 20 AND 29 THEN CONCAT(20 , '-' , 29)
      ELSE 'OTHER' -- 範囲外はOTHERになる
    END AGE_RANGE
  FROM PERSON P 
) PAR GROUP BY PAR.AGE_RANGE

あれれ？
20と29の組み合わせで全て置き換わってしまいました。
これでは期待した動きにはなりませんね。

次はパターン2です。

パターン2

SELECT
  PAR.AGE_RANGE, -- 指定された年齢範囲 or OTHER
  COUNT(*) AS COUNT, -- 人数
  SUM(CASE WHEN PAR.GENDER = 'M' THEN 1 ELSE 0 END) AS MASCULINE_COUNT, -- 男性の人数
  SUM(CASE WHEN PAR.GENDER = 'F' THEN 1 ELSE 0 END) AS FEMININE_COUNT -- 女性の人数
FROM (
  SELECT
    P.GENDER,
    CASE
      WHEN P.AGE BETWEEN 0 AND 9 THEN CONCAT(0 , '-' , 9)
      WHEN P.AGE BETWEEN 10 AND 19 THEN CONCAT(10 , '-' , 19)
      WHEN P.AGE BETWEEN 20 AND 29 THEN CONCAT(20 , '-' , 29)
      ELSE 'OTHER' -- 範囲外はOTHERになる
    END AGE_RANGE
  FROM PERSON P
) PAR
GROUP BY PAR.AGE_RANGE

こちらはSQLテキストとした文字列代入されました。
想定通りの値が設定されています。
SQLインジェクションの危険性を取り除けばこちらの書き方でも問題なさそうです。

パターン1もパターン2もループによってbind要素の値は置き換えられます。
違うのはバインドパラメータとして参照するか、ループしつつ参照しSQLテキストに文字列代入するかです。

参照するタイミングをよく考えれば納得できる挙動だと思います。
しかし、初めは予期せぬ挙動に戸惑い、ハマるかもしれません。私はハマりました(笑)

フォーマットすると悲惨

よく言われているようですが
SQLをXMLに書くのでIDEの標準フォーマットを使用するとSQL部分は悲惨なことになります。

最後に

ここまでMybatisについて感じたメリット・デメリットを書いてきました。

個人的にはSQLよりプログラムを書くのが得意な人は
Mybatisに慣れるのが早そうで、良いツールに成り得ると思います。

慣れるまではどうしても「SQLを作る」→「Mybatisに適用」という段階を踏むので逆に工数は掛かりそうです。
ですが、慣れて「SQLを考えながらMybatisに適用」となればメリットを活かせるようになります。

痛いところは
「2WaySQLのお手軽感が無くなる」
「機能フル活用で書くと発行されるSQLの原型が無くなる」
という点を考慮するとS2JDBCに比べ保守はしにくくなると思います。

やはり、あえて「SQLの原型が残るように書く！」というのは私は重要だと思います。