サブクエリ

ConditionBeanにおける定型化されたサブクエリの利用について説明をします。

• タイプセーフなサブクエリ
• 子テーブルの条件で絞り込み
• 子テーブルの導出カラム
  • 子テーブルの導出カラム(データ取得)
  • 子テーブルの導出カラム(絞り込み条件)
• 導出値との比較で絞り込み
• 関連テーブルの条件で絞り込み
• カラムと導出カラム
  • 応用の利く機能
• サブクエリもフォーマット
• 外だしSQLの土台にも利用
• クロージャが欲しくなります

タイプセーフなサブクエリ

ConditionBean ではサブクエリもサポートされています。 サブクエリと聞くと複雑なイメージがありますが、実は業務で使うサブクエリの多くは要件的にパターン化されています。 また、複雑なイメージとは裏腹に利用頻度は意外に多く、実際にバグが非常に生まれやすい部分でもあります。 そういったことから、ConditionBean では積極的に、パターン化されているサブクエリを定型化してタイプセーフに扱う ことができるようにしています。

子テーブルの条件で絞り込み

例えば、2000円以上の(一回の)購入をしたことのある会員を検索する、というような条件。これは、exsits 句の相関サブクエリで実現します。この機能を ExistsReferrer と呼びます。

e.g. 2000円以上の購入をしたことのある会員 @Java
cb.query().existsPurchaseList(new SubQuery<PurchaseCB>() {
    public void query(PurchaseCB subCB) {
        // 2000円以上の購入をしたことのある会員
        subCB.query().setPurchasePrice_GreaterEqual(2000);
    }
});

e.g. 2000円以上の購入をしたことのある会員 @DisplaySql
...
  from MEMBER dfloc
 where exists (select sub1loc.MEMBER_ID
                 from PURCHASE sub1loc
                where sub1loc.MEMBER_ID = dfloc.MEMBER_ID
                  and sub1loc.PURCHASE_PRICE >= 2000
       )

子テーブルの導出カラム

子テーブルの導出カラムに対してアプローチする機能、データ取得と絞り込み条件で二つあります。

子テーブルの導出カラム(データ取得)

例えば、それぞれの会員の最終ログイン日時を会員のデータと一緒に取得する、というような検索。これは、select 句での相関サブクエリで実現します。この機能を (Specify)DerivedReferrer と呼びます。

e.g. (Specify)DerivedReferrerを使って(モバイル除く)最終ログイン日時も取得 @Java
cb.specify().derivedMemberLoginList().max(new SubQuery<MemberLoginCB>() {
    public void query(MemberLoginCB subCB) {
        subCB.specify().columnLoginDatetime(); // 導出カラムの指定
        subCB.query().setMobileLoginFlg_Equal_False(); // 絞り込み条件
    }
}, Member.ALIAS_latestLoginDatetime);

List<Member> memberList = memberBhv.selectList(cb);
for (Member member : memberList) {
    Date latestLoginDatetime = member.getLatestLoginDatetime();
    ...
}

e.g. (Specify)DerivedReferrerを使って(モバイル除く)最終ログイン日時も取得 @DisplaySql
select dfloc.MEMBER_ID as c1, dfloc.MEMBER_NAME as c2, ...
     , (select max(sub1loc.LOGIN_DATETIME)
          from MEMBER_LOGIN sub1loc 
         where sub1loc.MEMBER_ID = dfloc.MEMBER_ID
           and sub1loc.MOBILE_LOGIN_FLG = 0
       ) as LATEST_LOGIN_DATETIME
  from MEMBER dfloc
...

子テーブルの導出カラム(絞り込み条件)

例えば、最大購入価格が10000円以上の会員を検索、というような条件。これは、where 句での相関サブクエリで実現します。この機能を (Query)DerivedReferrer と呼びます。

e.g. (Query)DerivedReferrerを使って(支払済み)最大購入価格が10000円以上の会員を取得 @Java
cb.specify().derivedPurchaseList().max(new SubQuery<PurchaseCB>() {
    public void query(PurchaseCB subCB) {
        subCB.specify().columnPurchasePrice(); // 導出カラムの指定
        subCB.query().setPaymentCompleteFlg_Equal_True(); // 絞り込み条件
    }
}).greaterEqual(10000) // 引数に条件値を設定

e.g. (Query)DerivedReferrerを使って(支払済み)最大購入価格が10000円以上の会員を取得 @DisplaySql
...
  from MEMBER dfloc
 where (select sum(sub1loc.PURCHASE_PRICE)
          from PURCHASE sub1loc
         where sub1loc.MEMBER_ID = dfloc.MEMBER_ID
           and sub1loc.PAYMENT_COMPLETE_FLG = 1
       ) >= 10000
