MPn+1查询实现

在实现一个使用分页进行查询一对多的数据时候会出现总数不对的情况。例如：我查询一个用户的角色（这时候用户对应一，然后角色对应多，查询的总数并不是用户数，而是角色数），这时候采用n+1查询，先查询主表，然后更据resultmap查询内表（角色表）；
：我这个例子是一个运费模板里面有关联的商品信息和地址信息；

1.构造resultMap，映射对象

 <resultMap id="templateMap" type="com.cqxh.yuetaoproject.vo.order.freight.FreightTemplateVo">
        <!-- 基础字段 -->
        <id column="template_id" property="templateId" />
        <result column="seller_name" property="sellerName" />
        <result column="type" property="type" />
        <result column="name" property="name" />
        <result column="seller_id" property="sellerId" />
        <result column="free_shipping_value" property="freeShippingValue" />
        <result column="shipping_fee_value" property="shippingFeeValue" />
        <result column="address_code" property="addressCode" />
        <result column="address_detail" property="addressDetail" />
        <result column="addtime" property="addtime" />
        <result column="updatetime" property="updatetime" />
        <result column="memo" property="memo" />

        <!-- 嵌套关联字段：connectInfo -->
        <collection property="connectInfo" ofType="com.cqxh.yuetaoproject.vo.order.freight.FreightTemplateDataVo"
        select="selectConnectInfo" column="{template_id}">
            <id column="template_id" property="templateId" />
            <result column="type" property="type" />
            <result column="connect_id" property="connectId" />
            <result column="connect_name" property="connectName" />
        </collection>

        <!-- 嵌套关联字段：addressInfo -->
        <collection property="addressInfo" ofType="com.cqxh.yuetaoproject.pojo.order.freight.SellerFreightTemplateAddress"
        select="selectAddressInfo" column="{template_id}">
            <id column="template_id" property="templateId" />
            <result column="address_code" property="addressCode" />
            <result column="fee" property="fee" />
        </collection>
    </resultMap>

public class FreightTemplateVo {

   @ApiModelProperty(value = "模板id")
   private Integer templateId;
   @ApiModelProperty(value = "商家名称")
   private String sellerName;

   @ApiModelProperty(value = "模板类型 0所有商品包邮 1所有商品满足金额包邮 2自定义包邮")
   private Integer type;

   @NotBlank(message = "模板名称不能为空")
   @ApiModelProperty(value = "模板名称")
   private String name;

   @NotNull
   @ApiModelProperty(value = "商家号")
   private String sellerId;

   @NotNull
   @ApiModelProperty(value = "包邮金额")
   @Min(value = 0,message = "包邮金额不能小于0")
   private Integer freeShippingValue;


   @NotNull
   @ApiModelProperty(value = "运费")
   @Min(value = 0,message = "运费不能小于0")
   private Integer shippingFeeValue;

   @NotNull
   @ApiModelProperty(value = "新增时间")
   private Date addtime;

   @NotNull
   @ApiModelProperty(value = "修改时间")
   private Date updatetime;

   @NotNull
   @ApiModelProperty(value = "新增-修改时间")
   private String addUpdateTime;


   @NotNull
   @ApiModelProperty(value = "发货地")
   private String addressCode;

   @NotNull
   @ApiModelProperty(value = "发货地详情",notes = "xx省xx市xx区")
   private String addressDetail;
   @NotNull
   @ApiModelProperty(value = "模板备注")
   private String memo;
   @ApiModelProperty(value = "关联商品或类别信息",notes = "每个关联信息中只需要提供type和connectId")
   private List<SellerFreightTemplateData> connectInfo;
   @ApiModelProperty(value = "偏远地区",notes = "每个信息中只需要提供addressCode和fee")
   private List<SellerFreightTemplateAddress> addressInfo;

}

1. 这里的resulMap就是用来映射结果的，其中<result>用来映射单个字段值；
2. <collection>用来映射list值，
   1. 这个属性最主要的就是colum，这个代表的就是外表(这里指的就是这个模板表）中需要给内表的参数，因为内表查询一定是有个条件的，这个条件就是外表的template_id,及主表查询出来的主键（这里对应的是数据库的字段名），然后因为他是先查主表，然后再查内表,因为在主表中 <id column="template_id" property="templateId" />这个已经映射成了templateId，所以内表查询的时候使用的参数是tempalteId;
   2. select，这个select的属性就是选择查询语句，自己构造一个查询的语句类似于如下

    <select id="selectConnectInfo" resultType="com.cqxh.yuetaoproject.vo.order.freight.FreightTemplateDataVo">
        SELECT
        template_id,
        ftd.type,
        ftd.connect_id AS connectId,
        CASE
        WHEN ftd.type = 0 THEN gc.name
        ELSE gq.name
        END AS connectName
        FROM
        seller_freight_template_data ftd
        LEFT JOIN
        goods_class gc
        ON ftd.type = 0 AND gc.class_id = ftd.connect_id
        LEFT JOIN
        goods_query gq
        ON ftd.type = 1 AND gq.goods_id = ftd.connect_id;
        WHERE
            ftd.template_id = #{templateId}
            <if test="type!=null">
                and ftd.type = #{type}
            </if>

    </select>

    <select id="selectAddressInfo" resultType="com.cqxh.yuetaoproject.pojo.order.freight.SellerFreightTemplateAddress">
        SELECT
        template_id, address_code, fee
        FROM
        seller_freight_template_address
        WHERE
        template_id = #{templateId}
    </select>

--主表查询语句
 <select id="selectTemplateInfo" resultMap="templateMap">
      SELECT
      template_id, s.name seller_name, ft.type, ft.name, ft.seller_id, free_shipping_value,
      shipping_fee_value, address_code, address_detail, memo,ft.addtime,ft.updatetime
      FROM
      seller_freight_template ft
      left join seller s
      on s.seller_id = ft.seller_id
      where ft.is_valid = 1
      <if test="map!=null and map.name!=null">
          AND ft.name LIKE CONCAT('%', #{map.name}, '%')
      </if>
      <if test="map!=null and map.type!=null">
          AND ft.type = #{map.type}
      </if>
      <if test="map!=null and map.is_published!=null">
          AND ft.is_published = #{map.isPublished}
      </if>
      order by
      <choose>
          <when test="orderField != null and orderField != ''">
              <choose>
                  <when test="orderField == 'updateTime'">
                      ft.updatetime
                  </when>

                  <!-- 添加其他允许排序的字段 -->
                  <otherwise>
                      ft.updatetime
                  </otherwise>
              </choose>
              <choose>
                  <when test="isAsc != null and isAsc == 1">
                      ASC
                  </when>
                  <otherwise>
                      DESC
                  </otherwise>
              </choose>
          </when>
          <otherwise>
              ft.updatetime DESC
          </otherwise>
      </choose>
  </select>