第4回　ConditionBeanにおける結合やソート

はじめに

前回は、ConditionBeanの条件組立ての基本機能をみていきました。

今回は、結合やソートの基本をみていきましょう。

ConditionBean

ConditionBeanにおける結合とは？

まず、ConditionBeanにおける結合とは何を示すかを意識合わせしておきましょう。結合のそのものの意味では単に「テーブルを結合する」ことだけを示します。結合すること自体は「手段」であり「目的」ではありません。

では、目的はどんなものがあるかというと、主に次の2つに整理できます。

結合先テーブルのデータを取得すること
結合先テーブルの条件で結果を絞り込むこと

ConditionBeanにおける結合は、このような目的を意識した仕様となっています。具体的には、プログラマはConditionBeanに対して上記の目的をそのまま伝えます。そして、結合をするかしないかは、指定された目的を基に自動で判別します。

例えば、「⁠結合先テーブルの条件で結果を絞り込むこと」という目的を達したい場合は、以下（リスト1、2）のようになります。

リスト1：結合先テーブルのデータを取得する-ConditionBeanでの指定

final MemberCB cb = new MemberCB();

// 親テーブルの会員ステータスを結合し、Select句に含める。
cb.setupSelect_MemberStatus();

リスト2：結合先テーブルのデータを取得する-実行されるSQL文

select member.MEMBER_ID, ... , memberStatus.MEMBER_STATUS_CODE, ...
  from MEMBER member
    left outer join MEMBER_STATUS memberStatus
      on member.MEMBER_STATUS_CODE = memberStatus.MEMBER_STATUS_CODE

また、「⁠結合先テーブルの条件で結果を絞り込むこと」という目的を達したい場合は、以下（リスト3、4）のようになります。

リスト3：結合先テーブルの条件で結果を絞り込む-ConditionBeanでの指定

final MemberCB cb = new MemberCB();

// 親テーブルの会員ステータスを結合し、
// 「'正'という文字で始まる会員ステータス名」という条件で絞り込む。
cb.query().queryMemberStatus().setMemberStatusName_PrefixSearch("正");

リスト4：結合先テーブルの条件で結果を絞り込む-実行されるSQL文

select member.MEMBER_ID, ...
  from MEMBER member
    left outer join MEMBER_STATUS memberStatus
      on member.MEMBER_STATUS_CODE = memberStatus.MEMBER_STATUS_CODE
 where memberStatus.MEMBER_STATUS_NAME like '正%'

無論、これら目的が同居する場合は、以下（リスト5、6）のようになります。

結合に関連する目的が2つ指定されたからといって、SQL上でJoin節が2つ出力されてしまうことはありません。

リスト5：結合先テーブルの条件で結果を絞り込む-ConditionBeanでの指定

final MemberCB cb = new MemberCB();

// 親テーブルの会員ステータスを結合し、Select句に含める。
cb.setupSelect_MemberStatus();

// 親テーブルの会員ステータスを結合し、
// 「'正'という文字で始まる会員ステータス名」という条件で絞り込む。
cb.query().queryMemberStatus().setMemberStatusName_PrefixSearch("正");

リスト6：結合先テーブルの条件で結果を絞り込む-実行されるSQL文

select member.MEMBER_ID, ..., memberStatus.MEMBER_STATUS_CODE, ...
  from MEMBER member
    left outer join MEMBER_STATUS memberStatus
      on member.MEMBER_STATUS_CODE = memberStatus.MEMBER_STATUS_CODE
 where memberStatus.MEMBER_STATUS_NAME like '正%'

ConditionBeanでは、結合先テーブルの条件を追加したからといって、勝手に結合先テーブルのデータを取得することはしません。

実際の業務ロジックでは、「⁠結合先テーブルの条件で絞り込んでデータとして取得するのは基点テーブルのみ」ということも多々あります。不要な結合先のテーブルのデータは取得しないようにするべき、というのがConditionBeanの思想です。

「呼び出し側プログラムが必ず何のテーブルを取得したいのか？　（⁠絞込み条件に関わらず）を意識するべき」という考えのもと設計されています。

