ハンズオンセクション 9

概要

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

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

事前準備

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

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

外だしSQLについて

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

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

そして、外だし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の実行計画を見てみましょう。 ctrl + shift + R (リソースの検索) で login-mysql-root.bat(sh) を起動して maihamadb に接続(use)し、explain [SQL文字列] を流してみましょう。

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

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

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

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

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

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

外だしSQLのファイル
MemberBhv_selectPartOfMember.sql
  • (Manual)ページング検索すること
  • 基本は "初めての外だしSQL" のSQLと同じで...
  • 会員ステータスの等値条件は要らない
  • 会員サービスのサービスポイント数の大なり条件を追加
  • 結合に関して、カウント検索のパフォーマンスを最大限考慮すること (会員サービスの結合とか)
  • そのとき、IFコメントに記述する条件が複雑にならないように (代理判定メソッドを使う)
ロジックのメソッド
PagingResultBean<PartOfMember> selectPartOfMember()
  • 会員名称に "vi" を含む会員を対象に検索
  • ページサイズは 4、ページ番号は 1 で検索すること
  • 指定された ParameterBean で検索して、検索結果を戻す
対応テストメソッド
test_selectPartOfMember_ページング検索されること()
  • 期待通りのSQLがログに出力されることを目視確認する
  • 検索したカラム全てのデータをログ出力
  • 検索結果がページサイズ以下のデータだけであることをアサート
※実装できたら、サービスポイント数が1000以上というのを追加してみましょう。実行して落ちなければOKです。
※代理判定メソッドの UnitTest をやってみましょう。PartOfMemberPmb で 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がログに出力されることを目視確認する
  • 期待通りの総レコード数であることを目視確認する ※1
  • 検索したカラム全てのデータをログ出力
※1: 一時的にpageSizeを99999にして実行した実データ検索の件数が総レコード数と言える

外だしSQLならでは

理解しましょう。ConditionBeanではできない、外だしSQLならではの検索と言えます。

お手軽チャレンジ

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

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

外だしSQLのコメント(タイトルと説明)を必須にして、一括チェックすることができます。

※Java8 (DBFlute-1.1) では、最初から必須です。

プロパティの設定で、OutsideSqlTestタスク実行時にコメントの有無を例外として検知できます。 outsideSqlMap.dfprop の isRequiredSqlTitle と isRequiredSqlDescription を true にして、OutsideSqlTestタスクを実行してみてください。

現場フィット - 外だしSQLのタイトル
e.g. 外だしSQLのコメントを必須に設定 @outsideSqlMap.dfprop
 ...
    ; isRequiredSqlTitle = true

 ...
    ; isRequiredSqlDescription = true

 ...

どこかで落ちたら、落ちないように修正しましょう。

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

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

"さらに外だし" のSQLで、検索カラム (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 (ハンズオンのデフォルト) を使っているのであれば、そのままでは実はメモリ対策になっていません。 outsideSql().configure() メソッドを使って、StatementConfig の FetchSize に Integer.MIN_VALUE を設定して再実行してみましょう。

(...テスト再実行中) ※エラーメッセージには真摯な姿勢で...エラーメッセージでググったり...

ちなみに、テストクラスにて新しいトランザクションを発行したり、トランザクション分離レベルを制御したりできます(@since UTFlute-0.3.3)。 これらはスーパークラスのメソッドです。

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

ちなみに、ハンズオンで使っているUTFluteは、こういうときのためにあります。 (DIコンテナがやっているAOPと同じような処理をUTFlute方式で実現...)

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

もし、ものすごい頑張れるのであれば、一行ずつフェッチで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 を使ってそのファイルを読み込んでログに出力してみましょう。 (講師によるコードリーディングの小話あり)

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

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

出力項目
会員名称、生年月日(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/

ストアドプロシージャも外だし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年以降であることをアサート

次のセクション

さて、次のセクションへ