ハンズオンセクション 9

概要

さて、ハンズオンの続きです。

(ひとつまえ) ハンズオンセクション 8

"はじめての外だしSQL" を学んでいきましょう。

事前準備

src/main/java 配下に org.docksidestage.handson.logic.HandsOn09Logic クラスを作成してください。この時点では空っぽで構いません。また、ERDを開いておくと良いでしょう。

MaihamaDB

【事務連絡】org.dbflute.handson から、org.docksidestage.handson に変わりました。 org.dbfluteで開始した人は、そのまま org.dbflute で続けてOKです。もし、移行するなら log4j.properties と basicInfoMap.dfprop の該当箇所を修正してください。

ハンズオンニュース - org.docksidestageに変更

外だしSQLについて

DBFluteにおける外だしSQLのコンセプトをもう一度おさらいしましょう。

バランス重視のDBアクセスI/F

そして、外だしSQLの概要を学んでいきましょう。

外だしSQL

初めての外だしSQL

まずは、外だしSQLの使い方です。

外だしSQLの使い方

それではエクササイズをやってみましょう。 SQLファイルは EMecha を使って作成し、CustomizeEntity や ParameterBean は、Sql2Entity で自動生成します。

外だしSQLのファイル
MemberBhv_selectOutsideMember.sql
  • 会員ID、会員名称、生年月日、会員ステータス名称、サービスポイント数を取得
  • 会員IDの等値、会員名称の前方一致、会員ステータスの等値を and で連結
  • それぞれ条件値がない場合は条件自体が無効になるように
  • 全ての条件値がない場合は全件検索になるように
ロジックのメソッド
List<OutsideMember> letsOutside(OutsideMemberPmb pmb)
  • 指定された ParameterBean で会員を検索する
  • 引数の値で null は許されない
対応テストメソッド
test_letsOutside_会員が検索されること()
  • 会員名称が "S" で始まる正式会員 (区分値メソッドを使う) で検索すること
  • 条件値を設定しなかった条件が除去されていることをログで目視確認
  • 会員サービスのサービスポイント数が取得できていることをアサート
  • 検索された会員が "S" で始まっていることをアサート
  • 正式会員であることをアサート (区分値メソッドを使う、select句にカラムを追加してもOK)
test_letsOutside_条件値なしで全件検索されること()
  • 全ての引数を null にして実行
  • where句が存在しないことをログで目視確認
  • 検索結果が全件であることをアサート

外だしSQLのドキュメント

Docタスクを叩いて、SchemaHTMLを作り直して外だしSQLの欄を見てみましょう。

2Way-SQLを活かして実行計画

2Way-SQLの特徴を活かして、作成した外だしSQLの実行計画を見てみましょう。 login-mysql-root.bat(sh) を実行してMySQLにroot接続して、use maihamadb; してから explain [SQL文字列] を流してみましょう。 

e.g. 外だしSQLのコメントを必須に設定 @Command
...$ explain select ... from ...;

実行計画の見方が変わらない場合は、片っ端からわからない単語などをググって、おおまかな流れを把握してみましょう。 カラムのインデックスの有無は、SchemaHTMLを参考に。また、検索結果が一件もヒットしないSQLの場合はそっけない実行計画になるので、会員IDの等値条件を抜いたりして試すと良いでしょう。

同じように、今までの ConditionBean の検索の中で、"ちょっと実行計画見てみたいなぁ" と思うようなものを二つ選んで、テストを実行してログに表示されたSQLをコピーして explain してみてください。ざらっと眺めて満足したら次のエクササイズへ。 (ここでは、インデックスによる検索の基本的な概念と、おおまかな実行計画の見方を掴むことさえできたらOKです)

外だしSQLでページングってみる

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

ConditionBeanでのページング検索を思い出して、比較しながら実装してみましょう。

DBアクセス - ページング検索
外だしSQLのファイル
MemberBhv_selectPartOfMember.sql
  • (Manual)ページング検索すること
  • 基本は "初めての外だしSQL" のSQLと同じで...
  • 会員ステータスの等値条件は要らない
  • 会員サービスのサービスポイント数の大なり条件を追加
  • 結合に関して、カウント検索のパフォーマンスを最大限考慮すること (会員サービスの結合とか)
  • そのとき、IFコメントに記述する条件が複雑にならないように (代理判定メソッドを使う)
