外だしSQL(OutsideSql)について

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

• 外だしSQL(OutsideSql)とは？
  • 二つの特徴
  • 自動生成ドリブン
• 2Way-SQL
  • 素のままでも実行できる
  • SQLのメタデータからEntityを自動生成
• パラメータコメント
• 実装上の役割
• 自由度の高さを重視

外だし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 mb.MEMBER_ID
     , mb.MEMBER_NAME
     , (select sum(pur.PURCHASE_PRICE)
          from PURCHASE pur
         where pur.MEMBER_ID = mb.MEMBER_ID
           and pur.PAYMENT_COMPLETE_FLG = 0
       ) as UNPAID_PRICE_SUMMARY
     , stat.MEMBER_STATUS_NAME
-- ELSE select count(*)
/*END*/
  from MEMBER mb
    /*IF pmb.isPaging()*/
    left outer join MEMBER_STATUS stat
      on mb.MEMBER_STATUS_CODE = stat.MEMBER_STATUS_CODE
    /*END*/
 /*BEGIN*/
 where
   /*IF pmb.memberId != null*/
       mb.MEMBER_ID = /*pmb.memberId*/3
   /*END*/
   /*IF pmb.memberStatusCode != null*/
   and mb.MEMBER_STATUS_CODE = /*pmb.memberStatusCode*/'FML'
   /*END*/
   /*IF pmb.unpaidMemberOnly*/
   and exists (select pur.PURCHASE_ID
                 from PURCHASE pur
                where pur.MEMBER_ID = mb.MEMBER_ID
                  and pur.PAYMENT_COMPLETE_FLG = 0
       )
   /*END*/
 /*END*/
 /*IF pmb.isPaging()*/
 order by UNPAID_PRICE_SUMMARY desc, mb.MEMBER_ID asc
 /*END*/

SQLのメタデータからEntityを自動生成

この 2Way-SQL を利用することで、さらなる恩恵を得ることができます。アプリ用のSQLをSQLツールで実行できるということは、DBFluteというツールでも実行できます。

DBFluteでは、2Way-SQL の特性を活かし、実行することで得られるSQL自体のメタデータから、SQLの select 句のカラム構成に対応する 受け取り用の Entity クラス "CustomizeEntity" を自動生成 します。

e.g. Sql2Entityで自動生成したクラスを生成 @Java
// 自動生成されたSQLへの引数クラス (ParameterBean) を使ってバインド変数の値をセット
UnpaidSummaryMemberPmb pmb = new UnpaidSummaryMemberPmb();
pmb.setMemberName_PrefixSearch("S");

// 外だしSQLの実行 (MemberBhv_selectUnpaidSummaryMember.sql)
List<UnpaidSummaryMember> memberList
        = memberBhv.outsideSql().selectList(pmb);

// 自動生成された受け取りクラス (CustomizeEntity) を使って検索データを扱う
for (UnpaidSummaryMember member : memberList) {
    Integer memberId = member.getMemberId();
    String memberName = member.getMemberName();
    String statusName = member.getMemberStatusName();
    ...
}

パラメータコメント

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を記述することを想定しています。