问题: Mybatis的ResultMap中存在Collection时，分页不正确！！！

条件

• o1为订单表 orders
• o2为订单商品表 order_items
• o3为订单状态表 order_status

其中o1和o2是一对多的关系(一笔订单中包含多个商品)，

public class UserCenterOrderVO {
  private String orderId;
  private String orderStatus;
  private Integer realPayAmount;
  private Integer postAmount;
  private Integer payMethod;
  private Integer isComment;
  private Date createdTime;
  // 订单商品用list接收
  private List<OrderItems> subOrderItemList;
  
  // getter() setter()    
}

先需要对o1, o2, o3 做关联查询，用于订单展示, 而且需要分页，效果如下图所示:

订单表中共15条数据，每页展示10条，期望效果: 展示2页，第一页10条数据，第二页5条数据

有问题的sql

mapper.xml

resultMap中用collection去存放 order_items 的列表

    <resultMap id="UserCenterOrdersResultMap" type="com.icoding.vo.UserCenterOrderVO" >
        <id column="orderId" property="orderId" jdbcType="VARCHAR" />
        <result column="realPayAmount" property="realPayAmount" jdbcType="INTEGER" />
        <result column="postAmount" property="postAmount" jdbcType="INTEGER" />
        <result column="payMethod" property="payMethod" jdbcType="INTEGER" />
        <result column="isComment" property="isComment" jdbcType="INTEGER" />
        <result column="createdTime" property="createdTime" jdbcType="TIMESTAMP" />
        <result column="orderStatus" property="orderStatus" jdbcType="TIMESTAMP" />
        <collection property="subOrderItemList" ofType="com.icoding.pojo.OrderItems">
            <result property="itemId" column="itemId"/>
            <result property="itemImg" column="itemImg"/>
            <result property="itemName" column="itemName"/>
            <result property="itemSpecId" column="itemSpecId"/>
            <result property="itemSpecName" column="itemSpecName"/>
            <result property="price" column="itemPrice"/>
            <result property="buyCounts" column="buyCounts"/>
        </collection>
    </resultMap>
    <!--查询指定用户和指定状态下的订单列表-->
    <select id="getOrdersByStatus" resultMap="UserCenterOrdersResultMap">
        SELECT
	        o1.id as orderId,
	        o3.order_status as orderStatus,
	        o1.user_id as userId,
	        o1.real_pay_amount as realPayAmount,
	        o1.pay_method as payMethod,
	        o1.is_comment as isComment,
	        o1.created_time as createdTime,
	        o2.id as orderItemId,
	        o2.item_id as itemId,
	        o2.item_img as itemImg,
	        o2.item_name as itemName,
	        o2.item_spec_id as itemSpecId,
	        o2.item_spec_name as itemSpecName,
	        o2.price as itemPrice,
	        o2.buy_counts as buyCounts
        FROM orders o1
        LEFT JOIN order_items o2 ON o1.id = o2.order_id
        LEFT JOIN order_status o3 on o1.id = o3.order_id
        WHERE o1.user_id = #{queryParams.userId}
        <if test="queryParams.orderStatus != '' and queryParams.orderStatus != null">
            and o3.order_status = #{queryParams.orderStatus}
        </if>
        LIMIT #{queryParams.start}, #{queryParams.end}
    </select>

此时前端看到的效果如下:

发现第一页只有5条数据，但是仔细看发现，每笔订单下恰好有两个商品 2*5 恰好是10条记录，然后回头看sql，因为我是对 多表查询的结果集做的分页，问题就出在这，至于为什么会这样，大家可以看完后面的解决方法后自己琢磨琢磨。

解决方法

1. 订单表 orders 不要关联 订单商品表 order_items，然后分页查询
2. 单独写一个select 根据订单id--orderId 去查询订单商品
3. 把步骤2的select的 id 放入 resultMap的collect的select属性中，column用于接收订单id