ロジックのメソッド
PagingResultBean<PartOfMember> selectPartOfMember()
  • 会員名称が "S" で始まる会員を対象に検索
  • ページサイズは 4、ページ番号は 1 で検索すること
  • 指定された ParameterBean で検索して、検索結果を戻す
対応テストメソッド
test_selectPartOfMember_ページング検索されること()
  • 期待通りのSQLがログに出力されることを目視確認する
  • 検索したカラム全てのデータをログ出力
  • 検索結果がページサイズ以下のデータだけであることをアサート
※実装できたら、サービスポイント数が1以上という絞り込み条件を設定してみましょう。実行して落ちなければOKです。
※代理判定メソッドの UnitTest をやってみましょう。PartOfMemberPmb で (Eclipseなら) ctrl+9 でテストクラス作成(継承クラスはPlainTestCaseでOK)。 isPaging()をオーバーライドして固定でtrueやfalseを戻したりして、代理判定メソッドの判定パターンをぜんぶ検証してみましょう。

さらに外だし

それではさらに外だしSQLのエクササイズです。せっかくなので機能的なやり方でやりましょう。 以下は、ConditionBeanでは実現できないレベルのSQLです。頑張ってください。

外だしSQLのファイル
PurchaseBhv_selectPurchaseMonthSummary.sql
  • "会員と購入月" ごとの購入の平均購入価格、合計購入数量を検索する
  • 会員ID、会員名称、購入月、平均購入価格、合計購入数量という形で検索
  • "支払済みの購入だけ" という条件が指定できるように (逆に "未払いだけ" の条件は要らない)
  • 会員名称の曖昧検索(部分一致)という条件が指定ができるようにする
  • 会員のサービスポイント数の大なり条件が指定できるようにする
  • 会員IDの昇順、購入月の降順で並べる
ロジックのメソッド
ListResultBean<PurchaseMonthSummary> selectLetsSummary(String memberName)
  • 引数の値で null は許されない
  • 指定された ParameterBean で検索して、検索結果を戻す
対応テストメソッド
test_selectLetsSummary_集計が検索されること()
  • 会員名称に "vi" を含む会員を対象に検索
  • 会員名称に "vi" が含まれていることをアサート
  • 期待通りのSQLがログに出力されることを目視確認する
  • 検索したカラム全てのデータをログ出力

外だしSQLならでは

ConditionBeanと外だしSQLの概念的な違いを理解しましょう。 ちょうど、この外だしSQLは、"ConditionBeanでは実現できない、外だしSQLならではの検索" と言えます。

ConditionBeanのスコープ

お手軽チャレンジ

ちょっと、この集計のSQLをページング検索に修正してみてください。 ページサイズは 4 でページ番号は 1 で検索してみましょう。

ページング検索

そして、期待通りの総レコード数であることを目視確認してみてください。 (一時的に pageSize を 99999 にして実行した実データ検索の件数が総レコード数と言えます)

外だしSQLのコメントを必須に

外だしSQLのコメント(タイトルと説明)は必須になっています。 ※Java8 (DBFlute-1.1) よりデフォルトで必須です。

試しに、すでに作成した外だしSQLのどれかを、コメント削除して "OutsideSqlTestタスク" を実行してみてください。manage.sh(bat) の 25 番です。

"タイトルと説明のないSQLは作らないようにしよう" という思想です。 他人の作ったSQLは、非常にメンテナンスしづらいものです。 何のためのSQLか？どういう特徴を持っているSQLなのか？最低限の情報を残しておきたいものです。

現場フィット - 外だしSQLのタイトル

プロパティとカラムのコメントを付与

"初めての外だしSQL" のSQLで、ParameterBeanのプロパティにコメントを付与してみましょう。 (ParameterBeanのページを参考に)

ParameterBean - プロパティのコメント

"さらに外だし" のSQLで、検索カラム (CustomizeEntityのプロパティ) にコメントを付与してみましょう。 (CustomizeEntityのページを参考に)

CustomizeEntity - 検索カラムのコメント

再度 Sql2Entity を実行し、ParameterBean と CustomizeEntity のGetter/SetterのJavaコメントを確認してみましょう。

外だしSQLでカーソル検索ってみる

そして、"さらに外だし" で作成した外だしSQLと同じ仕様(ページングは不要)で、新たにカーソル検索のエクササイズをやってみましょう。 ConditionBeanでのカーソル検索を思い出して、比較しながら実装してみましょう。

