未曾关注的Mybatis+PageHelper的分页查询的BUG

想必大家对SpringBoot+Mybatis+PageHelper这一套再熟悉不过了，但是你们是否遇到过这样的BUG:

举例：订单表orders 和 订单商品表order_items 

上面两个表是一对多的关系，一个订单必定关联很多个商品。

一般的查询方法如下：↓

serviceImpl:

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public PagedGridResult queryMyOrders(String userId,
                                         Integer orderStatus,
                                         Integer page,
                                         Integer pageSize) {

        Map<String, Object> map = new HashMap<>();
        map.put("userId", userId);
        if (orderStatus != null) {
            map.put("orderStatus", orderStatus);
        }

        PageHelper.startPage(page, pageSize);

        List<MyOrdersVO> list = ordersMapperCustom.queryMyOrders(map);

        return setterPagedGrid(list, page);
    }

    mapper.xml:

<resultMap id="myOrdersVO" type="com.alimather.pojo.vo.MyOrdersVO">
    <id column="orderId" property="orderId"/>
    <result column="createdTime" property="createdTime"/>
    <result column="payMethod" property="payMethod"/>
    <result column="realPayAmount" property="realPayAmount"/>
    <result column="postAmount" property="postAmount"/>
    <result column="orderStatus" property="orderStatus"/>
    <result column="isComment" property="isComment"/>

    <collection property="subOrderItemList"
                ofType="com.alimather.pojo.vo.MySubOrderItemVO">
      <result column="itemId" property="itemId"/>
      <result column="itemName" property="itemName"/>
      <result column="itemImg" property="itemImg"/>
      <result column="itemSpecId" property="itemSpecId"/>
      <result column="itemSpecName" property="itemSpecName"/>
      <result column="buyCounts" property="buyCounts"/>
      <result column="price" property="price"/>
    </collection>
  </resultMap>

    <select id="queryMyOrders" resultMap="myOrdersVO" parameterType="Map">
        SELECT
        od.id AS orderId,
        od.created_time AS createdTime,
        od.pay_method AS payMethod,
        od.real_pay_amount AS realPayAmount,
        od.post_amount AS postAmount,
        os.order_status AS orderStatus,
        od.is_comment AS isComment
        FROM
        orders od
        LEFT JOIN
        order_status os
        ON od.id = os.order_id
        LEFT JOIN
        order_items oi
        ON od.id = oi.order_id
        WHERE
        od.user_id = #{paramsMap.userId}
        AND
        od.is_delete = 0
        <if test="paramsMap.orderStatus != null">
            and os.order_status = #{paramsMap.orderStatus}
        </if>
        ORDER BY
        od.updated_time ASC
    </select>

     以上代码看着一点问题都没有。

但是！

实际查询的结果可能和你的想法背道而驰：

理想结果：每页显示10条订单。

实际结果：每页显示订单数量不等，或者6条或者5条，但是你会发现不管有多少条订单一定是有10个商品！

怎么办呢？？？

下面给出解决方案：

serviceImpl文件不变，修改对应的xml如下↓

<resultMap id="myOrdersVO" type="com.alimather.pojo.vo.MyOrdersVO">
    <id column="orderId" property="orderId"/>
    <result column="createdTime" property="createdTime"/>
    <result column="payMethod" property="payMethod"/>
    <result column="realPayAmount" property="realPayAmount"/>
    <result column="postAmount" property="postAmount"/>
    <result column="orderStatus" property="orderStatus"/>
    <result column="isComment" property="isComment"/>

    <collection property="subOrderItemList"
                select="getSubItems"
                column="orderId"
                ofType="com.alimather.pojo.vo.MySubOrderItemVO">
      <result column="itemId" property="itemId"/>
      <result column="itemName" property="itemName"/>
      <result column="itemImg" property="itemImg"/>
      <result column="itemSpecId" property="itemSpecId"/>
      <result column="itemSpecName" property="itemSpecName"/>
      <result column="buyCounts" property="buyCounts"/>
      <result column="price" property="price"/>
    </collection>
  </resultMap>

    <select id="queryMyOrders" resultMap="myOrdersVO" parameterType="Map">
        SELECT
        od.id as orderId,
        od.created_time as createdTime,
        od.pay_method as payMethod,
        od.real_pay_amount as realPayAmount,
        od.post_amount as postAmount,
        os.order_status as orderStatus,
        od.is_comment as isComment
        FROM
        orders od
        LEFT JOIN
        order_status os
        on od.id = os.order_id
        WHERE
        od.user_id = #{paramsMap.userId}
        AND
        od.is_delete = 0
        <if test="paramsMap.orderStatus != null">
            and os.order_status = #{paramsMap.orderStatus}
        </if>
        ORDER BY
        od.updated_time ASC
    </select>


    <select id="getSubItems" parameterType="String" resultType="com.alimather.pojo.vo.MySubOrderItemVO">

        select
            oi.item_id as itemId,
            oi.item_name as itemName,
            oi.item_img as itemImg,
            oi.item_spec_name as itemSpecName,
            oi.buy_counts as buyCounts,
            oi.price as price
        from
            order_items oi
        where
            oi.order_id = #{orderId}

    </select>

会发现有了两个查询标签体，同时
<collection property="subOrderItemList"
            select="getSubItems"
            column="orderId"
            ofType="com.alimather.pojo.vo.MySubOrderItemVO">

这个标签多了两个属性：

select：对应的下面一对多的“多”部分的查询语句，单独列出来进行查询。

column：参数是一对多的“一”部分的查询的列名，改参数作为上面select的查询参数。

如此以来，那个头疼的问题得以解决！