結合先テーブルのデータを取得すること

まずは、先述した目的の一つ「結合先テーブルのデータを取得すること」からみていきましょう。

実は、既に説明されてしまっている部分もありますが、しっかり単体テストの形式で構造的に追っていきます。

結合して親テーブルの取得

まずは、結合して親テーブルのデータを取得するやり方です（リスト7⁠）⁠。

cb.setupSelect_MemberStatus()を呼び出していることがポイントです。

リスト7：結合して親テーブルの取得

/**
 * 結合して会員ステータス(親テーブル)を取得する会員一覧を検索
 * 
 * @throws Exception
 */
public void test_ConditionBean_SetupSelect_ForeignTable_Tx() throws Exception {
// ## Arrange ##
    final MemberCB cb = new MemberCB();
    cb.setupSelect_MemberStatus();

    // ## Act ##
    final List memberList = memberBhv.selectList(cb);

    // ## Assert ##
    for (Member member : memberList) {
        final MemberStatus memberStatus = member.getMemberStatus();
        assertNotNull(memberStatus);
    }
}

会員Entityから会員ステータスEntityが取得できます。

もし、setupSelect_MemberStatus()が呼び出されなかった場合は、getMemberStatus()がnullを戻します。

また、setupSelect_MemberStatus()を呼び出されていても、そのFK列がNullの会員はgetMemberStatus()がnullを戻します。

この例題の場合は、FK列の会員ステータスコードにNotNull制約があり、会員に対して会員ステータスが必ず存在するため、「⁠getMemberStatus()が絶対にNullを戻さないこと」というように確認をしています。

結合してone-to-oneテーブルの取得

テーブル設計において、one-to-one関連のテーブルを作成することがよくあります。

表現の仕方は様々ですが、ここでのone-to-oneはスキーマ例の「会員セキュリティ⁠」⁠・「⁠会員退会」などのようなone-to-manyのmany側のFK列にユニークな制約（PrimaryKeyなど）が掛かっているような構造のものを示します。

そして、そのone-to-oneテーブルも結合して取得することが可能です（リスト8⁠）⁠。cb.setupSelect_MemberSecurityAsOne()を呼び出していることがポイントです。

リスト8：結合して親テーブルの取得

/**
 * 結合して会員セキュリティ情報(one-to-oneテーブル)を取得する会員一覧を検索
 * 
 * @throws Exception
 */
public void test_ConditionBean_SetupSelect_ReferrerAsOneTable_Tx() throws Exception {
    // ## Arrange ##
    final MemberCB cb = new MemberCB();
    cb.setupSelect_MemberSecurityAsOne();

    // ## Act ##
    final List memberList = memberBhv.selectList(cb);

    // ## Assert ##
    for (Member member : memberList) {
        final MemberSecurity memberSecurityAsOne = member.getMemberSecurityAsOne();
        log.debug("memberSecurityAsOne=" + memberSecurityAsOne);
        assertNotNull(memberSecurityAsOne);
    }
}

会員Entityから会員セキュリティ情報Entityが取得できます。

もし、setupSelect_MemberSecurityAsOne()が呼び出されなかった場合は、getMemberStatus()がnullを戻します。

また、setupSelect_MemberStatus()を呼び出していても、該当の会員セキュリティ情報が存在しない会員の場合は、getMemberStatus()がnullを戻します。

この例題の場合は、テストデータとして会員に対して会員セキュリティが必ず存在するようにしているため、「⁠getMemberSecurityAsOne()が絶対にNullを戻さないこと」というように確認をしています。

また、以後説明の中で「親テーブル」と言った場合に、この「one-to-oneテーブル」も含みます。

実際には親テーブルではありませんが、DBFlute上では「one-to-oneであれば親テーブルのように扱うことができる」ため、そのように表現させて頂きます。

結合して親の親テーブルの取得

そして、親の親テーブルの取得です。

