-- #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*/

/**
 * 会員一覧と最大購入価格を検索。
 * 会員名称が「ス」で始まる会員を対象に検索する。
 *
 * @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("ス"));
    }
}

/**
 * 会員と最大購入価格を一件検索。
 * 会員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());
}

 /**
  * 会員と最大購入価格をチェック付きの一件検索。
  * 存在しない会員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());
     }
 }

-- #MemberWithMaxPurchasePrice#
-- +cursor+

-- !MemberWithMaxPurchasePricePmb!
-- !!Integer memberId!!
-- !!String memberName!!

-- 会員IDと名称の前方一致で絞り込んで、会員一覧と購入最大価格を検索。
-- 絞り込み条件はそれぞれ値がnullじゃければ評価する。
select member.MEMBER_ID
     , member.MEMBER_NAME
...(以下変わらず)

/**
 * 会員一覧と最大購入価格をカーソル検索。
 * 会員名称が「ス」で始まる会員を検索する。
 *
 * @throws Exception
 */
public void test_OutsideSql_selectCursor_BasicExecution_Tx() throws Exception {
    // ## Arrange ##
    final String path = "sql/member/selectMemberWithMaxPurchasePrice.sql";
    final MemberWithMaxPurchasePricePmb pmb = new MemberWithMaxPurchasePricePmb();
    pmb.setMemberName("ス");
    final MemberWithMaxPurchasePriceCursorHandler handler = new MemberWithMaxPurchasePriceCursorHandler() {
       @Override
       protected Object fetchCursor(MemberWithMaxPurchasePriceCursor cursor) throws SQLException {
           while (cursor.next()) {
               final Integer memberId = cursor.getMemberId();
               final String memberName = cursor.getMemberName();
               final Integer maxPurchasePrice = cursor.getMaxPurchasePrice();
               log.debug(memberId + " - " + memberName + " - " + maxPurchasePrice);
               assertTrue(memberName.startsWith("ス"));
           }
           return null;
       }
    };

    // ## Act & Assert ##
    memberBhv.outsideSql().cursorHandling().selectCursor(path, pmb, handler);
}