外だしSQLの使い方

外だしSQLの実装における手順や仕様などのページです。

実装の流れ
外だしSQLをパラメータコメントを使って2Way-SQLで書く
Sql2Entityで自動生成
outsideSql()メソッドから実行
実装コンセプト
細かい補足仕様
コード補完を駆使して実装
- 先頭文字を覚えておくのが大事
- 戻り値の抽出

実装の流れ

実装の流れは以下のようになります。

外だしSQLを パラメータコメント を使って 2Way-SQL で書く
Sql2Entity を実行して CustomizeEntity と ParameterBean を自動生成
Behaviorの outsideSql() メソッドから実行

外だしSQLをパラメータコメントを使って2Way-SQLで書く

まず前提: Eclipse or IntelliJ or ...?

ドキュメントとしては、Eclipse で EMechaプラグインを使っていることを前提にしていますが、 IntelliJでも最終的に規約どおりの外だしSQLが(手動で)作成できればOK です。手順を参考に手動で外だしSQLを作成してください。

SQLファイルの作成 (EMecha利用)

外だしSQLのSQLは、厳密には基点テーブルが特定できないこともありますが、管理上どこかのテーブルに紐付けます。 外だしSQLの紐付けテーブル と呼びます。

基点テーブルの考え方は、ConditionBeanと同じです。検索結果のレコードの粒度が一致するテーブル を見つけましょう。厳密に一致するテーブルが存在しない場合でも、検索結果に一番影響を与えている中心となるテーブルを見つけましょう。(ここは厳密でなくても動作しますので、どちらかと言えば、というレベルでOKです)

ConditionBean - 目的ドリブン

Eclipse の "リソースの検索(ctrl + shift + R)" から対応する Behavior (Exクラス) を開き、Javaエディター内で右クリックして EMecha - New Outside Sql と選択すると画面が起動します。 (Eclipseでない方は、EMechaで作成される相当のSQLファイルを手動で作成します)

EMecha - EMSql - SQLファイルの作成

検索ショートカット

図 : EMSqlの画面 (初期状態)

SQL名 (SQL Name) を入力

初期状態では、select が選択されていています。検索系のSQLであればそのまま SQL Name の select に続けて(システムでユニークな) SQL業務名 を入力します。検索でなければ該当するものを選択してSQL名を完成させます。

e.g. selectUnpaidSummaryMember (未払い購入情報付き会員)
e.g. updateMemberChangedToWithdrawalForcedly (会員の強制退会)

SQLのタイトルを入力

そのSQLを一行で表現するタイトルを入力します。※思い付かなければ後でも構いません

Sql2Entityオプションを選択

基本的にはデフォルトのチェックはそのままでOK。(更新系なら CustomizeEntity を外す)

ページング検索するなら、"Use Paging"
カーソル検索するなら、"Use Cursor"
スカラ検索 (最大値など一つの値を取るだけの検索) するなら、"Use Scalar"
ドメイン検索 (既に存在するテーブルのEntityを利用) するなら、"Use Domain"

したら、Enter どーん！

パッケージ: src/main/resourcesのBehavior(Exクラス)と同じパッケージ
ファイル名: [紐付けテーブルBhv]_[select,updateなど][SQL業務名].sql

e.g. EMechaで作成されたSQLファイル {src/main/resources} @Directory
foo-project
 |-dbflute_foo
   |-dfprop
   |-...
 |-src/main/resources
   |-com
     |-...
       |-dbflute
         |-exbhv
           |-MemberBhv_selectSimpleMember.sql

SQLファイルのひな形が生成され、Sql2Entityマークも指定された通りに設定されています。

Sql2Entityマーク

#df:entity#: CustomizeEntityマーク (Entityを自動生成するぞ宣言)
!df:pmb!: ParameterBeanマーク (Pmbを自動生成するぞ宣言)
!!AutoDetect!!: ParameterBeanのプロパティ項目の自動判別マーク

e.g. EMechaで作成したばかりのSQL (CustomizeEntity と ParameterBean を利用) @OutsideSql
/*
 [df:title]
 SQL title here.
 
 [df:description]
 SQL description here.
*/
-- #df:entity#

-- !df:pmb!
-- !!AutoDetect!!

select ...
  from ...
 where ...
 order by ...

Eclipse以外の環境を使っているなど、どうしても EMecha が利用できないケースでも、EMechaで作っているSQLファイルを手動で作成すればOKです。パッケージやSql2Entityマークなど、文字の打ち間違いに気をつけましょう。