まずは、結合して親の親テーブルのデータを取得するやり方です（リスト9⁠）⁠。cb.setupSelect_MemberWithdrawalAsOne().withWithdrawalReason()を呼び出していることがポイントです。

リスト9：結合して親の親テーブルの取得

/**
 * 結合して会員退会情報と退会理由(親テーブル＆親の親テーブル)を取得する会員一覧を検索
 * 
 * @throws Exception
 */
public void test_ConditionBean_SetupSelect_ForeignTable_with_Foreign_Tx() throws Exception {
    // ## Arrange ##
    final MemberCB cb = new MemberCB();
    cb.setupSelect_MemberWithdrawalAsOne().withWithdrawalReason();

    // ## Act ##
    final List memberList = memberBhv.selectList(cb);

    // ## Assert ##
    boolean existsOneOrMoreAtLeast = false;
    for (Member member : memberList) {
        final MemberWithdrawal memberWithdrawalAsOne = member.getMemberWithdrawalAsOne();
        log.debug("memberWithdrawalAsOne=" + memberWithdrawalAsOne);
        if (memberWithdrawalAsOne != null) {
            existsOneOrMoreAtLeast = true;
            final WithdrawalReason withdrawalReason = memberWithdrawalAsOne.getWithdrawalReason();
            log.debug("    withdrawalReason=" + withdrawalReason);
            assertNotNull(withdrawalReason);
        }
    }
    // 少なくとも一件以上は退会者の情報を持っていることのチェック(テスト成立確認)
    assertTrue(existsOneOrMoreAtLeast);
}

会員Entityから会員退会情報Entityを取得して、その会員退会情報Entityから退会理由Entityが取得できます。

この例題の場合は、会員退会情報は全ての会員が保持しているわけではないので、会員退会情報Entityのnullチェックを行っています。

退会理由Entityは会員退会情報があれば必須なので（NotNull制約あり⁠）⁠、「⁠getWithdrawalReason()が絶対にNullを戻さないこと」というように確認をしています。

親テーブルの取得の制限

親テーブルを取得するsetupSelect_Xxx()メソッドならびにwithXxx()メソッドに関してですが、2階層目の親までという制限があります。つまり、親の親の親テーブルを結合して取得することはできません。

これは、フレームワーク上の都合として現状はこのような仕様になっております。

業務利用上、3階層以上の親を結合して取得したい場合はそこまで多くはないため、実際の支障は少ないですが、いずれ改善はしたいと考えている部分ではあります。

よく勘違いされやすいのですが、「⁠条件絞りをするための結合」の制限ではありません。

そちらの結合の方は、親方向に無限階層で条件を指定可能です。なので、親テーブルの結合の制限ではなく「親テーブルの取得の制限」という表現をしています。

結合先テーブルの条件で結果を絞り込むこと

次は、結合のもう一つの目的「結合先テーブルの条件で結果を絞り込むこと」をみていきましょう。

結合して親テーブルの条件で結果を絞り込むやり方です（リスト10⁠）⁠。cb.query().queryMemberStatus().set...というように親テーブルでの条件を指定しているところがポイントです。

リスト10：結合して親テーブルの条件で結果を絞込み

/**
 * 結合した会員ステータスの名称が“正”で始まる会員一覧を検索
 * 
 * @throws Exception
 */
public void test_ConditionBean_QueryForeign_ForeignTable_Tx() throws Exception {
    // ## Arrange ##
    final MemberCB cb = new MemberCB();
    cb.query().queryMemberStatus().setMemberStatusName_PrefixSearch("正");

    // ## Act ##
    final List memberList = memberBhv.selectList(cb);

    // ## Assert ##
    final MemberStatusCB memberStatusCB = new MemberStatusCB();
    memberStatusCB.query().setMemberStatusName_PrefixSearch("正");
    final MemberStatus memberFormalizedStatus = memberStatusBhv.selectEntityWithDeletedCheck(memberStatusCB);
    final String formalizedCode = memberFormalizedStatus.getMemberStatusCode();
    for (Member member : memberList) {
        log.debug("member=" + member);
        final String memberStatusCode = member.getMemberStatusCode();
        assertTrue("正式会員を示すコードであること", memberStatusCode.equals(formalizedCode));
        assertNull("会員ステータス自身は取得はしていないこと", member.getMemberStatus());
    }
}

