2WaySQL

検索条件が任意入力の一覧画面を作ると、条件が指定されたかどうかでSQLの WHERE 句が変わります。 Javaの文字列連結でSQLを組み立てると、出来上がったSQLをそのままDBツールに貼って動作を確かめられなくなります。

im_mirage が採用する 2WaySQL は、この問題を「SQLのコメント」で解決します。 条件分岐やバインド変数の指定をコメントとして書くため、ファイルはSQLとして妥当なまま保たれ、DBツールでそのまま実行できます。 実行時には im_mirage がコメントを解釈し、条件に応じたSQLへ組み替えます。

構文

構文	用途
/*IF 条件*/ ... /*END*/	条件分岐
/*BEGIN*/ ... /*END*/	オプショナルブロック
/*パラメータ名*/ダミー値	バインド変数
IN /*パラメータ名*/('dummy')	リストの要素をIN句に展開
/*FOR 要素 in リスト*/ ... /*END*/	ループ
/*$パラメータ名*/ダミー値	値の直接埋め込み（バインドしない）

バインド変数とダミー値

バインド変数は、パラメータ名のコメントと、その直後のダミー値で表します。

status = /*status*/'dummy'

実行時、'dummy' はプレースホルダに置き換えられ、パラメータの値がバインドされます。 ダミー値はDBツールで直接実行したときにだけ意味を持ちます。 値を文字列連結しているわけではないため、SQLインジェクションの経路にはなりません。

条件分岐とオプショナルブロック

/*IF*/ は条件が成り立つときだけ内側を出力します。 /*BEGIN*/ は、内側の /*IF*/ がすべて成り立たなかった場合に、ブロックごと出力を取りやめます。

SELECT
  order_id,
  customer_name,
  amount,
  status
FROM
  foo_order
/*BEGIN*/
WHERE
  /*IF status != null*/
  status = /*status*/'dummy'
  /*END*/
/*END*/
ORDER BY
  order_id

status が null なら WHERE ごと消え、全件検索になります。 /*BEGIN*/ で囲っておかないと、条件が一つも成立しなかったときに WHERE だけが残り、構文エラーになります。

条件を増やすときは、二つめ以降の /*IF*/ の先頭に AND を書きます。 /*BEGIN*/ は、ブロックの先頭に残った AND や OR を取り除いてくれます。

/*BEGIN*/
WHERE
  /*IF status != null*/
  status = /*status*/'dummy'
  /*END*/
  /*IF customerName != null*/
  AND customer_name = /*customerName*/'dummy'
  /*END*/
/*END*/

/*BEGIN*/ の中で /*IF*/ を入れ子にしない

パーサ自体は /*IF*/ の入れ子に対応していますが、/*BEGIN*/ の中で入れ子にすると、内側の /*IF*/ の先頭にある AND や OR が取り除かれることがあります。 外側の /*IF*/ がそのブロックで最初に成立した条件だったときに起こり、WHERE a = ? b = ? のような不正な SQL になります。 先に並んだ条件が成立していれば正しく出力されるため、パラメータの組み合わせによって通ったり落ちたりします。

複数の条件は入れ子にせず兄弟として並べ、条件どうしの依存は条件式で表します。

/*IF status != null*/
status = /*status*/'dummy'
/*END*/
/*IF status != null && customerName != null*/
AND customer_name = /*customerName*/'dummy'
/*END*/

内側のブロックが AND や OR やカンマで始まらない入れ子であれば、この問題は起きません。

IN句

リストの要素を IN句に展開するときは、パラメータ名のコメントの直後に ('dummy') を置きます。 実行時に、要素の数だけ並んだ (?, ?, ?) に展開されます。

SELECT
  order_id,
  customer_name
FROM
  foo_order
/*BEGIN*/
WHERE
  /*IF orderIds != null && orderIds.size() > 0*/
  AND order_id IN /*orderIds*/('dummy')
  /*END*/
/*END*/

リストが null のときも空のときも、バインドの部分は出力されません。 ガードがなければ IN だけが残って SQL が壊れるため、null と空リストの両方を /*IF*/ で止めます。 /*IF orderIds != null*/ だけでは、空リストを通してしまいます。 条件式の && は短絡評価されるため、orderIds が null でも size() は評価されません。

呼び出し側は、リストをプロパティに持つ JavaBean か Map を渡します。

public List<OrderEntity> findByOrderIds(final List<String> orderIds) {
    final Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("orderIds", orderIds);
    return super.sqlManager.getResultList(OrderEntity.class, SQL_PATH.concat("find-by-order-ids.sql"), parameters);
}

空リストで全件が返る

上の例のように IN句のガードが /*BEGIN*/ の中の唯一の条件だと、リストが null や空のときに WHERE ごと消えます。 SQL エラーにはなりませんが、「対象なし」ではなく、絞り込みのない全件が返ります。

空リストなら結果も空にしたい場合は、SQL 側のガードに頼らず、呼び出し元のリポジトリやサービスで空リストと null を判定し、SQL を実行せずに空のリストを返します。 アクセスできる ID の一覧を IN句に渡して権限で絞り込む用途では、空リストが全件の公開に化けるため、この判定は欠かせません。

ループ

/*FOR*/ は、リストの要素の数だけ内側を繰り返します。 要素とリストの区切りには、前後を半角スペースで挟んだ in または IN を書きます。 区切りが一致しないと TwoWaySQLException: For expression is invalid. になります。

SELECT
  order_id,
  customer_name
FROM
  foo_order
/*BEGIN*/
WHERE
/*FOR keyword in keywords*/
OR customer_name LIKE /*keyword*/'%dummy%' ESCAPE '\'
/*END*/
/*END*/

