動的なSQLクエリの生成でちょっとトリッキーな構築方法

背景

新人さん（PG歴3weekくらい？）のコードレビューしていて、やや面白いSQLのクエリ生成をしていたのでネタ共有を。いいかげん何かしらのORマッパーとかDomaとか使わせろって話なんですが、まぁそこは置いておいて。

前提

やりたいことは検索画面にて複数の入力ボックスがあり、ユーザが入力した項目をSQL検索条件に追加するというよくあるもの。

Active Record的なORマッパーを使っていなければ、Domaのような2-way-SQLのライブラリも使っていない。

気がきいていればSQLを外部ファイルに切り出すかもしれませんが、初心者の普通の発想としては動的に文字列生成してSQLを構築しようとする事がほとんどだと思います。

したがって、通常はコードの中でSQLクエリを文字列結合で生成することになるはずです。

今回のスキーマ：
http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05780-02/scripts.htm#Cihgfecd

例：社員テーブルから「職種にセールスが含まれており」、「給料が600万円以上」の検索したい場合のクエリ。
Like句でエスケープ設定がどうとかは省略させてください。

生成したいSQL

SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
AND jb.JOB_TITLE LIKE '%' || ? || '%';     -- ユーザの指定があれば条件に追加したい
AND jb.SALARY >= ?                         -- ユーザの指定があれば条件に追加したい
;

通常は値の有無から文字列結合の有無を分岐させることが多いかと。
StringBuilder使うとかは置いておいて、まぁ分からなくも無いかと。

String sql = ~ ; // 省略。WHERE句1行目までのSQLの想定。

if (jobTitle != null && jobTitle.length >= 0) {
  sql += "AND jb.JOB_TITLE LIKE '%' || ? || '%'"
}

if (minSalary != null && minSalaly.length >= 0) {
  sql += "AND jb.SALARY >= ?"
}

ところが、ある新人さんはこうやっていた。

jobTitleComment = "";
minSalaryComment = "";
if (jobTitle != null && jobTitle.length >= 0) {
 jobTitleComment = "--";
}

if (minSalary != null && minSalaly.length >= 0) {
 minSalaryComment = "--";
}

String sql = "SELECT"
+ " em.EMPLOYEE_ID"
+ ", em.FIRST_NAME"
+ ", em.LAST_NAME"
+ "FROM"
+ "  EMPLOYEES em"
+ ", INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)"
+ ", INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)"
+ "WHERE 1 = 1"
+ "AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE"
+ jobTitleComment + "AND jb.JOB_TITLE LIKE '%' || ? || '%'"  // ポイント
+ minSalaryComment + "AND jb.SALARY >= ?"                     // ポイント
;

パット見て意図が分からず数秒固まっちゃいましたが、よく見ると分からなくもないと。
SQLの検索条件を利用するかどうかをコメントを示す「--」でスイッチしようというわけ。

例えば、条件がどちらも未設定の場合の実行後のSQL生成例はこうなる。

検索条件が未設定の場合

SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
-- AND jb.JOB_TITLE LIKE '%' || ? || '%'  /* ★どちらもコメントアウトされます */
-- AND jb.SALARY >= ?　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　/* ★どちらもコメントアウトされます */
;

検索条件が空っぽなのでWhere句の絞込条件がコメントアウトされ無効になります。

続いて、ジョブ名だけ設定された場合の生成例です。

ジョブ名だけ設定された場合

SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
AND jb.JOB_TITLE LIKE '%' || 'セールス' || '%'  /* こちらは有効 */
-- AND jb.SALARY >= '??'                      /* こちらはコメントアウトのまま */
;

検索条件が存在した場合、Where句の該当する絞込条件が有効になります。
意外と見やすくて良いなと思いました。

まとめ

• こういう出力結果な2-way-SQLがあっても面白いかもですね。
• でもDoma使えよって感じですよね