PreparedStatement（バインドパラメータ）にはWITH句で名称を付けよう

概要

PreparedStatement（バインドパラメータ）とWITH句を組み合わせて使用した場合は以下の利点があります。

• パラメータに名称を付ける事が出来る
• パラメータの重複をなくす事が出来る
• PL/SQLのパラメータ付きカーソルとの移植性が高い記述が出来る

簡単に解説します。

今回の例題で使用するテーブル定義は以下の通りです。

SALARY_MST

ID	SALARY_YM	SALARY	DEDUCTION
社員ID	支給年月	給与	控除

パラメータに名称を付ける事が出来る

JavaのPreparedStatementを使用してSQLを書く場合、以下のように書くかと思います。

条件：社員IDが「123」の給与が「200,000」以上の支給年月を取得する。

WITH句なし

sample1_without.sql

SELECT
  SALARY_YM
FROM
  SALARY_MST
WHERE
  ID = ?
  AND SALARY >= ?

sample1.java

PreparedStatement pstmt = conn.prepareStatement(sql); //sample1_without.sql
pstmt.setInt(1, 123);
pstmt.setInt(2, 200000);
ResultSet rs = pstmt.executeQuery(sql);

WITH句あり

sample1_with.sql

WITH
  PARAMS AS (
    SELECT
      ? AS P1_ID,
      ? AS P2_SALARY
    FROM
      DUAL
  )
SELECT
  A.SALARY_YM
FROM
  SALARY_MST A, PARAMS P
WHERE
  A.ID = P.P1_ID
  AND A.SALARY >= P.P2_SALARY

sample1.java

PreparedStatement pstmt = conn.prepareStatement(sql); //sample1_with.sql
pstmt.setInt(1, 123);
pstmt.setInt(2, 200000);
ResultSet rs = pstmt.executeQuery(sql);

WITH句ありの方では、バインドパラメータ「?」をWITH句内に纏めた事と、
そのパラメータに別名を付けている事が要点となります。

例題の条件が単純なためイマイチ利点が見えないかもしれませんが、
百行程度のSQLを実務で見かけることはありませんか？
そんな時にバインドパラメータがそこら中に散見してしまうと、
「このパラメータ何番目だっけ」という事になったり、
SQLのリファクタリングをした後にJava側の修正を忘れて、
設定するパラメータの順番が入れ替わったりバグの元となりやすいですよね。

これらの問題はWITH句を使用する事でかなり緩和する事が出来ます。
まず、パラメータが一カ所に纏まっているためJava側との整合性確認が容易です。
そして、パラメータに名称を付けられるので順番間違えは根本的に起きません。

パラメータの重複をなくす事が出来る

それでは、次の複雑な条件の場合はどうでしょう。

条件：社員IDが「123」の給与が「200,000」以上で控除が「100,000」未満又は、
給与が「200,000」未満で控除が「100,000」以上の支給年月を取得する。

WITH句なし

sample2_without.sql

SELECT
  SALARY_YM
FROM
  SALARY_MST
WHERE
  ID = ?
  AND (
    (SALARY >= ? AND DEDUCTION < ?)
    OR (SALARY < ? AND DEDUCTION >= ?)
  )

sample2.java

PreparedStatement pstmt = conn.prepareStatement(sql); //sample2_without.sql
pstmt.setInt(1, 123);
pstmt.setInt(2, 200000);
pstmt.setInt(3, 100000);
pstmt.setInt(4, 200000); //2番目のパラメータと同一
pstmt.setInt(5, 100000); //3番目のパラメータと同一
ResultSet rs = pstmt.executeQuery(sql);

上記の例のように複雑な条件となるとパラメータが重複する事があったりします。
特にテーブルを何個も結合する場合パラメータが大量になる事もしばしばです。
(実数についてはJava側でマジックナンバーを使用しなければ重複しませんが、
パラメータを設定するメソッドの実行数を減らす事は出来ません)

WITH句あり

sample2_with.sql

WITH
  PARAMS AS (
    SELECT
      ? AS P1_ID,
      ? AS P2_SALARY,
      ? AS P3_DEDUCTION
    FROM
      DUAL
  )
SELECT
  A.SALARY_YM
FROM
  SALARY_MST A, PARAMS P
WHERE
  A.ID = P.P1_ID
  AND (
    (A.SALARY >= P.P2_SALARY AND A.DEDUCTION < P.P3_DEDUCTION)
    OR (A.SALARY < P.P2_SALARY AND A.DEDUCTION >= P.P3_DEDUCTION)
  )

sample2.java

PreparedStatement pstmt = conn.prepareStatement(sql); //sample2_with.sql
pstmt.setInt(1, 123);
pstmt.setInt(2, 200000);
pstmt.setInt(3, 100000);
ResultSet rs = pstmt.executeQuery(sql);

パラメータ数（メソッドの実行数）が減っています。
同じ意味のパラメータはひとつに纏めて管理出来ている事がわかります。

Java側にとっては正直、
重複する意味のパラメータを設定した所で問題になる事は有りませんが、
SQLだけを見た時にパラメータが全て「?」となっていると、
そもそもどんな意味のパラメータがきているのかわかりません。
例題では条件に給与に関するパラメータが2回与えられていますが、
それらは必ず同じ値（意味）なのか、別々の値になる事があるのか
WITH句なしのほうでは判断出来ませんね。
WITH句ありのほうでは、パラメータが纏まっている（かつ名称付き）ので、
必ず同じ値が来る事が判断出来ます。

PL/SQLのパラメータ付きカーソルとの移植性が高い記述が出来る

PL/SQLのパラメータ付きカーソル定義は以下のようになります。

sample_storedprocedure.sql

CURSOR SALARY_MST_CUR(
  P1_ID NUMERIC,
  P2_SALARY NUMERIC,
  P3_DEDUCTION NUMERIC
) IS
  SELECT
    A.SALARY_YM
  FROM
    SALARY_MST A
  WHERE
    A.ID = P1_ID
    AND (
      (A.SALARY >= P2_SALARY AND A.DEDUCTION < P3_DEDUCTION)
      OR (A.SALARY < P2_SALARY AND A.DEDUCTION >= P3_DEDUCTION)
    )

どうですか？
SQL部分がほぼ同じ構文となっています。

PL/SQLとユーザプログラム（Java等）との相互移植性が高いと、
例えば、Oracleから別のDBMSに移行するとき、
使っていたPL/SQLを別言語で再実装しないといけないとなったときに、
クエリ部分は標準SQLで書き出し出来る利点があります。
逆に高速化のためにPL/SQLで実装しようとなった時も同様に容易になります。

まとめ

今はJPAを使用すればパラメータに名前を付ける事が出来たり、
SQLとJavaクラスとのマッピングにより順番という概念すら無くなったり、
このテクニックの必要性が薄くなりましたが、
WITH句自体はSQLの可読性を上げるためにぜひ活用して頂きたいと思います。

ちなみに、WITH句を使用すると実行計画でコストが上がる事がしばしばあります。
ただ、実測値で差が出る事はそれほど無いイメージ（個人的感想）です。

ほぼ死に体のPreparedStatementですが、
それでもまだ使っている人がいる事を期待して少しでも参考になれば幸いです。