iBATIS一对多/多对多N+1问题解决方案

对于iBATIS一对多/多对多的问题，传统的办法是在一对多/多对多关联的属性上

再做一次子查询，这个解决办法很简单易懂，但是有个缺点，会导致N+1 selects

，导致查询的性能瓶颈，更好的解决办法是sql做一个表连接，然后主表的

resultMap配置上加“groupBy='...'”属性，这样一次查询就搞定，避免了N+1问

题，下面请看代码：
<resultMap id="billCardResult" class="BillCard" groupBy="cardId">
       <result property="cardId" column="card_id" />
       <result property="cardCode" column="card_code" />
       <result property="cardName" column="CARD_NAME" />
       <result property="cardCodeLength" column="CODE_LENGTH" />
       <result property="cardType" column="CARD_TYPE" />
       <result property="cardTypeName" column="CARD_NAME" />
       <result property="cardDescription" column="CARD_DESC" />
       <result property="personalDrawLimit" column="LIMIT_DRAW" />
       <result property="isVoucher" column="IS_VOUCHER" />
       <result property="isImport" column="IS_IMPORT" />
       <result property="returnLimit" column="LIMIT_RETURN" />
       <result property="hasCheckCode" column="HAS_CHECK_CODE" />
       <result property="codeRelation" column="CODE_RELA" />
       <result property="otherCode" column="OTHRE_CODE" nullValue=""/>
       <result property="status" column="STATUS" />
       <result property="createDate" column="CREATE_DATE" />
       <result property="createUserId" column="CREATED_BY" />
       <result property="updateDate" column="UPDATE_DATE" />
       <result property="updateUserId" column="UPDATED_BY" /> 
       <result property="premiumSum" column="PREMIUM_SUM" /> 
       <result property="billCardProductList"

resultMap="BillCardDefinition.billCardProductResult" nullValue="null"/>

 
    </resultMap>

<resultMap id="billCardProductResult" class="BillCardProduct">
       <result property="cardId" column="billCardProduct_card_id" />
       <result property="productId" column="PRODUCT_ID" />
       <result property="policyAmount" column="POLICY_AMOUNT" />
       <result property="premium" column="PREMIUM" />
       <result property="unit" column="UNIT" />
       <result property="productLevel" column="PRODUCT_LEVEL" />
       <result property="coverageTermId" column="COVERAGETERMID" />
       <result property="coveragePeriod" column="COVERAGEPERIOD" />
       <result property="paymentTermId" column="PAYMENTTERMID" />
       <result property="paymentPeriod" column="PAYMENTPERIOD" />
       <result property="paymentType" column="PAYMENTTYPE" />
       <!-- <result property="productId" column="PRODUCT_ID" /> -->
    </resultMap> 

    <select id="getBillCardById" parameterClass="Long"

resultMap="billCardResult">
       select billCard.*,billCardProduct.*,billCardProduct.card_id as

billCardProduct_card_id
        from t_billcard billCard
        join T_BILLCARD_TYPE billCardType on

billCard.Card_Type=billCardType.Card_Type
        left join t_billcard_product billCardProduct on

billCard.Card_Id=billCardProduct.Card_Id
        where billCard.status='Y'
        and billCard.Card_Id=#value#
        order by billCard.card_code asc
    </select>