原SQL 结果集映射
<resultMap id="rebateBalance" type="cn.hm1006.demo.CustomerRebateBalanceDTO">
<result property="customerCode" column="customer_code"/>
<result property="customerName" column="customer_name"/>
<result property="partyLayerCode" column="party_layer_code"/>
<result property="partyLayerName" column="party_layer_name"/>
<result property="branchCode" column="branch_code"/>
<result property="branchName" column="branch_name"/>
<collection property="crmOpenChannelResDTOList" ofType="cn.hm1006.demo.CrmOpenChannelResDTO">
<result property="openChannel" column="open_channel"/>
<result property="balanceAdjust" column="balance_adjust"/>
</collection>
</resultMap>
<select id="queryAllPageCustomerRebateBalance"
resultMap="rebateBalance">
SELECT
cci.customer_code,
cci.customer_name,
cci.party_layer_code,
cci.party_layer_name,
cci.branch_code,
cci.branch_name,
coc.open_channel,
coc.balance_adjust
FROM
`customer_crm_info` cci,
`crm_open_channel` coc
WHERE
coc.crm_info_id = cci.id
<if test="vo.customerCode!=null and vo.customerCode!=''">
and cci.customer_code = #{vo.customerCode}
</if>
<if test="vo.customerCode!=null and vo.customerCode!=''">
AND cci.customer_name = #{vo.customerName}
</if>
<if test="vo.partyLayerCode!=null and vo.partyLayerCode!=''">
AND cci.party_layer_code = #{vo.partyLayerCode}
</if>
<if test="vo.branchCode!=null and vo.branchCode!=''">
AND cci.branch_code = #{vo.branchCode}
</if>
</select>
新SQL 嵌套子查询，N+1查询，效率不好
<resultMap id="rebateBalance" type="cn.hm1006.demo.CustomerRebateBalanceDTO">
<result property="id" column="id"/>
<result property="customerCode" column="customer_code"/>
<result property="customerName" column="customer_name"/>
<result property="partyLayerCode" column="party_layer_code"/>
<result property="partyLayerName" column="party_layer_name"/>
<result property="branchCode" column="branch_code"/>
<result property="branchName" column="branch_name"/>
<!--子查询需传递多个参数，在column字段追加即可（column="{id=id,name=name}）-->
<collection property="crmOpenChannelResDTOList" ofType="cn.hm1006.demo.CrmOpenChannelResDTO"
select="selectRebateBalance" column="{id=id}">
</collection>
</resultMap>
<resultMap id="selectResultMap" type="cn.hm1006.demo.CrmOpenChannelResDTO">
<result property="openChannel" column="open_channel"/>
<result property="balanceAdjust" column="balance_adjust"/>
</resultMap>
<select id="selectRebateBalance" resultMap="selectResultMap">
select
coc.open_channel,coc.balance_adjust
from crm_open_channel coc
where
coc.crm_info_id = #{id}
</select>
<select id="queryAllPageCustomerRebateBalance"
resultMap="rebateBalance">
SELECT
cci.id,
cci.customer_code,
cci.customer_name,
cci.party_layer_code,
cci.party_layer_name,
cci.branch_code,
cci.branch_name
FROM
`customer_crm_info` cci
WHERE
1 = 1
<if test="vo.customerCode!=null and vo.customerCode!=''">
and cci.customer_code = #{vo.customerCode}
</if>
<if test="vo.customerCode!=null and vo.customerCode!=''">
AND cci.customer_name = #{vo.customerName}
</if>
<if test="vo.partyLayerCode!=null and vo.partyLayerCode!=''">
AND cci.party_layer_code = #{vo.partyLayerCode}
</if>
<if test="vo.branchCode!=null and vo.branchCode!=''">
AND cci.branch_code = #{vo.branchCode}
</if>
</select>
解决PageHelp分页总条数异常问题
Q.E.D.