ボディの先頭を AND や OR やカンマで始める場合は、/*BEGIN*/ で囲みます。 先頭の AND や OR が取り除かれるのは、囲んだブロックの中身がまだ空のときだけです。 /*BEGIN*/ がないと1件目の OR が残り、WHERE OR ... という壊れた SQL になります。

ブロックの中で参照できるのは、ループ変数（上の例では keyword）に束ねられた要素一つだけです。 IN句の組み立てには /*FOR*/ ではなく、前節の IN /*パラメータ名*/('dummy') を使います。

JSSP の 2WaySQL との違い

/*FOR*/ を使えるのは im_mirage と IM-LogicDesigner です。 スクリプト開発モデル（JSSP）の2WaySQLはループ構文に対応していません。

/*IF*/ の条件式は、im_mirage では OGNL として、JSSP では JavaScript として評価されます。 リストの要素数は、im_mirage では orderIds.size()、JSSP では orderIds.length と書きます。

/*IF*/ と /*BEGIN*/ とバインド変数の書き方は共通ですが、これらの違いに加えてパラメータの渡し方や呼び出しAPIも異なるため、JSSP側のSQLファイルをそのままJava側へ流用することはできません。

値の直接埋め込み

/*$パラメータ名*/ は、値をバインドせず、SQL の本文にそのまま連結します。 ソート順のように、バインド変数を使えない箇所のための構文です。

ORDER BY /*$sortColumn*/order_id /*$sortOrder*/ASC

パーサが拒否するのは、値に ; が含まれる場合だけです。 OR 1=1 や UNION SELECT ... は、そのまま SQL に入ります。 使うのは動的なテーブル名、カラム名、ソート順に限り、値は必ずホワイトリストで検証します。 値をパラメータとして渡すなら、/*パラメータ名*/'dummy' を使います。

プロパティをたどるドットは1段までです。 /*$a.b.c*/ は a.b までを評価し、.c 以降は無視して a.b の文字列表現を埋め込みます。 このとき例外にはなりません。

UPDATE 文の SET 句

更新するカラムを条件によって変えるときは、/*BEGIN*/ にカンマの除去を任せません。 カンマを /*IF*/ と同じ行に置くか別の行に置くかで除去されたりされなかったりするうえ、条件がすべて成り立たないと SET ごと消えてしまうためです。

SET 句の先頭には主キーの自己代入を常に置き、残りの項目は先頭にカンマを付けて並べます。

UPDATE foo_order
SET
  order_id = /*orderId*/'dummy'
  , record_user_cd = /*recordUserCd*/'dummy'
  , record_date = /*recordDate*/'2000-01-01 00:00:00'
  /*IF status != null*/
  , status = /*status*/'dummy'
  /*END*/
  /*IF customerName != null*/
  , customer_name = /*customerName*/'dummy'
  /*END*/
WHERE
  order_id = /*orderId*/'dummy'

sqlManager.executeUpdate で実行する UPDATE 文では、監査項目が自動で設定されません。 上の例のように、更新者と更新日時も SET 句に含めます。

SQLファイルにコメントを書かない

SQLファイルには、-- の行コメントも、2WaySQL の構文ではないブロックコメントも書きません。 2WaySQLのパーサは -- 行コメントの中身を区別せず、ファイル全体を走査して構文を探します。 説明のつもりで書いた /*IF*/ などの構文表記やバインド名、? が本物の指示として解釈され、UnsupportedOperationException: not supported や 列インデックスは範囲外です のような、コメントとは無関係に見える実行時エラーの原因になります。

SQLの説明は、そのファイルを使う DAO メソッドの JavaDoc に書きます。

パラメータの渡し方

SQL内のプレースホルダ名は、渡したオブジェクトのプロパティ名またはキー名と一致させます。 エンティティ、任意の JavaBean、Map<String, Object> のいずれも渡せます。

/*IF*/ の条件式には、status != null のようにパラメータのプロパティを書きます。

LIKE 検索でのエスケープ

LIKE演算子に渡す値では、利用者の入力に含まれる \ や % や _ は特殊文字として働きます。 % をそのまま入力されれば全件がヒットし、_ を入力されれば任意の1文字に一致します。

ワイルドカードは画面から送らせず、サーバ側で付与します。 そのうえで、特殊文字をエスケープしてからワイルドカードを付け、SQL側に ESCAPE 句を書きます。

AND customer_name LIKE /*customerName*/'%dummy%' ESCAPE '\'

DB方言ごとのファイル

DB製品によってSQLの構文が違う場合があります。 im_mirage は、稼働中のDBに応じてファイル名の異なるSQLファイルを自動で選びます。

指定した find-by-status.sql に対し、find-by-status_<方言名>.sql がクラスパス上に存在すればそちらを使い、なければ元のファイルにフォールバックします。

DB製品	方言名（ファイル名のサフィックス）
Oracle	oracle
PostgreSQL	postgre
SQLServer	sqlserver

src/main/resources/META-INF/sql/jp/co/example/foo/infrastructure/dao/OrderDAO/
├── find-by-status.sql            … ベースファイル
├── find-by-status_oracle.sql     … Oracle 固有の構文が必要な場合のみ追加
└── find-by-status_sqlserver.sql  … SQLServer 固有の構文が必要な場合のみ追加

DAO側が指定するのはベースファイルのパスだけで済みます。 方言別ファイルは、構文差分がある場合にだけ追加します。 差分がないのに全方言分を複製すると、修正のたびに同じ変更を何か所にも入れることになります。

関連ドキュメント

• エンティティと DAO の作成：SQLファイルの配置場所と SqlManager のメソッドの使い分け
• トランザクション制御：SQL実行を囲むトランザクション境界