タイトル (df:title) や、説明(df:description)は必須です(Sql2EntityタスクやOutsideSqlタスクでチェックされます)。外だしSQLには、必ず業務的なタイトルと説明を入れましょう。 "SQL title here." や "SQL description here." のままではいけません。

2Way-SQLを実装

そうしたら、パラメータコメントを駆使して、2Way-SQLとしてSQLを実装していきます。

パラメータコメント

バインド変数コメント: /*pmb.memberName*/'S%' ※必ずテスト値を入れること
IFコメント: /*IF pmb.memberStatusCode != null*/ ... /*END*/
BEGINコメント: /*BEGIN*/where ... /*END*/
FORコメント: /*FOR pmb.memberNameList*/ ... /*END*/

e.g. 2Way-SQLでの実装 (CustomizeEntity と ParameterBean を利用) @OutsideSql
/*
 [df:title]
 単なるシンプルな会員検索
 
 [df:description]
 これはDBFluteドキュメント上のExampleであり、
 正直、このくらいなら ConditionBean で実装してしまうだろう。
*/
-- #df:entity#

-- !df:pmb!
-- !!AutoDetect!!

select mb.MEMBER_ID
     , mb.MEMBER_NAME
     , stat.MEMBER_STATUS_NAME
  from MEMBER mb
    left outer join MEMBER_STATUS stat
      on mb.MEMBER_STATUS_CODE = stat.MEMBER_STATUS_CODE
 /*BEGIN*/
 where
   /*IF pmb.memberId != null*/
   mb.MEMBER_ID = /*pmb.memberId*/3
   /*END*/
   /*IF pmb.memberName != null*/
   and mb.MEMBER_NAME like /*pmb.memberName*/'M%'
   /*END*/
 /*END*/
 order by mb.BIRTHDATE desc, mb.MEMBER_ID asc

SQLのタイトルや説明: 他の人や将来の自分がこのSQLをメンテナンスするときのために、df:title, df:description にSQLのタイトルや説明を入れておくと良いでしょう。書いておくと SchemaHTML でも表示されます。
(外だしSQL)現場フィット - 外だしSQLのタイトル

SchemaHTML - 外だしSQL一覧
検索カラムのコメント: 関数などで計算処理を入れたカラム、SQLだけ見てもなんでここでそれを検索しているのかわからないカラム、アプリケーションで利用上の注意があるようなカラム、こういった特徴的なカラムを検索するような場合は、検索カラムのコメントを入れておくと良いでしょう。
CustomizeEntity - 検索カラムのコメント
ParameterBeanのオプション: (主に)バインド変数コメントにおいて、ParameterBeanのプロパティにオプションを付与することができます。これらをしっかり利用することで、より安全でスピーディーな実装ができます。
ParameterBeanのオプション

Sql2Entityで自動生成

Sql2Entityタスクの実行

それでは、Sql2Entityタスクを叩いて、CustomizeEntity や ParameterBean を自動生成しましょう。 DBFluteクライアント配下の manage.bat|sh から Sql2Entity を実行します。

Windows環境のEclipse上からは、ダブルクリックで実行できます。もしくは、"ctrl + shift + R" の "Open Resource" からコマンドファイルを選択して実行できます(多くの人がこれで実行しています)。

Sql2Entity - 実行コマンド

成功するまで修正＆トライ

このとき、Sql2Entity内では、外だしSQLが 2Way-SQL として実際のデータベースに実行されます。ゆえに、この時点でSQLの文法エラーがあるとタスクが落ちます。コンソールと dbflute.log にエラーが出力されますので、そのエラー内容をもとにSQLを直して通るまで再実行を繰り返します。

タスク実行ログ

成功すると自動生成される

成功すると、CustomizeEntity や ParameterBean が所定のパッケージに自動生成されます。

CustomizeEntity: bsentity.customize と exentity.customize 配下
ParameterBean: bsbhv.pmbean と exentity.pmbean 配下

outsideSql()メソッドから実行

外だしSQLの呼び出し実装

それでは準備が整ったので、BehaviorのoutsideSql()メソッドから外だしSQLを実行しましょう。

ParameterBean を new して条件値をセットしていく ※SQL業務名 + Pmb という名前のクラス
outsideSql()メソッドから検索スタイルを選ぶ ※リスト検索、一件検索、ページング検索など
ctrl+2, L のショートカットで戻り値を補完 ※CustomizeEntityで受け取り

