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

第8回応用編「ページング検索」

はじめに

今回は、応用編第2回としてConditionBean/OutsideSql(外だしSQL)両方における「ページング検索」を見て行きます。

ページング検索

ページング検索とは?

まずは基本的な概念から説明します。 ここで言うページング検索とは、以下のようなページングナビゲーションを 付与する検索画面の検索処理を前提としてます。

図1 ページングナビゲーションの例
図1 ページングナビゲーションの例

そして、これら要素満たすために必要な処理は以下の3つの処理です。

リスト1 ページング処理の基本
1. ページングなし件数取得
    → ページングをしなかった場合の件数を取得し、総ページ数などの導出要素とする。
    → ページング検索画面のページングナビゲーションの実装において必須の処理となる。

2. ページング実データ検索
    → ページング絞りの条件で絞られた取得したい実データのリスト検索。

3. ページング結果計算処理
    → ページングナビゲーションンで利用するページング結果の計算処理。(総ページ数の導出や次ページ有無判定など)
    → ページング検索画面のページングナビゲーションの実装において必須の処理となる。

ポイントは、⁠ページングなし件数取得」です。 これをやらないと総ページ数などの計算ができません。 この処理のSQLは、⁠ページング実データ検索」と同じ条件で、 ページングの絞込みだけがないものになります。

仕組みによってはここでWhere句の条件が二箇所にわたる冗長管理に なってしまったりと、開発者の頭を悩ませるきっかけになったりします。

ConditionBeanによるページング検索

では、さっそくConditionBeanにおけるページング検索をみてみましょう。

普通の検索

まずは普通の検索を見て下さい。

リスト2 ConditionBeanによる普通の検索
// 取得:会員スタータスを結合して取得
// 条件:会員アカウントが'S'で始まること
// 並び:会員IDの昇順
MemberCB cb = new MemberCB();
cb.setupSelect_MemberStatus();
cb.query().setMemberAccount_PrefixSearch("S");
cb.query().addOrderBy_MemberId_Asc();

List<Member> memberList = memberBhv.selectList(cb);

for (Member member : memberList) {
    ...
}

ページング検索

そして、ページング検索をするための条件を付与してみます。 必要なのは「1ページは何件か?(ページサイズ)」「何ページ目を検索したいか?(検索対象ページ番号)」の情報です。

ConditionBeanによるページング検索は以下のようになります。

リスト3 ConditionBeanによるページング検索
// 取得:会員スタータスを結合して取得
// 条件:会員アカウントが'S'で始まること
// 並び:会員IDの昇順
MemberCB cb = new MemberCB();
cb.setupSelect_MemberStatus();
cb.query().setMemberAccount_PrefixSearch("S");
cb.query().addOrderBy_MemberId_Asc();

// 1ページ20件で3ページ目を検索
cb.paging(20, 3);// 

// ページング検索
//   1. ページングなし件数取得
//   2. ページング実データ検索
//   3. ページング結果計算処理
PagingResultBean<Member> memberPage = memberBhv.selectPage(cb); 

for (Member member : memberPage) {
    ...
}

paging()にて「1ページは何件か?(ページサイズ)」⁠何ページ目を検索したいか?(検索対象ページ番号)」を指定しています。 そして、selectList()の代わりにselectPage()を利用しています。 戻り値はPagingResultBeanというクラスです。

実は、このselectPage()とPagingResultBeanで、先述の「ページング処理の基本」が 全て完了しています。計算結果を取得してみましょう。

リスト4 ConditionBeanによるページング検索の結果
PagingResultBean<Member> memberPage = memberBhv.selectPage(cb); 

// 3ページ目(41件目から60件目)のリスト
for (Member member : memberPage) {
    ...
}

// ページングなし条件での総件数
int allRecordCount = memberPage.getAllRecordCount();

// 総ページ数
int allPageCount = memberPage.getAllPageCount();

// 対象ページ番号
int currentPageNumber = memberPage.getCurrentPageNumber();

// 前ページがあるか否か
boolean isExistPrePage = memberPage.isExistPrePage();

// 次ページがあるか否か
boolean isExistNextPage = memberPage.isExistNextPage();

// ナビゲーションのページリンク候補一覧
memberPage.setPageRangeSize(5);
List<Integer> pageNumberList = memberPage.pageRange().createPageNumberList();

// ...その他省略

「総ページ数」「前ページがあるか否」などがPagingResultBeanから取得できます。 先ほどの「ページングナビゲーションの例」に照らし合わせて見ましょう。

図2 ページングナビゲーションとページング結果
図2 ページングナビゲーションとページング結果

