解决pageHelper不支持嵌套分页查询

（请先查看springboot项目，自定义xml，查询多表封装返回_p＆f°的博客-CSDN博客 一对多内容）

查询结果为一对多：一个 订单vo 包含多个 子商品voList

此时pagerHelper不支持嵌套查询，使用这种方法查询出来的结果是按照子商品vo内容分页的，并不是根据订单vo分页。显然，我们原本意思是想按照订单数目分页

一开始的xml如下：

<!--一对多：一个 订单vo 包含多个 子商品voList-->
  <!--pagerHelper不支持嵌套查询，使用这种方法查询出来的结果是按照子商品vo内容分页的，并不是根据订单vo分页-->
  <select id="queryMyOrders(NotUse)" parameterType="map" resultMap="myOrdersVO">
    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,
      od.is_comment as isComment,
      os.order_status as orderStatus,
      oi.item_id as itemId,
      oi.item_name as itemName,
      oi.item_img as itemImg,
      oi.item_spec_id as itemSpecId,
      oi.item_spec_name as itemSpecName,
      oi.buy_counts as buyCounts,
      oi.price as price
    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>

解决办法：

把查询语句分开，先查询出 订单vo 的结果，再根据 订单vo 查询出 子商品vo。

也就是懒查询，先查询主表的结果集，再根据主表的结果查询子表的结果。

<?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.xpf.mapper.OrdersMapperCustom" >
  <resultMap id="myOrdersVO" type="com.xpf.pojo.vo.MyOrdersVO" >
    <id column="orderId" property="orderId"  />
    <result column="createdTime" property="createdTime"  />
    <result column="payMethod" property="payMethod"  jdbcType="INTEGER"/>
    <result column="realPayAmount" property="realPayAmount"  />
    <result column="orderStatus" property="orderStatus"  />
    <result column="postAmount" property="postAmount"  />
    <result column="isComment" property="isComment"  />

    <collection property="subOrderItemList"
                select="getSubItems"
                column="orderId"
                ofType="com.xpf.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" jdbcType="INTEGER" />
      <result column="price" property="price" jdbcType="INTEGER" />
    </collection>
  </resultMap>

  <select id="queryMyOrders" parameterType="map" resultMap="myOrdersVO">
    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,
      od.is_comment as isComment,
      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>

  <!--将queryMyOrders(NotUse)拆开。先查询主表，再查询子表-->
  <select id="getSubItems" parameterType="String" resultType="com.xpf.pojo.vo.MySubOrderItemVO">
    select
      oi.item_id as itemId,
      oi.item_name as itemName,
      oi.item_img as itemImg,
      oi.item_spec_id as itemSpecId,
      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>

  <!--此select没有删除留做参考-->
  <!--一对多：一个 订单vo 包含多个 子商品voList-->
  <!--pagerHelper不支持嵌套查询，使用这种方法查询出来的结果是按照子商品vo内容分页的，并不是根据订单vo分页-->
  <select id="queryMyOrders(NotUse)" parameterType="map" resultMap="myOrdersVO">
    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,
      od.is_comment as isComment,
      os.order_status as orderStatus,
      oi.item_id as itemId,
      oi.item_name as itemName,
      oi.item_img as itemImg,
      oi.item_spec_id as itemSpecId,
      oi.item_spec_name as itemSpecName,
      oi.buy_counts as buyCounts,
      oi.price as price
    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>
</mapper>