gihyo.jpサイトのロゴ

DBアクセスを定番化しよう DBFlute入門

第6回外だしSQLの様々な機能

2008-04-23

はじめに

前回は、外だしSQL（OutsideSql）の基本を見ていきました。今回は、様々な状況に合わせた機能をみていきましょう。

一件検索

前回は、リスト検索のみを対象としていました。実際の業務は、外だしSQLを利用する場合のほとんどがリスト検索になることが（経験上）多いため、ベースとなる検索をリスト検索と位置づけて説明しました。しかし、当然一件検索もしたいことがあります。

もう一度、前回利用したSQLのサンプルを思い出しましょう。

リスト1：外だしSQLのサンプル（SQLファイル）
-- #MemberWithMaxPurchasePrice#

-- !MemberWithMaxPurchasePricePmb!
-- !!Integer memberId!!
-- !!String memberName!!

-- 会員IDと名称の前方一致で絞り込んで、会員一覧と購入最大価格を検索。
-- 絞り込み条件はそれぞれ値がnullじゃければ評価する。
select member.MEMBER_ID
     , member.MEMBER_NAME
     , (select max(purchase.PURCHASE_PRICE)
          from PURCHASE purchase
         where purchase.MEMBER_ID = member.MEMBER_ID) as MAX_PURCHASE_PRICE
  from MEMBER member
 /*BEGIN*/where
   /*IF pmb.memberId != null*/member.MEMBER_ID = /*pmb.memberId*/3/*END*/
   /*IF pmb.memberName != null*/and member.MEMBER_NAME like /*pmb.memberName*/'ス' || '%'/*END*/
 /*END*/

そして、リスト検索の場合の呼び出しです。

リスト2：外だしSQLのリスト検索
/**
 * 会員一覧と最大購入価格を検索。
 * 会員名称が「ス」で始まる会員を対象に検索する。
 *
 * @throws Exception
 */
public void test_OutsideSql_selectList_BasicExecution_Tx() throws Exception {
    // ## Arrange ##
    final String path = "sql/member/selectMemberWithMaxPurchasePrice.sql";
    final MemberWithMaxPurchasePricePmb pmb = new MemberWithMaxPurchasePricePmb();
    pmb.setMemberName("ス");
    final Class entityType = MemberWithMaxPurchasePrice.class;

    // ## Act ##
    final List memberList
            = memberBhv.outsideSql().selectList(path, pmb, entityType);

    // ## Assert ##
    for (MemberWithMaxPurchasePrice member : memberList) {
        final String memberName = member.getMemberName();
        final Integer maxPurchasePrice = member.getMaxPurchasePrice();
        log.debug(memberName + " - " + maxPurchasePrice);
        assertTrue(memberName.startsWith("ス"));
    }
}

BehaviorのoutsideSql()からselecList()を呼んでいますね。一件検索をしたいときは、outsideSql()に続いてentityHandling()を呼び出し、selectEntity()を実行します。

リスト3：外だしSQLの一件検索
/**
 * 会員と最大購入価格を一件検索。
 * 会員IDが6の会員を対象に検索する。
 *
 * @throws Exception
 */
public void test_OutsideSql_selectEntity_BasicExecution_Tx() throws Exception {
    // ## Arrange ##
    final Integer expectedMemberId = 6;
    final String path = "sql/member/selectMemberWithMaxPurchasePrice.sql";
    final MemberWithMaxPurchasePricePmb pmb = new MemberWithMaxPurchasePricePmb();
    pmb.setMemberId(expectedMemberId);
    final Class entityType = MemberWithMaxPurchasePrice.class;

    // ## Act ##
    final MemberWithMaxPurchasePrice member
            = memberBhv.outsideSql().entityHandling().selectEntity(path, pmb, entityType);

    // ## Assert ##
    final String memberName = member.getMemberName();
    final Integer maxPurchasePrice = member.getMaxPurchasePrice();
    log.debug(memberName + " - " + maxPurchasePrice);
    assertEquals(expectedMemberId, member.getMemberId());
}

このselectEntity()は、検索結果が0件の時はnullを戻します。ここはConditionBeanのときの一件検索と同じで、nullだったら例外になってほしい場合は、selectEntityWithDeletedCheck()を使います。

リスト4：外だしSQLのチェック付き一件検索
 /**
  * 会員と最大購入価格をチェック付きの一件検索。
  * 存在しない会員IDで検索してチェックに引っかかり例外になる。
  *
  * @throws Exception
  */
 public void test_OutsideSql_selectEntityWithDeletedCheck_BasicExecution_Tx() throws Exception {
     // ## Arrange ##
     final Integer expectedMemberId = 99999;// No Exist
     final String path = "sql/member/selectMemberWithMaxPurchasePrice.sql";
     final MemberWithMaxPurchasePricePmb pmb = new MemberWithMaxPurchasePricePmb();
     pmb.setMemberId(expectedMemberId);
     final Class entityType = MemberWithMaxPurchasePrice.class;
     
     // ## Act & Assert ##
     try {
         memberBhv.outsideSql().entityHandling().selectEntityWithDeletedCheck(path, pmb, entityType);
         fail();
     } catch (EntityAlreadyDeletedException e) {
         // OK
         log.debug(e.getMessage());
     }
 }

