逆引きConditionBean
概要
ConditionBeanの利用方法を要件からの逆引きで説明します。
Exampleで利用しているデータベースに関しては、こちらのERDをご覧下さい。
曖昧検索でエスケープ処理を入れたい
例えば、'100%ジュース'という文字列の条件値として中間一致の曖昧検索をするとした場合に、エスケープ処理をしないと'100%ジュース'だけでなく'100回まわってジュース一気飲み'というデータまでHITしてしまいます。これは「xxx like '%100%ジュース%'」というように「100%」の「%」もワイルドカードとして扱われてしまうためです。
エスケープするには以下のようにSQLである必要があります。エスケープ文字を指定して、ワイルドカードとして認識されたくない文字の前にエスケープ文字を配置します。
ex) パイプラインを使ってエスケープした中間一致検索 @SQL
where MEMBER_NAME like '%100|%ジュース%' escape '|'
ConditionBeanでは、この「エスケープ文字の指定」と「エスケープ文字の埋め込み」を以下のように実装することが可能です。
ex) パイプラインを使ってエスケープした中間一致検索 @Java
MemberCB cb = new MemberCB();
cb.query().setMemberName("100%ジュース", new LikeSearchOption().likeContain().escapeByPipeLine());
... = memberBhv.selectList(cb);
パイプラインだけでなく、スラッシュ、バックスラッシュ、アットマークなど用意されています(escapeByXxx())。業務で適切なエスケープ文字を選んで下さい。
関連：外だしSQLの曖昧検索
親テーブル(ForeignTable)の条件で絞り込みたい
例えば、購入(PURCHASE)の一覧画面にて、「生産中止の商品に関する購入」という絞り込みをする要件があったとした場合、SQLとして以下のようになります。
ex) 「生産中止の商品に関する購入」という絞り込みの検索 @SQL
from PURCHASE purchase
left outer join PRODUCT product
on purchase.PRODUCT_ID = product.PRODUCT_ID
where product.PRODUCT_STATUS_CODE = 'PST'
ConditionBeanでは、以下のように実装することが可能です。
ex) 「生産中止の商品に関する購入」という絞り込みの検索 @Java
PurchaseCB cb = new PurchaseCB();
cb.query().queryProduct().setProductStatusCode_Equal_ProductStop();
... = memberBhv.selectList(cb);
上記の例では、setProductStatusCode_Equal_ProductStop()はDBFluteのClassification機能を利用しています。
上記の例では、条件絞り込みのためだけに商品テーブルを結合していますが、さらに商品テーブル自身のデータを取得したいという場合は、cb.setupSelect_Product();を呼び出します。
「left outer join」は「inner join」の方が良いのでは？と思われるかも知れませんが、Where句に結合先のカラムでの絞り込みを入れた瞬間にSQLの結果としては違いはなくなります。そのことをオプティマイザも考慮するため、パフォーマンス的にも差が出ることはありません。但し、古いバージョンのDBの場合はこの辺がシビアな場合もありますが、それほど古い場合はDBFluteがそもそも未対応の可能性もあります。
子テーブル(ReferrerTable)の条件で絞り込みたい
例えば、会員(MEMBER)の一覧画面にて、「価格1万以上の購入をしたことのある会員」という絞り込みをする要件があったとした場合、SQLとして以下のようになります。
ex) 「1万以上の購入をしたことのある会員」という絞り込みの検索 @SQL
from MEMBER member
where member.MEMBER_ID exists (
select purchase.MEMBER_ID
from PURCHASE purchase
where purchase.MEMBER_ID = member.MEMBER_ID
and purchase.PURCHASE_PRICE >= 10000
)
ConditionBeanでは、以下のように実装することが可能です。
ex) 「1万以上の購入をしたことのある会員」という絞り込みの検索 @Java
MemberCB cb = new MemberCB();
cb.query().existsPurchaseList(new SubQuery<PurchaseCB>() {
public void query(PurchaseCB subCB) {
subCB.query().setPurchasePrice_GreaterEqual(10000);
}
});
... = memberBhv.selectList(cb);
PurchaseCBの部分で匿名の括弧'{}'で囲っているのは、単に見やすさを考慮しているだけです(必須ではありません)。
ex) DBFlute-0.7.0以下の場合
MemberCB cb = new MemberCB();
{
PurchaseCB purchaseSubCB = new PurchaseCB();
purchaseSubCB.query().setPurchasePrice_GreaterEqual(10000);
cb.query().setMemberId_ExistsSubQuery(purchaseSubCB.query());// SubQueryの登録
}
... = memberBhv.selectList(cb);
結合する親テーブル(ForeignTable)を絞り込みたい
これは、「親テーブル(ForeignTable)の条件で絞り込みたい」とは違い、基点テーブルは絞り込まれずに、絞り込まれるのは結合した親テーブルだけとなります。要は、「親テーブルを絞り込んでから」結合したい場合です。
例えば、購入(PURCHASE)の一覧画面にて、会員名称を一緒に表示したいが正式会員だけ名称を表示する(退会会員や仮会員は規約上名称をその画面上に表示してはいけないなど)、という要件があったとした場合、SQLとして以下のようになります。
ex) 「会員名称を一緒に表示したいが正式会員だけ名称を表示する」の検索 @SQL
from PURCHASE purchase
left outer join MEMBER member
on purchase.MEMBER_ID = member.MEMBER_ID
and member.MEMBER_STATUS_CODE = 'FML'
ちょっと例が無理矢理かもしれません。主には非依存のリレーションと論理削除の仕組みを組み合わせた場合に良く利用されます。結合先の情報を必要とするので結合して取得するが、論理削除されているものは存在しないものとして結合対象から外すというような場合です。もし、依存型のリレーションの場合は、結合先が論理削除されていたら自分自身も削除されているとみなすので別項の「親テーブル(ForeignTable)の条件で絞り込みたい」と同じ話になります。
ConditionBeanでは、以下のように実装することが可能です。
ex) 「会員名称を一緒に表示したいが正式会員だけ名称を表示する」の検索 @Java
PurchaseCB cb = new PurchaseCB();
cb.query().queryMember().on().setMemberStatusCode_Equal_Formalized();
... = memberBhv.selectList(cb);
setMemberStatusCode_Equal_Formalized()はDBFluteのClassification機能を利用しています。
On句の条件でなう、インラインビューを使ったやり方もあります。
ex) 「会員名称を一緒に表示したいが正式会員だけ名称を表示する」の検索(インラインビュー) @SQL
from PURCHASE purchase
left outer join (select * from MEMBER where member.MEMBER_STATUS_CODE = 'FML') member
ex) 「会員名称を一緒に表示したいが正式会員だけ名称を表示する」の検索(インラインビュー) @Java
PurchaseCB cb = new PurchaseCB();
cb.query().queryMember().inline().setMemberStatusCode_Equal_Formalized();
... = memberBhv.selectList(cb);
しかし、基本的にはOn句の利用をお奨めします。「left outer join」と「inner join」の話とは違い、DB次第ではありますがオプティマイザがこの２つを同一のものとみなす力がない可能性があります。
インラインビューの存在意義は、「より複雑な絞り込みが可能」という点です。例えば、On句の中ではInScopeSubQueryやExistsSubQueryが利用できない(ConditionBeanの)仕様になっていますが、インラインビューではその制限がありません。