pagehelper插件嵌套查询分页异常

最新推荐文章于 2025-04-16 11:05:05 发布

朝酒晚舞双修

最新推荐文章于 2025-04-16 11:05:05 发布

阅读量570

点赞数

文章标签： mybatis

本文链接：https://blog.youkuaiyun.com/hhfhhf66/article/details/109520512

版权

本文介绍如何通过拆分MyBatis中的复杂SQL查询来提高查询效率。主要涉及将嵌套查询转换为独立查询，并利用resultMap中的collection标签进行数据组装。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先将mapper.xml中的sql语句进行更改

更改前语句

<select id="queryMyOrdersDoNotUse" resultMap="myOrdersVO">
        SELECT
        o.id as orderId,
        o.created_time as createdTime,
        o.pay_method as payMethod,
        o.real_pay_amount as realPayAmount,
        o.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_id as itemSpecId,
        oi.item_spec_name as itemSpecName,
        oi.buy_counts as buyCounts,
        oi.price as price
        FROM
        `orders` o
        left JOIN
        order_status os
        on
        o.id = os.order_id
        LEFT JOIN
        order_items oi
        on
        oi.order_id = o.id
        where
        o.user_id = #{paramMap.userId}
        and
        o.is_delete = 0
        <if test="paramMap.orderStatus != null">
            and os.order_status = #{paramMap.orderStatus}
        </if>
        ORDER BY o.updated_time ASC


    </select>

将嵌套查询的sql拆开,首先在resultMap标签中的collection 标签中添加属性,
select=“getSubItems” 代表查询方法的id
column=“orderId” 代表传入查询方法的参数

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

将sql语句拆分,分别查询数据

    <select id="queryMyOrders" resultMap="myOrdersVO">
        SELECT
            o.id as orderId,
            o.created_time as createdTime,
            o.pay_method as payMethod,
            o.real_pay_amount as realPayAmount,
            o.post_amount as postAmount,
            os.order_status as orderStatus
        FROM
            `orders` o
        left JOIN
            order_status os
        on
            o.id = os.order_id
        where
            o.user_id = #{paramMap.userId}
        and
            o.is_delete = 0
            <if test="paramMap.orderStatus != null">
                and os.order_status = #{paramMap.orderStatus}
            </if>
        ORDER BY o.updated_time ASC


    </select>

    <select id="getSubItems" parameterType="String" resultType="com.yf.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>