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>