そもそもカーソル検索とは？

このエクササイズは とっても難しい です。気を引き締めて臨んでください。

外だしSQLのファイル
PurchaseBhv_selectPurchaseMonthCursor.sql
  • "さらに外だし" と同じ仕様(ページングは不要)
  • カーソル検索として作成すること
  • 明らかにおかしいカラム名のカラムはデータ取得してはいけない (後は何を取ってもOKですが、これだけはっ)
ロジックのメソッド
void selectLetsCursor(PurchaseMonthCursorPmb pmb)
  • 指定された ParameterBean でカーソル検索する
  • 引数の値で null は許されない
  • 平均購入価格の分、その会員のサービスポイント数を足す
  • 足す際、パフォーマンス考慮のために事前selectはせず、updateだけで足す (varyingUpdat...)
  • ジョブスケジューラーからで呼ばれることを想定した処理として実装 (つまりバッチ処理として)
対応テストメソッド
test_selectLetsCursor_集計が検索されること()
  • 会員名称に "vi" を含む会員を対象に検索
  • 期待通りのSQLがログに出力されることを確認する
  • 誰か一人でもサービスポイント数が増えていることをアサート

ボーナスステージ

さて、MySQL (ハンズオンのデフォルト) を使っているのであれば、そのままでは実はメモリ対策になっていません。 DBFluteがカーソル検索をしていても、MySQLのJDBCドライバーが全件メモリ上に保持してしまいます(少なくとも、MySQL JDBC-5.1.xでは)。 ジョブスケジューラーからで呼ばれることを想定したバッチ処理であれば、検索対象が100万件になるかもしれませんので、このままだと本番環境で OutOfMemoryError が発生する可能性があります。

そこで、outsideSql().configure() メソッドを使って、StatementConfig の FetchSize に Integer.MIN_VALUE を設定して再実行してみましょう。

ただし、ここから壁が待っています。それに伴い、新しい要件です。

本番でもテストでも動く
本番環境でもUnitTest実行でも問題なく動作するようにしましょう。 ハンズオンでは、本番での実行をテストする仕組みはありませんが、論理的に問題なく実行されることを想定した実装をしましょう。
Interceptorを実行させない
Logicクラスにはたくさんの Interceptor (AOP) が仕掛けられているとします。 UnitTestの環境では実行しづらい処理が含まれていると想定して、UnitTestのときは実行されないようにしましょう。
テストデータは元に戻す
やむを得ずトランザクションをコミットせざるを得ないにしても、無理矢理でも良いのでUnitTestでデータが自動的に元に戻るようにしてみましょう。

ちなみに、ハンズオンの環境では、テストクラスにて新しいトランザクションを発行したり、トランザクション分離レベルを制御したりできます。 これらはスーパークラスのメソッドです。

新しいトランザクション発行
performNewTransaction()
READ COMMITTED にする
adjustTransactionIsolationLevel_ReadCommitted()

さて、どうしてもわからない場合は、ヒントが用意されています。見ると後悔するかもしれませんので、タイミング良く見てください。

セクション 9 のヒント

スーパーボーナスステージ

もし、ものすごい頑張れるのであれば、一行ずつフェッチでCSVにデータを出力してみてください。

出力項目
会員名称、購入月、合計購入数量
デリミタ文字
カンマ
エンコーディング
UTF-8
改行コード
LF
カラムヘッダー
一行目にはカラム名のヘッダー
出力ファイル
[PROJECT_ROOT]/target/hands-on-outside-bonus.csv ※1
CSV出力API
FileToken @since DBFlute-1.0.4F (それはもう、じっくりソースを読んで...)

※1: DfResourceUtil.getBuildDir(getClass())で target/classes の File インスタンスが取得できます

Logicクラスの selectLetsCursor() にて、そのまま処理を追加し、 テストコードのアサートにて、同じく FileToken を使ってそのファイルを読み込んでログに出力してみましょう。

だいぶコード量が増えネストも深くなってきます。適度にprivateメソッドなどに切り出して、Logicのpublicメソッドのコードの見通しを良くしましょう。 IDEにメソッドの切り出しのショートカットが用意されていますので、ぜひ使ってみましょう。(Eclipseであれば、切り出す行を選択して ctrl+1, extract to method)

ミラクルボーナスステージ

