一对多嵌套分页查询

淡淡的烟草味﹌ 2022-12-16 13:29 200阅读 0赞

public class MyOrdersVO {
    
        private String orderId;
        private Date createdTime;
        private Integer payMethod;
        private Integer realPayAmount;
        private Integer postAmount;
        private Integer isComment;
        private Integer orderStatus;
        // 嵌套orderItem
        private List<MySubOrderItemVO> subOrderItemList;
    
    }

SQL需要分开写, 否则使用分页插件后的总数有问题

<mapper namespace="com.java.mapper.OrdersDao">
    
        <resultMap id="myOrdersVO" type="com.java.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"/>
            <result column="isComment" property="isComment"/>
    
            <collection property="subOrderItemList" select="getSubItems" column="orderId" ofType="com.java.pojo.vo.MySubOrderItemVO">
                <result column="itemId" property="itemId"/>
                <result column="itemName" property="itemName"/>
                <result column="itemImg" property="itemImg"/>
                <result column="itemSpecName" property="itemSpecName"/>
                <result column="buyCounts" property="buyCounts"/>
                <result column="price" property="price"/>
            </collection>
        </resultMap>
    
        <select id="getSubItems" parameterType="String" resultType="com.java.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 buyCounts,
            oi.price as price
          from
            order_items oi
          where
            oi.order_id = #{orderId}
    
        </select>
    
        <select id="queryMyOrders" resultMap="myOrdersVO" 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
            ORDER BY
            od.updated_time ASC
        </select>
    
    </mapper>