先述の通り、one-to-oneテーブルも親テーブルとして扱うことが可能なため、「⁠親テーブルの条件で絞込み」の例題のように指定して条件を付けることが可能です。

また、queryXxx()メソッドは、親方向に無限階層指定することが可能なため、SetupSelectの「親テーブルの取得の制限」というような階層制限は存在しません。

ConditionBeanでソート指定

次はソートのやり方をみていきましょう。

ソートに関してはあまり深い概念は存在しませんので、プログラムをみてもらうのが一番早いかと思われます（リスト11⁠）⁠。cb.query().addOrderBy_MemberAccount_Desc()というようにソート条件を指定しているところがポイントです。

リスト11：基点テーブルの列で並べて検索

/**
 * 会員アカウントの降順で並べた会員一覧を検索
 * 
 * @throws Exception
 */
public void test_ConditionBean_Query_AddOrderBy_Tx() throws Exception {
    // ## Arrange ##
    final MemberCB cb = new MemberCB();
    cb.query().addOrderBy_MemberAccount_Desc();

    // ## Act ##
    final List memberList = memberBhv.selectList(cb);

    // ## Assert ##
    String preMemberAccount = null;
    for (Member member : memberList) {
        final String memberName = member.getMemberName();
        final String memberAccount = member.getMemberAccount();
        log.debug("memberName=" + memberName + " memberAccount=" + memberAccount);
        if (preMemberAccount == null) {
            preMemberAccount = memberAccount;
            continue;
        }
        if (preMemberAccount.compareTo(memberAccount) > 0) {
            // OK
        } else if (preMemberAccount.compareTo(memberAccount) == 0) {
            // OK
        } else {
            fail();
        }
    }
}

AscなのかDescなのかは、メソッド指定で決定します。

複数のソート条件を指定したい場合は、addOrderBy_Xxx...()を順次呼び出していけば、呼び出した順番でソート条件が追加されます。

ConditionBeanでの基本検索

それでは、おおよその基本をみてきたので、ここでそれらを組み合わせた検索をみておきましょう（リスト12、13⁠）⁠。

今までに登場してきた条件絞込み・結合・ソートを組み合わせた検索になっております。これら基本機能だけで、アプリケーションにて発行するSQLの多くを表現することが可能でしょう。

リスト12：基本検索

/**
 * 以下の条件の会員一覧を検索。
 * ・会員ステータスと退会理由を取得
 * ・会員アカウントが“M”で始まる
 * ・会員ステータス.表示順の昇順、会員.誕生日の降順、会員.会員IDの昇順でソート
 * 
 * @throws Exception
 */
public void test_ConditionBean_BasicSelect_Tx() throws Exception {
    // ## Arrange ##
    final MemberCB cb = new MemberCB();
    cb.setupSelect_MemberStatus();
    cb.setupSelect_MemberWithdrawalAsOne().withWithdrawalReason();
    cb.query().setMemberAccount_PrefixSearch("M");
    cb.query().queryMemberStatus().addOrderBy_DisplayOrder_Asc();
    cb.query().addOrderBy_MemberBirthday_Desc();
    cb.query().addOrderBy_MemberId_Asc();

    // ## Act ##
    final List memberList = memberBhv.selectList(cb);

    // ## Assert ##
    for (Member member : memberList) {
        final String memberAccount = member.getMemberAccount();
        final Date memberBirthday = member.getMemberBirthday();
        final String memberStatusName = member.getMemberStatus().getMemberStatusName();
        final MemberWithdrawal memberWithdrawalAsOne = member.getMemberWithdrawalAsOne();
        String withdrawalReasonText = null;
        if (memberWithdrawalAsOne != null) {
            final WithdrawalReason withdrawalReason = memberWithdrawalAsOne.getWithdrawalReason();
            withdrawalReasonText = withdrawalReason.getWithdrawalReasonText();
        }
        log.debug(memberAccount + " - " + memberBirthday + " - " + memberStatusName + " - " + withdrawalReasonText);
    }
}