カーソル検索

検索結果をカーソル（ResultSet）で扱うことのできるメソッドが存在します。

大量件数を扱う場合は、検索結果を一気にメモリ上に展開するとメモリ不足になることがあります。カーソル（ResultSet/DataReader）経由で1件ずつ処理をすることでその問題をそれを解決します。一番わかりやすい例としては、「⁠検索結果をそのままCSVファイルに出力する」というのが考えられるでしょう。

ResultSetと聞いてちょっといやな思いをする方もいらっしゃるでしょう。JDBCバリバリで実装してたころにResultSetの使いづらいインターフェースに悩まされた記憶がよみがえりませんでしょうか？　そして、「⁠結局O/Rマッパを使っているのにResultSet直触りするのかぁ」と思いそうなのですが、自動生成を武器にしているDBFluteはそんなことはしません。

外だしSQLのSql2Entityの宣言のところにオプションを示す一行を追加します。「⁠--」に続けて「+cursor+」と記述します。

リスト5：Sql2Entityでカーソル指定（SQLファイル）
-- #MemberWithMaxPurchasePrice#
-- +cursor+

-- !MemberWithMaxPurchasePricePmb!
-- !!Integer memberId!!
-- !!String memberName!!

-- 会員IDと名称の前方一致で絞り込んで、会員一覧と購入最大価格を検索。
-- 絞り込み条件はそれぞれ値がnullじゃければ評価する。
select member.MEMBER_ID
     , member.MEMBER_NAME
...(以下変わらず)

これでSql2Entityを実行します。

すると、前回生成したような戻り値Entityではなく、「⁠exdao.cursor」配下にCursorを扱うクラスが生成されます。実際に使ってみましょう。

リスト5：Sql2Entityでカーソル指定（SQLファイル）
/**
 * 会員一覧と最大購入価格をカーソル検索。
 * 会員名称が「ス」で始まる会員を検索する。
 *
 * @throws Exception
 */
public void test_OutsideSql_selectCursor_BasicExecution_Tx() throws Exception {
    // ## Arrange ##
    final String path = "sql/member/selectMemberWithMaxPurchasePrice.sql";
    final MemberWithMaxPurchasePricePmb pmb = new MemberWithMaxPurchasePricePmb();
    pmb.setMemberName("ス");
    final MemberWithMaxPurchasePriceCursorHandler handler = new MemberWithMaxPurchasePriceCursorHandler() {
       @Override
       protected Object fetchCursor(MemberWithMaxPurchasePriceCursor cursor) throws SQLException {
           while (cursor.next()) {
               final Integer memberId = cursor.getMemberId();
               final String memberName = cursor.getMemberName();
               final Integer maxPurchasePrice = cursor.getMaxPurchasePrice();
               log.debug(memberId + " - " + memberName + " - " + maxPurchasePrice);
               assertTrue(memberName.startsWith("ス"));
           }
           return null;
       }
    };

    // ## Act & Assert ##
    memberBhv.outsideSql().cursorHandling().selectCursor(path, pmb, handler);
}

コールバックのハンドラを実装してメソッドに渡しています。メソッドは、outsideSql()に続いてcursorHandling()を呼び出した後のselectCursor()です。ここではResultSetは登場していませんが、1件ずつメモリ上に展開しての処理がされています。

そして、オーバーライドしたメソッドの引数から「タイプセーフ」にカラムの値が取得できています。

DBFluteの発想としては、「⁠Sql2EntityでSelect句の構成がわかるのだから、文字列とか番号インデックスなんかで指定するのではなく、タイプセーフにアクセスできるようにしてしまおう」というものです。実際にはResultSetをラップしているだけではありますが、直接ResultSetを扱うときに比べてはるかに「安全な実装」が可能になっています。

また、コールバック形式の処理なので、カーソル（ResultSet）のClose処理を気にする必要はありません。このコールバックを呼び出すフレームワーク内で解決しますので、開発者はただループを回してタイプセーフに値を取って本来一番大事な業務処理の実装に集中することができます。

アプリケーションで1、2箇所はこういった大量件数で1件ずつ処理したいことが少なからずあります。このDBFluteのカーソル検索ならいざそういった場面が来ても安心して実装可能です。

まとめ

外だしSQLでの細かい、かつ、よくある場面に対する対応をみてきました。

DBFluteは、外だしSQLを重要視しています。ConditionBeanでできない複雑なSQLを実装するのは非常に大変な作業ですから、極力業務処理とは無関係な余計なところで悩みたくないものです。できる限り安全な実装ができるような支援を心がけています。

次回は、ConditionBean、外だしSQL双方にまたがる応用編「ページング⁠」⁠、「⁠エスケープ付き曖昧検索」を見ていきましょう。

おすすめ記事

記事・ニュース一覧