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

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

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>

正确显示分页的代码：如下，把之前的一条嵌套查询拆分成两个查询，就等同于子查询的效果，
在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>