ページングナビゲーションで必要な情報の計算処理はDBFluteが行います。 そのため、ここでよくありがちな「1ページ多い」とか ⁠次のページないのにあるように扱ってる」などの計算ミスは発生しません。 このようにDBFluteは、ページング処理をできるだけ安全に実装できるように工夫をしています。

ページング絞込みのSQL

ページングの絞込みは、SQLレベルで行います。 Oracleであれば「rownum⁠⁠、PostgreSQLであれば「limit/offset」など できる限りデータベースの機能を使って絞り込みます。 もし、サポートしていないデータベースに関しては、ResultSetの ループ処理にて絞込みをします。

関連補足:最初のn件を取得

「最初のn件を取得する」という検索専用のメソッドも用意されています。

リスト5 fetchFirst()を使った検索
// 取得:会員スタータスを結合して取得
// 条件:会員アカウントが'S'で始まること
// 並び:会員IDの昇順
MemberCB cb = new MemberCB();
cb.setupSelect_MemberStatus();
cb.query().setMemberAccount_PrefixSearch("S");
cb.query().addOrderBy_MemberId_Asc();

// 最初の20件
cb.fetchFirst(20); 

// 検索結果の最初の20件だけを取得
List<Member> memberList = memberBhv.selectList(cb); 

for (Member member : memberList) {
    ...
}

OutsideSql(外だしSQL)によるページング検索

それでは、OutsideSql(外だしSQL)によるページング検索です。

Auto/Manual (ConditionBeanとの違い)

ConditionBeanとは違う大きなポイントをまず説明します。 こちらの場合は、SQL自体は手動で作成するため、ページング絞りのSQLは 自分で実装することになります(rownumやlimit/offsetなど)。 しかし、そのSQLを自分で書くのは結構敷居の高い作業でもあります。 DBFluteでは以下の二通りのやり方を提供しています。

  • AutoPaging : ResultSetレベルでの自動絞り込み
  • ManualPaging : SQLレベルでの手動絞込み(自分で書く)

例えば、Oracleのrownumでの絞り込みは記述が大変で間違えやすいものです。 それならば、本当にパフォーマンスを厳密に意識したい場合にだけ「ManualPaging」で、 そうでない場合は「AutoPaging」でサクっと実装してしまう、などという オプション的なやり方が考えられます。

「ResultSetレベルでの自動絞り込み」も利用可能なDBの場合は、 ⁠空回し」ではなく「カーソルによるすっ飛ばし(ポインタずらし)」で 実現するため、大きなパフォーマンス劣化に結びつく可能性は そこまで大きくないと思われます。

普通の検索

まずは、普通の検索を見てみます。

リスト6 OutsideSql(外だしSQL)の普通の検索のSQL
-- #SimpleMember#

-- !SimpleMemberPmb!
-- !!Integer memberId!!
-- !!String memberName!!

select member.MEMBER_ID
     , member.MEMBER_NAME
     , memberStatus.MEMBER_STATUS_NAME
  from MEMBER member
        left outer join MEMBER_STATUS memberStatus
      on member.MEMBER_STATUS_CODE = memberStatus.MEMBER_STATUS_CODE
 /*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*/
 order by member.MEMBER_ID asc
リスト7 OutsideSql(外だしSQL)の普通の検索の実装
// SQLのパス
String path = MemberBhv.PATH_selectSimpleMember;

// 検索条件
SimpleMemberPmb pmb = new SimpleMemberPmb();
pmb.setMemberName("ス");

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

// ## Act ##
// SQL実行!
List<SimpleMember> memberList = memberBhv.outsideSql().selectList(path, pmb, entityType);

for (Member member : memberList) {
    ...
}

ページング検索 (AutoPaging)

そして、ページング検索です。ここではまず「AutoPaging」を使って説明します。 SQLは以下のようになります。

リスト8 OutsideSql(外だしSQL)によるページング検索のSQL(AutoPaging)
-- #SimpleMember#

-- !SimpleMemberPmb extends SPB!
-- !!Integer memberId!!
-- !!String memberName!!

/*IF pmb.isPaging()*/
select member.MEMBER_ID
     , member.MEMBER_NAME
     , 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*/
 /*END*/
 /*IF pmb.isPaging()*/
 order by member.MEMBER_ID asc
 /*END*/

普通の検索と違うところが二点あります。

1.
ParameterBeanの指定に「extends SPB」という宣言が追加されています。
これは生成するParameterBeanが「SimplePagingBean」というクラスを継承することを示し、 ページング絞りのための情報を扱うことのできるParameterBeanが生成されます。
2.
Select句、Join句、OrderBy句が「/*IF pmb.isPaging()*/」にて囲われています。
このif文は、⁠ページングなし件数取得」「ページング実データ検索」を区別しています。
DBFluteでは、ページングのSQLを「ページングなし件数取得」「ページング実データ検索」とで分ける必要はありません。このような形で共存させることによって、一番変わりやすいWhere句の条件を両方の処理にて再利用しています。
先述の「extends SPB」の宣言をすることによって、isPaging()が利用可能になります。

