DBFlute現場指向O/Rマッパ

外だしSQLのページング検索

外だしSQLでもページング検索が可能です。

前提

ページング検索とは？

ここで言うページング検索とは、以下の３つの処理を行うことを前提とします。
「ページングなし件数取得」・「ページング実データ検索」・「ページング結果計算処理」
詳しくはこちら

「ページング実データ検索のみ」のページング検索は、上記で定義したページング検索に包含されるものとして文中で補足を入れるにとどめています。

ページング検索の２つの手法

さらに、外だしSQLのページング検索では「自動ページング(autoPaging)」・「手動ページング(manualPaging)」という２つの手法が存在します。

1. 自動ページング(autoPaging) ： ResultSetのすっ飛ばしを利用するページング
→ 開発者はSQLでページング絞りの条件を記述する必要はない。
→ ページング絞りの条件は、DBによって記述方法が異なるが、この方法ではそれを意識する必要はない。
2. 手動ページング(manualPaging) ： 開発者がSQL内に明示的にページング絞りの条件を記述するページング
→ 開発者はSQLでページング絞りの条件を記述する必要がある。
→ 厳密なパフォーマンスを考慮したい場合は、こちらの手法が良い。

バグ報告

自動ページング(autoPaging)に関して、DBFlute-0.5.8にてバグが存在しておりDBの種類によっては利用できません。
DBFlute-0.5.9にて解決され、正常に動作しております。

実装概要

ページング検索のSQLを作成し、autoPaging()/manualPaging()から「selectPage()」を実行します。
引数のParameterBeanは、PagingBeanインターフェースの実装クラスである必要があります。

ex) 自動ページング(autoPaging)
PagingResultBean<UnpaidSummaryMember> page = memberBhv.outsideSql().autoPaging().selectPage(path, pmb, entityType);
		

実装の流れは以下の通りです：

• 1. SQLファイルを作成する。
• 2. ページング検索SQLを2Way-SQLで実装する。
• 3. Sql2Entityを実行する。
• 4. selectPage()メソッドを利用して実行する。

実装手順

1. 1. SQLファイルを作成する。

   このステップは、外だしSQLの基本で書いてあることと特に変わりはありません。
   

