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