mybatis-plus：一对多查询分页时total和size错误的解决方案

无论是mybatis-plus还是之前的mybatis，在使用中都会遇到对象之间一对多，多对多的联查，如果使用collection，并在在sql中使用left join或者right join时，查询出的数据是没问题的，但是分页时，数据总条数total和当页数量size会错误。
其实是使用mybatis-plus的姿势不对。

先写出错误的代码

Mapper层接口

List<OrderVO> selectOrderPage(IPage page, OrderVO order);

mapperXml代码

 <resultMap id="orderResultMap" type="com.ht.store.entity.Order">
    <id column="orderId" property="id"/>
    <result column="amount" property="amount"/>
    <result column="transport" property="transport"/>
    <result column="address_id" property="addressId"/>
    <result column="pay_way" property="payWay"/>
    <result column="remark" property="remark"/>
    <result column="goods_name" property="goodsName"/>
    <result column="openid" property="openid"/>
    <result column="create_user" property="createUser"/>
    <result column="create_time" property="createTime"/>
    <result column="update_user" property="updateUser"/>
    <result column="update_time" property="updateTime"/>
    <result column="create_dept" property="createDept"/>
    <result column="is_deleted" property="isDeleted"/>
    <result column="status" property="status"/>
</resultMap>

<resultMap id="orderVoResultMap" type="com.ht.store.vo.OrderVO" extends="orderResultMap">
    <association property="orderRefund" javaType="com.ht.store.entity.OrderRefund">
        <result column="refund_amount" property="refundAmount"/>
    </association>
    <collection property="goodsList" javaType="java.util.ArrayList" ofType="com.ht.store.entity.OrderGoods">
        <result column="goods_id" property="goodsId"/>
        <result column="goods_name" property="goodsName"/>
        <result column="order_id" property="orderId"/>
        <result column="goods_price" property="goodsPrice"/>
        <result column="goods_count" property="goodsCount"/>
        <result column="goods_money" property="goodsMoney"/>
        <result column="goods_category" property="goodsCategory"/>
        <result column="goods_img" property="goodsImg"/>
    </collection>
</resultMap>
<select id="selectOrderPage" resultMap="orderVoResultMap" parameterType="com.ht.store.entity.Order">
SELECT
      o.*,
      og.id as og_id,
      og.goods_id,
      og.order_id,
      og.goods_price,
      og.goods_count,
      og.goods_money,
      og.goods_category,
      og.goods_img,
      r.id as or_id,
      r.refund_amount
  FROM
      orders o
          LEFT JOIN order_goods og ON o.id = og.order_id
          LEFT JOIN order_refund r ON o.id = r.order_id
  WHERE o.is_deleted = 0
  <if test="order.id != null">
     and o.id = #{order.id}
  </if>
  <if test="order.status != null">
      and o.status = #{order.status}
  </if>
  <if test="order.transport != null">
      and o.transport = #{order.transport}
  </if>
  <if test="order.payWay != null">
      and o.pay_way = #{order.payWay}
  </if>
  <if test="order.openid != null">
      and o.openid = #{order.openid}
  </if>
  <if test="order.createTime != null">
      and o.create_time = #{order.createTime}
  </if>
  <if test="order.startTime != null">
      and o.create_time &gt;= #{order.startTime}
  </if>
  <if test="order.endTime != null">
      and o.create_time &lt;= #{order.endTime}
  </if>
  <if test="order.goodsName != null and order.goodsName !=''">
      and og.goods_name like concat('%','${order.goodsName}','%')
  </if>-->
</select>

修改之后的代码：

<resultMap id="orderResultMap" type="com.ht.store.entity.Order">
        <id column="orderId" property="id"/>
        <result column="amount" property="amount"/>
        <result column="transport" property="transport"/>
        <result column="address_id" property="addressId"/>
        <result column="pay_way" property="payWay"/>
        <result column="remark" property="remark"/>
        <result column="goods_name" property="goodsName"/>
        <result column="openid" property="openid"/>
        <result column="create_user" property="createUser"/>
        <result column="create_time" property="createTime"/>
        <result column="update_user" property="updateUser"/>
        <result column="update_time" property="updateTime"/>
        <result column="create_dept" property="createDept"/>
        <result column="is_deleted" property="isDeleted"/>
        <result column="status" property="status"/>
    </resultMap>

    <resultMap id="orderVoResultMap" type="com.ht.store.vo.OrderVO" extends="orderResultMap">
        <association property="orderRefund" javaType="com.ht.store.entity.OrderRefund" column="orderId"
                     select="com.ht.store.mapper.OrderRefundMapper.selectOrderRefundByOrderId">
        </association>
        <collection property="goodsList" javaType="java.util.ArrayList" ofType="com.ht.store.entity.OrderGoods"
        column="orderId" select="com.ht.store.mapper.OrderGoodsMapper.selectGoodsListByOrderId">
        </collection>
    </resultMap>

<select id="selectOrderPage" resultMap="orderVoResultMap" parameterType="com.ht.store.entity.Order">
   SELECT
       o.*,
       o.id as orderId
   FROM
       orders o
   WHERE o.is_deleted = 0
       <if test="order.id != null">
           and o.id = #{order.id}
       </if>
       <if test="order.status != null">
           and o.status = #{order.status}
       </if>
       <if test="order.transport != null">
           and o.transport = #{order.transport}
       </if>
       <if test="order.payWay != null">
           and o.pay_way = #{order.payWay}
       </if>
       <if test="order.openid != null">
           and o.openid = #{order.openid}
       </if>
       <if test="order.createTime != null">
           and o.create_time = #{order.createTime}
       </if>
       <if test="order.startTime != null">
           and o.create_time &gt;= #{order.startTime}
       </if>
       <if test="order.endTime != null">
           and o.create_time &lt;= #{order.endTime}
       </if>
</select>

com.ht.store.mapper.OrderRefundMapper.selectOrderRefundByOrderId接口

mapper层接口代码

OrderRefund selectOrderRefundByOrderId(String orderId);

mapperXml文件代码

<select id="selectOrderRefundByOrderId" resultMap="orderRefundResultMap">
    select * from order_refund where is_deleted = 0 and order_id = #{orderId}
</select>

com.ht.store.mapper.OrderGoodsMapper.selectGoodsListByOrderId接口

List<OrderGoods> selectGoodsListByOrderId(String orderId);

mapperXml

<select id="selectCartGoodsPage" resultMap="cartGoodsResultMap">
    select * from cart_goods where is_deleted = 0
</select>

修改之后total和size均已正常，并且联查的信息也是正常的。