外だしSQL(OutsideSql)について

外だしSQLとはそもそもどういったものか?わりと事務的なページです。

外だしSQL(OutsideSql)とは?

外部テキストファイルに記載されたSQL文をアプリケーションから実行 する機能のことを言います。

二つの特徴

特徴はこの二つ。

2Way-SQL
アプリからでもツールからでも動作するSQLとして管理できるので、すぐに実行して確認できる
SQL変更に強い
必要なクラスは自動生成され、SQL変更の影響もコンパイルエラーとして検知できる

自動生成ドリブン

一般に、外だしにされたSQLを呼び出す際に必要な手続きは三つ。これらを自動生成することでタイプセーフな実装ができるようにし、最大限呼び出し手続きでのミスが発生しないようにしています。

どのSQLを実行するか? (BehaviorQueryPath)
SQLファイルを指定するパス定義を自動生成 (タイプセーフ呼び出し)
SQLにどんな値を渡すか? (ParameterBean)
SQLに渡すパラメータを格納するクラスを自動生成 (タイプセーフ設定)
検索結果をどう受け取るか? (CustomizeEntity)
SQL(select文)の結果セットに対応するクラスを自動生成 (タイプセーフ受け取り)

2Way-SQL

外だしSQLはアプリケーションでもSQLツールでもどちらでも実行可能です。

昔はよく見かけた "画面から実行しないとそのSQLが本当に文法的に正しいかわからない" という状態は基本的に発生しません。アプリを起動させる前にSQLツールなどで実行して気軽に確認ができます。

それをどうやって実現しているのか?ポイントが二つ。

バインド指定しつつテスト値
バインド変数指定でテスト値を含めることができる
動的制御をSQLコメントで
分岐やループなどのSQLの動的制御をSQLコメント形式で書ける

この "アプリケーション・SQLツールの両方で実行可能なSQL" のことを 2Way-SQL と呼びます。

e.g. 2Way-SQLでの実装 (アプリケーション・SQLツールの両方で実行可能) @OutsideSql
/*IF pmb.isPaging()*/
select member.MEMBER_ID
     , member.MEMBER_NAME
     , (select sum(purchase.PURCHASE_PRICE)
          from PURCHASE purchase
         where purchase.MEMBER_ID = member.MEMBER_ID
           and purchase.PAYMENT_COMPLETE_FLG = 0
       ) as UNPAID_PRICE_SUMMARY
     , memberStatus.MEMBER_STATUS_NAME
-- ELSE select count(*)
/*END*/
  from MEMBER member
    /*IF pmb.isPaging()*/
    left outer join MEMBER_STATUS memberStatus
      on member.MEMBER_STATUS_CODE = memberStatus.MEMBER_STATUS_CODE
    /*END*/
 /*BEGIN*/
 where
   /*IF pmb.memberId != null*/member.MEMBER_ID = /*pmb.memberId*/3/*END*/
   /*IF pmb.memberStatusCode != null*/
   and member.MEMBER_STATUS_CODE = /*pmb.memberStatusCode*/'FML'
   /*END*/
   /*IF pmb.unpaidMemberOnly*/
   and exists (select 'yes'
                 from PURCHASE purchase
                where purchase.MEMBER_ID = member.MEMBER_ID
                  and purchase.PAYMENT_COMPLETE_FLG = 0
       )
   /*END*/
 /*END*/
 /*IF pmb.isPaging()*/
 order by UNPAID_PRICE_SUMMARY desc, member.MEMBER_ID asc
 /*END*/

この 2Way-SQL を利用することで、さらなる恩恵を得ることができます。アプリ用のSQLをSQLツールで実行できるということは、DBFluteというツールでも実行できます。 DBFluteでは、2Way-SQL の特性を活かし、実行することで得られるSQL自体のメタデータから、SQLの select 句のカラム構成に対応する受け取り用の Entity クラス、CustomizeEntity を自動生成します。

パラメータコメント

2Way-SQLを実現するための動的制御のためのSQLコメントを、パラメータコメントと呼びます。

パラメータコメントの中で、アプリケーションから指定される動的なパラメータを参照したり判定したりして、SQL上で分岐指定(IF文)・バインド変数指定 などができます。

e.g. 会員IDが指定されていれば、その会員IDで等値条件(テスト値:3)
/*IF pmb.memberId != null*/
MEMBER_ID = /*pmb.memberId*/3
/*END*/
e.g. 複雑な制御を入れたパラメータコメントの利用 (ループなど) @OutsideSql
/*BEGIN*/
where
  /*FOR pmb.memberNameList*/
  /*NEXT 'and '*/member.MEMBER_NAME like /*#current*/'S%'
  /*END*/
  /*IF pmb.memberStatusCode != null*/
  and member.MEMBER_STATUS_CODE = /*pmb.memberStatusCode*/'FML'
  /*END*/
/*END*/

実装上の役割

実装上の役割としては、ConditionBeanでは実現できない複雑(非定型)なSQLを対象とします。 FK制約を付与できない(RDBとしては特殊な)関連での結合group by 結果の取得集計関数以外のSQL関数の利用 といった内容が含まれるSQLを記述することを想定しています。

自由度の高さを重視

バランス重視のDBアクセスI/Fの観点から、徹底して堅い ConditionBean に対してバランスを得るために、DBFluteの外だしSQLは "自由度の高さ" を重視しています。

DBFluteの外だしSQLは、SQLファイルと呼び出し構造が分離されています。 いざとなればSQLファイルの呼び出しは文字列で自由に選べます。 但し、通常はそういうことはせず、BehaviorQueryPath で安全な呼び出しを行います。 こういったラップ式にすることで、外だしSQLの機能の中で安全性と自由度のバランスを得ています。

例えば、一つのSQLファイルで一件検索もできればリスト検索もできます。呼び出し側でそのSQLファイルに対してアプローチしたい検索方法を自由に選べます。 実際にページング検索では、その特徴を活かして、一つのSQLで総件数取得とページング実データ取得の両方を実現しています。 また、SQLファイルの取扱いを開発規模次第でプロジェクト独自のものにしたい可能性もあります。 その場合は、BehaviorQueryPath の枠組みを外して利用します。 "いざとなったら何でもできる" のこの安心感が保たれることが、外だしSQLには求められているのです。