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

最新推荐文章于 2022-12-28 23:24:49 发布

那只小猿

最新推荐文章于 2022-12-28 23:24:49 发布

阅读量1.2k

点赞数 2

分类专栏： mybatis 文章标签： mybatis java

本文链接：https://blog.csdn.net/weixin_41869361/article/details/116427385

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

博客讲述了在SpringBoot项目中使用PageHelper进行分页查询时遇到的问题，即嵌套查询导致分页结果不正确。作者发现PageHelper官网提到的坑，并提供了修正方案，将嵌套查询拆分为两个独立的查询，通过在collection标签中使用select属性引用单独的SQL来获取子项数据，从而解决了问题。

摘要由CSDN通过智能技术生成

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>