それでは実装を見てましょう。 やりたいことはConditionBeanと変わりません。 ⁠1ページは何件か?(ページサイズ)」「何ページ目を検索したいか?(検索対象ページ番号)⁠⁠ を指定して、PagingResultBeanを受け取ります。

リスト9 OutsideSql(外だしSQL)によるページング検索の実装(AutoPaging)
// SQLのパス
String path = "selectUnpaidSummaryMember";

// 検索条件
UnpaidSummaryMemberPmb pmb = new UnpaidSummaryMemberPmb();
pmb.setMemberStatusCode_Formalized();

// 戻り値Entityの型
Class<UnpaidSummaryMember> entityType = UnpaidSummaryMember.class;

// 1ページ20件で3ページ目を検索
pmb.paging(20, 3); 

// ## Act ##
// SQL実行!
PagingResultBean<SimpleMember> memberPage 
    = memberBhv.outsideSql().autoPaging().selectPage(path, pmb, entityType);

// 3ページ目(41件目から60件目)のリスト
for (Member member : memberPage) {
    ...
}

このように「ページングを扱えるParameterBean」を使って、 OutsideSqlでもselectPage()が利用できます。 ここではautoPaging()を呼び出しているので、ページング絞りは自動に行われます。

ページング検索 (ManualPaging)

おおよその流れはAutoPagingで学びました。 ManualPagingは、その流れの中で二点だけ違いがあります。 まずは、SQLに自分でページングの絞り込み条件を付与する必要があります。

リスト10 OutsideSql(外だしSQL)によるページング検索のSQL(ManualPaging)
   ...(略)

 /*IF pmb.isPaging()*/
 order by member.MEMBER_ID asc
 /*END*/
 /*IF pmb.isPaging()*/
 limit /*$pmb.fetchSize*/20, offset /*$pmb.pageStartIndex*/80
 /*END*/

「ページング実データ検索」の処理のときにページング絞りの条件が 有効になるように記述します。limit/offsetのサイズはParameterBeanで 計算されたものを利用します。Manualとは言っても、絞り込み条件の 計算処理はDBFluteが行うため、そこは安全に実装できます。 記述方法はDBによって異なります。 この場合は、H2データベースを利用しており、バインド変数ではなく ⁠埋め込み変数コメント」を利用しています。 例えば、Oracleだと以下のようになります。

リスト11 OutsideSql(外だしSQL)によるページング検索のSQL(ManualPaging)-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

そして、実装の方では、autoPaging()メソッドを呼び出しているところが、 manualPaging()に変わります。

リスト12 OutsideSql(外だしSQL)によるページング検索の実装(ManualPaging)
   ...(略)

// ## Act ##
// SQL実行!
PagingResultBean<SimpleMember> memberPage 
    = memberBhv.outsideSql().manualPaging().selectPage(path, pmb, entityType);

まとめ

ページング検索をみてみました。 こちらも実務で非常に役に立つ機能です。ぜひ利用してみて下さい。

さて、今回でこの連載はおしまいです。 最後に、DBFluteに触れてみる際にとても役に立つExampleを紹介します。

DBFluteBasicExample
SVN : https://www.seasar.org/svn/sandbox/dbflute/
Project : trunk/dbflute-basic-example

DBFluteの一番基本となるExampleでBehaviorやConditionBeanなどの 使い方を一つ一つ「JUnitの単体テスト」の形式で実装して紹介しています。 組み込みデータベース「H2」を利用しているため、SVNからチェックアウト するだけで、それらテストケースを実行して試すことが可能です。 環境的な面・実装的な面両方で参考になるExampleです。

DBFluteNBasicExample
SVN : https://www.seasar.org/svn/sandbox/dbflute/
Project : trunk/dbflute-nbasic-example

実はDBFluteはC#版も用意されています。 そのC#版DBFluteを使ったExampleです。 C#ユーザの方はぜひご覧になられて下さい。

DBFluteSpringExample
SVNhttps://www.seasar.org/svn/sandbox/dbflute/
Project : trunk/dbflute-spring-example

実はDBFluteはSpring Frameworkでも動作します。 SpringでDBアクセス周りに困っている方はぜひご覧になられて下さい。

それでは、おしまいです。 今までありがとうございました。

おすすめ記事

記事・ニュース一覧