e.g. リスト検索 (TypedParameterBean) {名前が 'S' で始まる会員を検索｝@Java
SimpleMemberPmb pmb = new SimpleMemberPmb();
pmb.setMemberName_PrefixSearch("S");

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

リスト検索: outsideSql().selectList(pmb)
一件検索: outsideSql().selectEntity(pmb)
一件検索(チェック): outsideSql().selectEntityWithDeletedCheck(pmb)
手動ページング検索: outsideSql().selectPage(pmb) as manual
自動ページング検索: outsideSql().selectPage(pmb) as auto
カーソル検索: outsideSql().selectPage(pmb)

ParameterBeanのプロパティがおかしい場合

ParameterBeanのプロパティ項目は、自動判別機能(AutoDetect)を使って生成されています。ゆえに、時にプロパティのプログラム型が自動判別仕切れず想定外のものになってしまうことがあります。自動判別の情報となるバインド変数コメントのテスト値を判別ルールと照らし合わせて見直したり、プロパティ項目の明示的な宣言を利用したりして微調整しましょう。 (直したら、もういっかい Sql2Entity を実行)

プロパティがない: プロパティ自動判別対象外ではないか？
プログラム型がおかしい: ルールとテスト値を見直し(refも検討)、だめなら明示的宣言で微調整
オプションが付与されない: オプション付与のルールを見直し、だめなら明示的宣言で微調整

outsideSql()の呼び出しでコンパイルエラー

selectList(pmb) や manualPaging().selectPage(pmb) の部分で、"引数の型が違う" というコンパイルエラーが発生するような場合は、その外だしSQLがその検索スタイルに合っていないこと を示します。例えば、ページングじゃない外だしSQLの ParameterBean で selectPage() は呼べません。

ParameterBean の JavaDoc コメントで、どの検索スタイルに対応しているかどうか表示されますので、まずはそこを確認してみてください。 (Eclipse上でカーソルを合わせるとぴょいっと出てきます)

e.g. ParameterBean の JavaDoc をぴょいっと表示 (リスト検索と一件検索に対応)  @JavaDoc
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
The typed parameter-bean of SimpleMember. (typed to list, entity)
This is related to "selectSimpleMember" on MemberBhv,
described as "単なるシンプルな会員検索".

You can implement your original methods here.
This class remains when re-generating.
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

想定した検索スタイルに対応していなければ、以下のポイントを踏まえて外だしSQLを確認してみて、修正したらもう一度 Sql2Entity を実行してみてみましょう。

Behavior: 紐付きテーブルのBehaviorかどうか？ ※間違ってると呼べない
パッケージ: パッケージ間違っていると何にもならない ※EMechaで作っていればありえない
ページング宣言: ページング宣言していれば、manual/auto の selectPage() が呼べる
ページング条件: ページング条件があれば、manualPaging() の selectPage() が呼べる
カーソル宣言: カーソル宣言していれば、cursorHandling() の selectCursor() が呼べる

実装コンセプト

外だしSQLの実装におけるコンセプトを理解しておくと、より一層実装がスムーズに進むでしょう。

外だしSQLの実装コンセプト

細かい補足仕様

Behaviorインスタンスの取得

Behaviorインスタンスは DIコンテナから取得 します。

Behaviorの使い方

エンコーディング

outsideSqlDefinitionMap.dfprop の sqlFileEncoding で指定されたエンコーディング(デフォルトは UTF-8)で作成する必要があります。

dfprop - sqlFileEncoding

存在しないBehaviorを指定すると

存在しないBehaviorを指定すると、Sql2Entityで明示的な例外が発生します。テーブル名の変更などで利用していたBehaviorが削除された場合は、Sql2Entityを実行することで検知して修正します。

外だしSQLの実行ログ

外だしSQLの実行ログ(表示用SQLも含む)は以下のようになります。

現場フィット - デバッグログ

現場フィット - 表示用SQL

e.g. リスト検索のテスト実装のログ @Log
(XLog#log():38) - /========================================================================================
(XLog#log():38) -                                                       MemberBhv.outsideSql().selectList()
(XLog#log():38) -                                                       ==================================/
(XLog#log():38) - BehaviorBasicTest.test_outsideSql_selectList_selectSimpleMember_Tx():457 -> ...
(XLog#log():38) - path: selectSimpleMember
(XLog#log():38) - option: {paging=non, dynamic=false}
(QLog#log():38) - 
/*
 [df:title]
 シンプルな会員検索のExample
 
 [df:description]
 このSQLはOutsideSqlの最も基本的なExampleです。
 参考ポイント：
   o パラメータコメント(IFコメントやバインド変数コメントなど)の利用方法
   o Sql2Entityで生成するCustomizeEntityとParameterBeanの定義方法
   o 外だしSQLの業務的なタイトル・説明(SchemaHTMLにて表示される)の記述方法
 外だしSQLのエンコーディングはデフォルトで「UTF-8」です。
 ダブルバイト文字を利用する場合はエディタのエンコーディングにご注意下さい。
 補足：
   o この外だしSQLと同じSQLはConditionBeanで実現可能
*/
-- #df:entity#

-- !df:pmb!
-- !!AutoDetect!!

select member.MEMBER_ID
     , member.MEMBER_NAME
     , member.BIRTHDATE
     , memberStatus.MEMBER_STATUS_NAME
  from MEMBER member
    left outer join MEMBER_STATUS memberStatus
      on member.MEMBER_STATUS_CODE = memberStatus.MEMBER_STATUS_CODE
 
 where
   
   member.MEMBER_NAME like 'S%' escape '|' 
   
   
 
 order by member.BIRTHDATE desc, member.MEMBER_ID asc
(XLog#log():38) - ===========/ [00m00s063ms (4) first={12, Suker, 1968-01-01, 仮会員}@ef28f3f]
(XLog#log():38) -

フリースタイルの呼び出しメソッド

一方、ParameterBean が宣言されている SQL 以外の SQL を呼び出したり(つまり、別のSQLで ParameterBean を再利用)、CustomizeEntity を利用せずに独自のクラスを戻り値の型にしたりなど、パスやパラメータ、戻り値の型がバラバラに連携するような場合は、それぞれの要素を個別に指定して呼び出します(フリースタイル形式)。

e.g. リスト検索 (フリースタイル形式) {名前が 'S' で始まる会員を検索｝@Java
// SQLのパス: BehaviorQueryPath
String path = MemberBhv.PATH_selectSimpleMember;

// パラメータ: ParameterBean
SimpleMemberPmb pmb = new SimpleMemberPmb();
pmb.setMemberName_PrefixSearch("S");

// 戻り値Entityの型: CustomizeEntity
Class<SimpleMember> entityType = SimpleMember.class;

// 外だしSQLの実行 (指定された path に対応するSQL)
List<SimpleMember> memberList
        = memberBhv.outsideSql().selectList(path, pmb, entityType);

フリースタイルにおける全てメソッドで共通の基本仕様です。ここにない情報はそれぞれのメソッドの詳細をご覧下さい。

SQLのパス (path): 主に第一引数で指定します。必須の引数なので null を指定してはいけません。; String型ですが、基本的には BehaviorQueryPath を利用することで簡単に指定できます。 BehaviorQueryPath を利用しない場合は、"/" (スラッシュ)区切りのクラスパスからのファイルパスとなります。
Sql2Entity - BehaviorQueryPath; 存在しないパスを指定した場合は、OutsideSqlNotFoundException が発生します。
ParameterBean (pmb): SQLへ渡すパラメータがない場合は null 指定できます。; Object型なので、(推奨されませんが)自動生成していないクラスも指定できます。また、パラメータが一つだけの場合は、String 型や Integer 型などの値をそのまま指定することもできます。但し、その場合、ParameterBeanの空文字フィルタやもろもろのオプションは利用できません。
this - ParameterBeanマーク
CustomizeEntity (entityType): 主に検索時(かつ、カーソル検索でない)において戻り値Entityの型を第三引数で指定します。必須の引数なので null を指定してはいけません。Entity ではなく String や Integer などのスカラ型も指定できます(これらも広い意味でEntityと概念付けます)。

コード補完を駆使して実装

外だしSQLとはいえ、せめて呼び出す処理に関しては、コード補完で実装しましょう。メソッド名を一字一句丁寧に打ち込むというようなことはやらずに、メソッドの先頭の文字を打ち込んでメソッド候補を補完 して、利用したいメソッドを選んで実装していくようにします。ここでは Eclipse を基本として説明します。

先頭文字を覚えておくのが大事

外だしSQLのメソッドは多くありません。ゆえに、先頭文字さえ打ってしまえば、その時点でほとんどメソッドを呼び出したも同然です。

まずは、必ず唱える outsideSql() メソッド。Behaviorには、"o" で始まる public メソッドは他にありません。".o" と打って enter とするだけで呼び出せます。

e.g. Eclipseでのショートカット (outsideSql()を一文字で補完) @Java
memberBhv.o // ".o" とだけ打って enter
 to
memberBhv.outsideSql() // 続けて "." と打って続きのメソッドを補完

その他メソッド(経由するメソッドも含む)に関しても、先頭文字をおおよそ把握しておくとさらに良いでしょう。

外だしSQLの機能

戻り値の抽出

これは、Behaviorで紹介しているものと全く同じです。特に外だしSQLは、戻り値の型は Generic で指定しますので、戻り値の型は自動で抽出するのがお奨めです。

Behavior - 戻り値の抽出