もし、ミラクルものすごい頑張れるのであれば、セクション３のカーソル検索の実装に、TSV出力処理を追加してみましょう。 (セクション３のコードをセクション９にコピーして持ってきて、修正してみましょう)

出力項目
会員名称、生年月日(yyyy/MM/dd)、正式会員日時(yyyy/MM/dd HH:mm:ss)
デリミタ文字
タブ
エンコーディング
UTF-8
改行コード
LF
カラムヘッダー
なし
出力ファイル
[PROJECT_ROOT]/target/hands-on-cb-bonus.tsv
TSV出力API
FileToken @since DBFlute-1.0.4F

同じように、アサートで FileToken を使ってそのファイルを読み込んでログに出力してみてください。

しかしながら、テストを実行するたびに target 配下を F5 するのは面倒ではありませんか？ そのテストの最後の処理でハンズオンのプロジェクトを自動的に F5 されるようにしてみたらどうでしょう!? (Eclipseじゃない人には無関係ですが、エクササイズとして実行するプログラムは書いてみましょう)

F5 API (リフレッシュAPI)
DfRefreshResourceRequest @since DBFlute-1.0.4F
Project Name
dbflute-hands-on
Request URL
http://localhost:8386/

どの単位でメソッドにするか？どういう名前にするか？

プログラミングの "かたち" に関しては、正解がありません。それはデザインだからです。 どうすればわかりやすいか？どうすれば勘違いしにくいか？常に考え続けることが大切です。

プログラマーに求められるデザイン脳 | jfluteの日記

ストアドプロシージャも外だしSQL

ちょっとだけストアドプロシージャも作って呼んでみましょう。

IN, OUTパラメータのプロシージャ

outsideSqlMap.dfprop のプロシージャ対応の ParameterBean を自動生成するプロパティを true にし、playsql に以下プロシージャを記述した replace-schema-50-procedure.sql を作成し、ReplaceSchemaしてプロシージャをDBに作成して、Sql2Entity を実行してください。そして、自動生成されたクラスを使ってエクササイズをやってみましょう。 

e.g. IN, OUTパラメーター混在のプロシージャ @replace-schema-50-procedure.sql
-- #df:begin#
create procedure SP_IN_OUT_PARAMETER(
      in v_in_varchar varchar(32)
    , out v_out_varchar varchar(32)
    , inout v_inout_varchar varchar(32)
)
begin
  set v_out_varchar = v_inout_varchar;
  set v_inout_varchar = v_in_varchar;
end;
-- #df:end#
ロジックのメソッド
SpInOutParameterPmb callInOutProcedure(String in, String inout)
  • SP_IN_OUT_PARAMETER を call
  • ParameterBean を戻す
対応テストメソッド
test_callInOutProcedure_値がへんてこりんになっていること()
  • in に "foo" を、inout に "bar" を設定
  • プロシージャを呼び出した後、ParameterBeanの値が入れ替わってること

結果セットを戻すプロシージャ

さらには、検索セットを戻すプロシージャを試してみましょう。

outsideSqlMap.dfprop にて、プロシージャの CustomizeEntity を自動生成するプロパティを true にして、以下のプロシージャを先ほどのSQLファイルに追加して、同じようにエクササイズをしてみてください。 

e.g. 複数の検索を実行するプロシージャ @replace-schema-50-procedure.sql
-- #df:begin#
create procedure SP_RETURN_RESULT_SET(in birthdateFrom DATE)
begin
  select MEMBER_NAME, BIRTHDATE, MEMBER_STATUS_CODE
    from MEMBER
   where BIRTHDATE >= birthdateFrom
   order by BIRTHDATE desc, MEMBER_ID asc;
  select MEMBER_STATUS_CODE, MEMBER_STATUS_NAME
    from MEMBER_STATUS
   order by DISPLAY_ORDER;
end;
-- #df:end#
ロジックのメソッド
SpReturnResultSetPmb callResultSetProcedure(LocalDate birthdateFrom)
  • 指定された ParameterBean で SP_RETURN_RESULT_SET を call
  • 会員名称と会員ステータス名称を一行のログで出力すること
対応テストメソッド
test_callResultSetProcedure_検索結果が取得できてること()
  • birthdate に1968年1月1日を設定
  • 生年月日が1968年以降であることをアサート

次のセクション

さて、次のセクションへ

ハンズオンセクション 10