...

導出値との比較で絞り込み

例えば、一番若い会員のデータを検索(生年月日と最大の生年月日の等値)、というような条件。これは、where 句での基点テーブル(対応テーブル)のサブクエリで実現します。

e.g. ScalarConditionを使って一番若い正式会員(のレコード)を取得 @Java
cb.query().scalar_Equal().max(new SubQuery<MemberCB>() {
    public void query(MemberCB subCB) { // 一番若い正式会員
        subCB.specify().columnBirthdate(); // 比較対象カラムの指定
        subCB.query().setMemberStatusCode_Equal_Formalized(); // 絞り込み条件
    }
});

e.g. ScalarConditionを使って一番若い正式会員(のレコード)を取得 @DisplaySql
...
  from MEMBER dfloc
 where dfloc.BIRTHDATE = (select max(sub1loc.BIRTHDATE)
                              from MEMBER sub1loc
                             where sub1loc.MEMBER_STATUS_CODE = 'FML'
       )
...

関連テーブルの条件で絞り込み

InScope (in句) での関連テーブルのサブクエリ(相関でない)を利用して、基点テーブルを絞り込みます。 これは代替機能であり、親テーブルに対して利用した場合は Query(Relation) で絞り込み条件を付与したときと結果は同じになり、子テーブルに対して利用した場合は ExistsReferrer と結果は同じになります。パフォーマンスなどの要件で、SQLでの展開の仕方を微調整したいときに有効です。

カラムと導出カラム

カラム同士の比較条件ができる ColumnQuery と (Specify)DerivedReferrer のコラボレーションにより、ColumnQuery の指定するカラムを導出カラムにすることで柔軟な比較ができます。

e.g. 生まれる前、もしくは生まれた瞬間に購入をしたことのある会員 @Java
...
cb.columnQuery(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnBirthdate();
    }
}).lessEqual(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().derivedPurchaseList().min(new SubQuery<PurchaseCB>() {
            public void query(PurchaseCB subCB) {
                subCB.specify().columnPurchaseDatetime();
                subCB.query().setPaymentCompleteFlg_Equal_True();
            }
        }, null); // エリアス名は利用しないので null 固定
    }
});

e.g. 生まれる前、もしくは、生まれた瞬間に(支払済み)購入をしたことのある会員 @DisplaySql
...
  from MEMBER dfloc
 where dfloc.BIRTHDATE <= (select min(sub1loc.PURCHASE_DATETIME)
                             from PURCHASE sub1loc 
                            where sub1loc.MEMBER_ID = dfloc.MEMBER_ID
                              and sub1loc.PAYMENT_COMPLETE_FLG = 1
       )

サブクエリもフォーマット

ConditionBean で組み立てたサブクエリは、しっかり人の見やすい形にフォーマットされます。 逆にそれができないと、いくらタイプセーフだからといって、本当に自分が意図したクエリになっているかどうかが不安になって仕方ありません。 ログのSQLをコピーしてSQLツールに貼り付けてフォーマットできなくはないですが多少面倒ですし、 なかなかサブクエリまで綺麗にフォーマットしてくれるツールも多くはありません。

それも踏まえた上での実現を内部的な実装で行っています。 階層も解決された ConditionBean のサブクエリ、もし気が向けば、サブクエリがどうできあがっているのか、ソースを追っかけてみると新鮮かも知れません。

外だしSQLの土台にも利用

最初から外だしSQLで書く、と決まっている場合でも、この ConditionBean のサブクエリの機能を使って実装し、組み立てられた表示用SQLをもとに、外だしSQLにおけるサブクエリの土台にするという技もあります。 サブクエリは一から書くと大変な上に、非常に間違いやすいものです。かつ、間違ったときに間違いを発見しにくいものです。ConditionBean でのサブクエリ実装に慣れてしまえば、このようなやり方で自身の外だしSQLの実装を支援することができます。

e.g. 2000円以上の購入をしたことのある会員 (C# では delegate で実現) @C#
cb.Query().ExistsPurchaseList(delegate(PurchaseCB subCB) {
    // 2000円以上の購入をしたことのある会員
    subCB.Query().SetPurchasePrice_GreaterEqual(2000);
});

e.g. Eclipse-3.5 以上でコールバック実装のコード補完 @Java
// "new " (new + 空白一つ) と打って ctrl + space そして enter
cb.query().existsPurchaseList(new )
--
// 実装メソッドの空実装が自動生成される (Eclipse-3.5 以上)
cb.query().existsPurchaseList(new SubQuery<PurchaseCB>() {
    
    public void query(PurchaseCB subCB) {
        // TODO Auto-generated method stub
        
    }
})