はじめに
前回は,
一件検索
前回は,
もう一度,
リスト1:外だしSQLのサンプル
-- #MemberWithMaxPurchasePrice#
-- !MemberWithMaxPurchasePricePmb!
-- !!Integer memberId!!
-- !!String memberName!!
-- 会員IDと名称の前方一致で絞り込んで,会員一覧と購入最大価格を検索。
-- 絞り込み条件はそれぞれ値がnullじゃければ評価する。
select member.MEMBER_ID
, member.MEMBER_NAME
, (select max(purchase.PURCHASE_PRICE)
from PURCHASE purchase
where purchase.MEMBER_ID = member.MEMBER_ID) as MAX_PURCHASE_PRICE
from MEMBER member
/*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*/
そして,
リスト2:外だしSQLのリスト検索
/**
* 会員一覧と最大購入価格を検索。
* 会員名称が「ス」で始まる会員を対象に検索する。
*
* @throws Exception
*/
public void test_OutsideSql_selectList_BasicExecution_Tx() throws Exception {
// ## Arrange ##
final String path = "sql/member/selectMemberWithMaxPurchasePrice.sql";
final MemberWithMaxPurchasePricePmb pmb = new MemberWithMaxPurchasePricePmb();
pmb.setMemberName("ス");
final Class entityType = MemberWithMaxPurchasePrice.class;
// ## Act ##
final List memberList
= memberBhv.outsideSql().selectList(path, pmb, entityType);
// ## Assert ##
for (MemberWithMaxPurchasePrice member : memberList) {
final String memberName = member.getMemberName();
final Integer maxPurchasePrice = member.getMaxPurchasePrice();
log.debug(memberName + " - " + maxPurchasePrice);
assertTrue(memberName.startsWith("ス"));
}
}
BehaviorのoutsideSql()からselecList()を呼んでいますね。一件検索をしたいときは,
リスト3:外だしSQLの一件検索
/**
* 会員と最大購入価格を一件検索。
* 会員IDが6の会員を対象に検索する。
*
* @throws Exception
*/
public void test_OutsideSql_selectEntity_BasicExecution_Tx() throws Exception {
// ## Arrange ##
final Integer expectedMemberId = 6;
final String path = "sql/member/selectMemberWithMaxPurchasePrice.sql";
final MemberWithMaxPurchasePricePmb pmb = new MemberWithMaxPurchasePricePmb();
pmb.setMemberId(expectedMemberId);
final Class entityType = MemberWithMaxPurchasePrice.class;
// ## Act ##
final MemberWithMaxPurchasePrice member
= memberBhv.outsideSql().entityHandling().selectEntity(path, pmb, entityType);
// ## Assert ##
final String memberName = member.getMemberName();
final Integer maxPurchasePrice = member.getMaxPurchasePrice();
log.debug(memberName + " - " + maxPurchasePrice);
assertEquals(expectedMemberId, member.getMemberId());
}
このselectEntity()は,
リスト4:外だしSQLのチェック付き一件検索
/**
* 会員と最大購入価格をチェック付きの一件検索。
* 存在しない会員IDで検索してチェックに引っかかり例外になる。
*
* @throws Exception
*/
public void test_OutsideSql_selectEntityWithDeletedCheck_BasicExecution_Tx() throws Exception {
// ## Arrange ##
final Integer expectedMemberId = 99999;// No Exist
final String path = "sql/member/selectMemberWithMaxPurchasePrice.sql";
final MemberWithMaxPurchasePricePmb pmb = new MemberWithMaxPurchasePricePmb();
pmb.setMemberId(expectedMemberId);
final Class entityType = MemberWithMaxPurchasePrice.class;
// ## Act & Assert ##
try {
memberBhv.outsideSql().entityHandling().selectEntityWithDeletedCheck(path, pmb, entityType);
fail();
} catch (EntityAlreadyDeletedException e) {
// OK
log.debug(e.getMessage());
}
}