mybatis collection嵌套集合

业务场景：商户集合嵌套卡券集合

 

 JAVA代码：

@Data
public class UserMerchantCardVo {
    /** 商户名称*/
    private String merchantName;
    /** 商户地址*/
    private String address;
    /** 商户经度*/
    private BigDecimal lng;
    /** 商户纬度*/
    private BigDecimal lat;
    /** 电话*/
    private String bossTel;
    /** 用户卡券列表*/
    private List<UserCardVo> cardList;
}

SQL:

<resultMap id="result" type="com.zhhs.app.vo.UserMerchantCardVo">
        <result property="merchantName" column="merchant_name"/>
        <result property="address" column="address"/>
        <result property="lng" column="lng"/>
        <result property="lat" column="lat"/>
        <result property="bossTel" column="boss_tel"/>
        <collection property="cardList" ofType="com.zhhs.app.vo.UserCardVo">
            <id property="id" column="id"/>
            <result property="cardName" column="card_name"/>
            <result property="cardType" column="card_type"/>
            <result property="cardTime" column="card_time"/>
            <result property="cardNumber" column="card_number"/>
            <result property="status" column="status"/>
            <result property="cardUseExplain" column="card_use_explain"/>
            <result property="cardUseProduct" column="card_use_product"/>
        </collection>
    </resultMap>

    <select id="getUserCardList" resultMap="result">
        SELECT muc.id,muc.card_id,bm.merchant_name,bm.address,bm.lng,bm.lat,bm.boss_tel,bc.card_name,bc.card_type,bc.card_time,bc.card_number,bc.card_use_explain,muc.status,
        CASE bc.card_type WHEN '通用券' THEN '全场通用' WHEN '品类券' THEN CONCAT( '限', GROUP_CONCAT( bpt.type_name ) ) ELSE CONCAT( '限', GROUP_CONCAT( bp.product_name ) ) END AS card_use_product
        FROM media_user_card muc
        LEFT JOIN base_merchant bm ON muc.merchant_id = bm.id
        LEFT JOIN base_card bc ON muc.card_id = bc.id
        LEFT JOIN media_card_product_type bcp ON bcp.card_id = bc.id
        LEFT JOIN base_product_type bpt ON bcp.product_type_id = bpt.id
        LEFT JOIN base_product bp ON bcp.product_id = bp.id
        WHERE muc.user_id = #{userId} and muc.status = #{status}
        GROUP BY muc.id
        ORDER BY muc.id desc
    </select>

 这样写有个弊端就是数据结果会按卡券分页，而不是根据主表商户进行分页

改为下面方法

<select id="getUserCardList" resultMap="result">
        SELECT bm.id as merchant_id,bm.merchant_name,bm.address,bm.lng,bm.lat,from_base64(bm.boss_tel) as boss_tel,muc.user_id,muc.status
        FROM media_user_card muc
        LEFT JOIN base_merchant bm ON muc.merchant_id = bm.id
        WHERE muc.user_id = #{userId} and muc.status =#{status}
        GROUP BY bm.id
        ORDER BY muc.id desc
</select>
主表根据商户查询分页

<resultMap id="result" type="com.zhhs.app.vo.UserMerchantCardVo">
        <result property="merchantName" column="merchant_name"/>
        <result property="address" column="address"/>
        <result property="lng" column="lng"/>
        <result property="lat" column="lat"/>
        <result property="bossTel" column="boss_tel"/>
        <collection property="cardList" column="{merchantId=merchant_id,userId=user_id,status=status}"  ofType="com.zhhs.app.vo.UserCardVo" javaType="ArrayList" select="getCardDetailByMerchantId">

        </collection>
    </resultMap>
查询结果集，指明子查询

{merchantId=merchant_id,userId=user_id,status=status} 为子查询需要传递的参数

子查询getCardDetailByMerchantId   

<select id="getCardDetailByMerchantId"  parameterType="map" resultMap="UserCardVo">
         SELECT muc.id,bc.card_name,bc.card_type,muc.expire_time AS card_time,bc.card_number,bc.card_use_explain,muc.status,
        CASE bc.card_type WHEN '通用券' THEN '全场通用' WHEN '品类券' THEN CONCAT( '', GROUP_CONCAT( bpt.type_name ) ) ELSE CONCAT( '', GROUP_CONCAT( bp.product_name ) ) END AS card_use_product
        FROM media_user_card muc
        LEFT JOIN base_merchant bm ON muc.merchant_id = bm.id
        LEFT JOIN base_card bc ON muc.card_id = bc.id
        LEFT JOIN media_card_product_type bcp ON bcp.card_id = bc.id
        LEFT JOIN base_product_type bpt ON bcp.product_type_id = bpt.id
        LEFT JOIN base_product bp ON bcp.product_id = bp.id
        WHERE muc.user_id = #{userId} and muc.status = #{status} and bm.id = #{merchantId}
        GROUP BY muc.id
        ORDER BY muc.id desc
    </select>

<resultMap id="UserCardVo" type="com.zhhs.app.vo.UserCardVo">
        <id property="id" column="id"/>
        <result property="cardName" column="card_name"/>
        <result property="cardType" column="card_type"/>
        <result property="cardTime" column="card_time"/>
        <result property="cardNumber" column="card_number"/>
        <result property="status" column="status"/>
        <result property="cardUseExplain" column="card_use_explain"/>
        <result property="cardUseProduct" column="card_use_product"/>
    </resultMap>

定义vo

@Data
public class UserMerchantCardVo {
    /** 商户名称*/
    private String merchantName;
    /** 商户地址*/
    private String address;
    /** 商户经度*/
    private BigDecimal lng;
    /** 商户纬度*/
    private BigDecimal lat;
    /** 电话*/
    private String bossTel;
    /** 用户卡券列表*/
    private List<UserCardVo> cardList;
}