解决mybatis嵌套查询使用PageHelper分页不准确

springboot项目中嵌套查询使用PageHelper发现使用结果不正确，上PageHelper官网看了一下果然是有坑。。

就是下面这种映射会导致分页结果不正确，既然下面这种不行换了一种方式

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.imooc.mapper.OrdersMapperCustom">
  <resultMap id="MyOrdersList" type="com.imooc.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" />

    <!--
       collection 标签：用户定义关联的list集合
       property 对应属性名
       ofType 集合的类型
    -->
    <collection property="subOrderItemList" ofType="com.imooc.pojo.vo.MySubOrderItemVO">
      <result column="itemId"  property="itemId" />
      <result column="itemImg"  property="itemImg" />
      <result column="itemName"  property="itemName" />
      <result column="itemSpecName"  property="itemSpecName" />
      <result column="buyCounts"  property="buyCounts" />
      <result column="price"  property="price" />
    </collection>
  </resultMap>
  
  <select id="queryMyOrdersNotUse" resultMap="MyOrdersList" 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,
        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 byuCounts,
        oi.price AS price
    FROM
        orders od
        LEFT JOIN order_items oi ON od.id = oi.order_id
        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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
正确显示分页的代码：如下，把之前的一条嵌套查询拆分成两个查询，就等同于子查询的效果，
在collection 标签加上select属性 getSubItems 映射下面的sql，column用于传递参数

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.imooc.mapper.OrdersMapperCustom">
  <resultMap id="MyOrdersList" type="com.imooc.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" />

    <!--
       collection 标签：用户定义关联的list集合
       property 对应属性名
       ofType 集合的类型
    -->
    <collection property="subOrderItemList" select="getSubItems" column="orderId" ofType="com.imooc.pojo.vo.MySubOrderItemVO">
      <result column="itemId"  property="itemId" />
      <result column="itemImg"  property="itemImg" />
      <result column="itemName"  property="itemName" />
      <result column="itemSpecName"  property="itemSpecName" />
      <result column="buyCounts"  property="buyCounts" />
      <result column="price"  property="price" />
    </collection>
  </resultMap>

<select id="queryMyOrders" resultMap="MyOrdersList" 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
        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.imooc.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 byuCounts,
            oi.price AS price
        from
            order_items oi
        where
            oi.order_id = #{orderId}
    </select>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

————————————————
版权声明：本文为CSDN博主「那只小猿」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_41869361/article/details/116427385