2. 2. ページング検索SQLを2Way-SQLで実装する。

   この２つの処理は１つのSQLでIFコメントを利用して実現します。
   
   ポイントは以下の通りです：

   • ParameterBeanの宣言に「 extends SPB」を付与する。

   → ParameterBeanがSimplePagingBeanを継承するようになる。
   → これにより、ParameterBeanで「実データ検索なのか件数取得なのか(pmb.isPaging())」が判定可能になる。

   
   -- !UnpaidSummaryMemberPmb extends SPB!
   -- !!Integer memberId!!
   -- !!...!!
   					

   • Select句を「/*IF pmb.isPaging()*/」で囲って「-- ELSE select count(*)」を付与する。

   → 「カラム列挙」と「count(*)」とを切り替えることが可能になる。
   → 「ページング実データ検索のみ」の場合は不要。
   → C#版の場合は「/*IF pmb.IsPaging*/」となるので注意。

   
   /*IF pmb.isPaging()*/
   select member.MEMBER_ID
        , member.MEMBER_NAME
        , ...
   -- ELSE select count(*)
   /*END*/
   					

   • ページング絞りの条件を記述し、「/*IF pmb.isPaging()*/」で囲う。

   → これは手動ページング(manualPaging)の場合のみ必要。
   → 「/*$pmb.fetchSize*/」で取得するレコードの件数を指定可能。
   → 「/*$pmb.pageStartIndex*/」で検索対象ページの開始を示すインデックス(0オリジン)を指定可能。
   → 「/*$pmb.pageEndIndex*/」で検索対象ページの終了を示すインデックス(0オリジン)を指定可能。
   → ページング絞りの条件の記述方法はDBによって異なるので注意(バインド変数が利用できるか否かも含む)。
   → 「ページング実データ検索のみ」の場合は「/*IF pmb.isPaging()*/」での囲い込みが不要。
   → C#版の場合は「/*$pmb.FetchSize*/」・「/*$pmb.PageStartIndex*/」・「/*$pmb.PageEndIndex*/」と先頭が大文字になるので注意。

   ex) H2
    /*IF pmb.isPaging()*/
    limit /*$pmb.fetchSize*/20, offset /*$pmb.pageStartIndex*/80
    /*END*/
   					

   ex) MySQL
    /*IF pmb.isPaging()*/
    limit /*$pmb.pageStartIndex*/80, /*$pmb.fetchSize*/20
    /*END*/
   					

   ex) Oracle
   select *
     from (
   select base.*, rownum as rn
     from (
   
       select ... from ... where ... order by ..
   
          ) base
          )
    where rn > /*$pmb.pageStartIndex*/80
      and rn <= /*$pmb.pageEndIndex*/100
   					

   • 「ページングなし件数取得」で不要な結合やソート指定「/*IF pmb.isPaging()*/」で囲う。

   → これはパフォーマンス考慮のため。 → 「ページング実データ検索のみ」の場合は不要。

   
    /*IF pmb.isPaging()*/
    order by UNPAID_PRICE_SUMMARY desc, member.MEMBER_ID asc
    /*END*/
   					

   上記のポイントを実装した例題のSQLが以下となります。
   

   ex) ページング検索SQLの実装（自動ページング）
   			
   -- #UnpaidSummaryMember#
   
   -- !UnpaidSummaryMemberPmb extends SPB!
   -- !!Integer memberId!!
   -- !!String memberName!!
   -- !!String memberStatusCode!!
   -- !!boolean unpaidMemberOnly!!
   
   /*IF pmb.isPaging()*/
   select member.MEMBER_ID
        , member.MEMBER_NAME
        , (select sum(purchase.PURCHASE_PRICE)
             from PURCHASE purchase
            where purchase.MEMBER_ID = member.MEMBER_ID
              and purchase.PAYMENT_COMPLETE_FLG = 0
          ) as UNPAID_PRICE_SUMMARY
        , memberStatus.MEMBER_STATUS_NAME
   -- ELSE select count(*)
   /*END*/
     from MEMBER member
       /*IF pmb.isPaging()*/
       left outer join MEMBER_STATUS memberStatus
         on member.MEMBER_STATUS_CODE = memberStatus.MEMBER_STATUS_CODE
       /*END*/
    /*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*/
      /*IF pmb.memberStatusCode != null*/and member.MEMBER_STATUS_CODE = /*pmb.memberStatusCode*/'FML'/*END*/
      /*IF pmb.unpaidMemberOnly*/
      and exists (select 'yes'
                    from PURCHASE purchase
                   where purchase.MEMBER_ID = member.MEMBER_ID
                     and purchase.PAYMENT_COMPLETE_FLG = 0
          )
      /*END*/
    /*END*/
    /*IF pmb.isPaging()*/
    order by UNPAID_PRICE_SUMMARY desc, member.MEMBER_ID asc
    /*END*/
   			
   			

3. 3. Sql2Entityを実行する。

   このステップは、外だしSQLの基本で書いてあることと特に変わりはありません。
   

   Sql2Entityに関してはこちら

