本文共 4513 字，大约阅读时间需要 15 分钟。

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>

转载地址：http://hdng.baihongyu.com/