リスト13：基本検索のSQL

select MEMBER.MEMBER_ID
     , MEMBER.MEMBER_NAME
     , MEMBER.MEMBER_ACCOUNT
     , MEMBER.MEMBER_STATUS_CODE
     , MEMBER.MEMBER_BIRTHDAY
     , MEMBER.VERSION_NO
     , MEMBER.MEMBER_FORMALIZED_DATETIME
     , MEMBER.REGISTER_DATETIME
     , MEMBER.REGISTER_USER
     , MEMBER.REGISTER_PROCESS
     , MEMBER.UPDATE_DATETIME
     , MEMBER.UPDATE_USER
     , MEMBER.UPDATE_PROCESS
     , dbfluteRelno_0.MEMBER_STATUS_CODE AS MEMBER_STATUS_CODE_0
     , dbfluteRelno_0.MEMBER_STATUS_NAME AS MEMBER_STATUS_NAME_0
     , dbfluteRelno_0.DISPLAY_ORDER AS DISPLAY_ORDER_0
     , dbfluteRelno_2.MEMBER_ID AS MEMBER_ID_2
     , dbfluteRelno_2.VERSION_NO AS VERSION_NO_2
     , dbfluteRelno_2.REGISTER_DATETIME AS REGISTER_DATETIME_2
     , dbfluteRelno_2.REGISTER_USER AS REGISTER_USER_2
     , dbfluteRelno_2.REGISTER_PROCESS AS REGISTER_PROCESS_2
     , dbfluteRelno_2.UPDATE_DATETIME AS UPDATE_DATETIME_2
     , dbfluteRelno_2.UPDATE_USER AS UPDATE_USER_2
     , dbfluteRelno_2.UPDATE_PROCESS AS UPDATE_PROCESS_2
     , dbfluteRelno_2.WITHDRAWAL_REASON_CODE AS WITHDRAWAL_REASON_CODE_2
     , dbfluteRelno_2.WITHDRAWAL_REASON_INPUT_TEXT AS WITHDRAWAL_REASON_INPUT_TEXT_2
     , dbfluteRelno_2.WITHDRAWAL_DATETIME AS WITHDRAWAL_DATETIME_2
     , dbfluteRelno_2_1_n2.WITHDRAWAL_REASON_TEXT AS WITHDRAWAL_REASON_TEXT_2_1
     , dbfluteRelno_2_1_n2.DISPLAY_ORDER AS DISPLAY_ORDER_2_1
     , dbfluteRelno_2_1_n2.WITHDRAWAL_REASON_CODE AS WITHDRAWAL_REASON_CODE_2_1
  from MEMBER
    left outer join MEMBER_STATUS dbfluteRelno_0
      on MEMBER.MEMBER_STATUS_CODE = dbfluteRelno_0.MEMBER_STATUS_CODE
    left outer join MEMBER_WITHDRAWAL dbfluteRelno_2
      on MEMBER.MEMBER_ID = dbfluteRelno_2.MEMBER_ID
    left outer join WITHDRAWAL_REASON dbfluteRelno_2_1_n2
      on dbfluteRelno_2.WITHDRAWAL_REASON_CODE = dbfluteRelno_2_1_n2.WITHDRAWAL_REASON_CODE
 where MEMBER.MEMBER_ACCOUNT like 'M%'
 order by dbfluteRelno_0.DISPLAY_ORDER asc, MEMBER.MEMBER_BIRTHDAY desc, MEMBER.MEMBER_ID asc

次回

これにてConditionBeanの基本は一通り抑えました。実を言うと応用がたくさん存在するのですが、それらはまた後で説明させて頂きます。

次回は、いざというときに大活躍する「外だしSQL（OutsideSql⁠）⁠」をみていきましょう！

編注）: 次回は、2008年2月の公開予定です。