4. 4. selectPage()メソッドを利用して実行する。

   BehaviorのoutsideSql()メソッドを利用しながらも、ページング条件の指定やページング結果計算処理(PagingResultBean)は、ConditionBeanを利用したページング検索のときと同じです。
   PagingResultBeanについてはこちら
   
   ポイントは以下の通りです：

   • BehaviorのoutsideSql()メソッドの後、「autoPaging()」・「manualPaging()」メソッドのどちらかを呼び出す。

   → 自動ページングか手動ページングかを選択する。

   • 「autoPaging()」・「manualPaging()」メソッドの後、selectPage()を呼び出す。

   → 戻り値がPagingResultBeanで、ParameterBeanの引数の型がPagingBeanインターフェース。
   → 「ページング実データ検索のみ」の場合は代わりにselectList()を利用。

   ex) 自動ページング(autoPaging)
   PagingResultBean<UnpaidSummaryMember> page = memberBhv.outsideSql().autoPaging().selectPage(path, pmb, entityType);
   					

   ex) 手動ページング(manualPaging)
   PagingResultBean<UnpaidSummaryMember> page = memberBhv.outsideSql().manualPaging().selectPage(path, pmb, entityType);
   					

   • ParameterBeanの「fetchFirst()」・「fetchPage()」メソッドを利用してページング情報を指定する。

   → SimplePagingBeanを継承していればこのメソッドが利用可能になる。
   → メソッド仕様はConditionBeanの場合と全く同じである。

   
   UnpaidSummaryMemberPmb pmb = new UnpaidSummaryMemberPmb();
   pmb.paging(20, 3);
   
   // paging()メソッドはDBFlute-0.7.3よりサポート。
   // DBFlute-0.7.2までは以下の通り：
   // pmb.fetchFirst(20);
   // pmb.fetchPage(3);
   					

   上記のポイントを実装した例題のテスト実装が以下となります。
   

   ex) 自動ページング検索のテスト実装｛条件：正式会員であること｝
   			
   public void test_outsideSql_select_autoPaging_selectPage_Tx() throws Exception {
       // ## Arrange ##
       // SQLのパス
       String path = MemberBhv.PATH_selectUnpaidSummaryMember;
   
       // 検索条件
       final UnpaidSummaryMemberPmb pmb = new UnpaidSummaryMemberPmb();
       pmb.setMemberStatusCode(ClassificationDefinition.CODE_MemberStatus_Formalized);
   
       // 戻り値Entityの型
       Class<UnpaidSummaryMember> entityType = UnpaidSummaryMember.class;
   
       // ## Act ##
       int pageSize = 2;// ページサイズ「２」
       pmb.paging(pageSize, 1);// １ページ目
       PagingResultBean<UnpaidSummaryMember> page1 = memberBhv.outsideSql().autoPaging().selectPage(path, pmb, entityType);
   
       pmb.paging(pageSize, 2);// ２ページ目
       PagingResultBean<UnpaidSummaryMember> page2 = memberBhv.outsideSql().autoPaging().selectPage(path, pmb, entityType);
   
       pmb.paging(pageSize, 3);// ３ページ目
       PagingResultBean<UnpaidSummaryMember> page3 = memberBhv.outsideSql().autoPaging().selectPage(path, pmb, entityType);
   
       pmb.paging(pageSize, page1.getAllPageCount());// 最後のページ
       PagingResultBean<UnpaidSummaryMember> lastPage = memberBhv.outsideSql().autoPaging().selectPage(path, pmb, entityType);
   
       // ## Assert ##
       showPage(page1, page2, page3, lastPage);
       assertEquals(2, page1.size());
       assertEquals(2, page2.size());
       assertEquals(2, page3.size());
       assertNotSame(page1.get(0).getMemberId(), page2.get(0).getMemberId());
       assertNotSame(page2.get(0).getMemberId(), page3.get(0).getMemberId());
       assertEquals(1, page1.getCurrentPageNumber());
       assertEquals(2, page2.getCurrentPageNumber());
       assertEquals(3, page3.getCurrentPageNumber());
       assertEquals(page1.getAllRecordCount(), page2.getAllRecordCount());
       assertEquals(page2.getAllRecordCount(), page3.getAllRecordCount());
       assertEquals(page1.getAllPageCount(), page2.getAllPageCount());
       assertEquals(page2.getAllPageCount(), page3.getAllPageCount());
       assertFalse(page1.isExistPrePage());
       assertTrue(page1.isExistNextPage());
       assertTrue(lastPage.isExistPrePage());
       assertFalse(lastPage.isExistNextPage());
   }
   			
   			

   ex) 実行ログ｛条件：正式会員であること｝
   			
   (S2DaoInterceptor#traceMethod():201) - /===============================================================================
   (S2DaoInterceptor#traceMethod():202) -                                                       OutsideSqlDao.selectList()
   (S2DaoInterceptor#traceMethod():203) -                                                       =========================/
   (S2DaoInterceptor#traceMethod():211) - OutsideSql: sql/member/selectUnpaidSummaryMember.sql
   (Logger#debug():105) - -- #UnpaidSummaryMember#
   
   -- !UnpaidSummaryMemberPmb extends SPB!
   -- !!Integer memberId!!
   -- !!String memberName!!
   -- !!String memberStatusCode!!
   -- !!boolean unpaidMemberOnly!!
   
   select count(*)
   
     from MEMBER member
       
    where
      
      
      member.MEMBER_STATUS_CODE = 'FML'
      
    
    
   (S2DaoInterceptor#traceReturn():477) - ===========/ [00m00s000ms - Selected count: 13]
   (S2DaoInterceptor#traceReturn():496) - 
   (S2DaoInterceptor#traceMethod():201) - /===============================================================================
   (S2DaoInterceptor#traceMethod():202) -                                                       OutsideSqlDao.selectList()
   (S2DaoInterceptor#traceMethod():203) -                                                       =========================/
   (S2DaoInterceptor#traceMethod():211) - OutsideSql: sql/member/selectUnpaidSummaryMember.sql
   (Logger#debug():105) - -- #UnpaidSummaryMember#
   
   -- !UnpaidSummaryMemberPmb extends SPB!
   -- !!Integer memberId!!
   -- !!String memberName!!
   -- !!String memberStatusCode!!
   -- !!boolean unpaidMemberOnly!!
   
   
   select member.MEMBER_ID
        , member.MEMBER_NAME
        , (select sum(purchase.PURCHASE_PRICE)
             from PURCHASE purchase
            where purchase.MEMBER_ID = member.MEMBER_ID
              and purchase.PAYMENT_COMPLETE_FLG = 0
          ) as UNPAID_PRICE_SUMMARY
        , 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_STATUS_CODE = 'FML'
      
    
    
    order by UNPAID_PRICE_SUMMARY desc, member.MEMBER_ID asc
    
   (S2DaoInterceptor#traceReturn():479) - ===========/ [00m00s016ms - Selected list: 1 first={20,諸葛瑾子瑜,null,正式会員}]
   (S2DaoInterceptor#traceReturn():496) - 
   (AppTestBase#log():69) - 1/7 of 13 listSize=2 pageSize=2 page:{false/true} groupSize=0 rangeSize=0
   (AppTestBase#log():69) - 2/7 of 13 listSize=2 pageSize=2 page:{true/true} groupSize=0 rangeSize=0
   (AppTestBase#log():69) - 3/7 of 13 listSize=2 pageSize=2 page:{true/true} groupSize=0 rangeSize=0
   (AppTestBase#log():69) - 7/7 of 13 listSize=1 pageSize=2 page:{true/false} groupSize=0 rangeSize=0
   (AppTestBase#log():69) - [page1]
   (AppTestBase#log():69) -   {4,スタンコビッチ,5100,正式会員}
   (AppTestBase#log():69) -   {19,ミルコ,2000,正式会員}
   (AppTestBase#log():69) - [page2]
   (AppTestBase#log():69) -   {1,ストイコビッチ,null,正式会員}
   (AppTestBase#log():69) -   {5,ミロシェビッチ,null,正式会員}
   (AppTestBase#log():69) - [page3]
   (AppTestBase#log():69) -   {7,オグニエノビッチ,null,正式会員}
   (AppTestBase#log():69) -   {8,ケズマン,null,正式会員}
   (AppTestBase#log():69) - [page4]
   (AppTestBase#log():69) -   {20,諸葛瑾子瑜,null,正式会員}
   			
   			

Document

• DBFlute Introduction
• Tutorial(for Dev)
• DBFlute Setup
• Template Setup
• EMecha Setup
• After Setup
• DBFlute Version-up
• ConditionBean
• ConditionBean Scope
• Reverse Dictionary
• OutsideSql
• About OutsideSql
• Parameter Comment
• Sql2Entity
• Entity Handling
• Cursor Handling
• Dynamic Binding
• Paging
• Like Search
• Behavior
• DB-Vendor Handling

Topics

• S2Dao Comparing
• To C# Users

Various

• SqlLogRegistry