一对多嵌套分页查询

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>