mybatisplus 一对多(collection)分页解决方案
简单以订单、商品来举例说明,即一个订单包含多个商品
背景1:查询订单表的内容,商品表不参与条件查询,用懒加载形式
<resultMap id="OrderInfoListResultMap" type="*******OrderInfoVo">
<id column="id" property="id"/>
<result column="order_name" property="orderName"/>
<result column="order_no" property="orderNo"/>
...........省略若干
<collection property="goodsList" javaType="ArrayList" ofType="****OrderGoodsVo"
select="getOrderGoodsVoByOrderId" column="{orderId = id}"/>
</resultMap>
<select id="findBuyerOrderListByPage" resultMap="OrderInfoListResultMap">
SELECT *FROM `order_info`
</select>
<select id="getOrderGoodsVoByOrderId" resultType="*******OrderGoodsVo">
SELECT * FROM `order_goods` WHERE `order_id` = #{orderId}
</select>
背景2:查询订单表的内容,商品表要作为查询条件参与查询,例如要根据商品名称查询出订单信息
<resultMap id="OrderInfoListResultMap" type="*******OrderInfoVo">
<id column="id" property="id"/>
<result column="order_name" property="orderName"/>
<result column="order_no" property="orderNo"/>
...........省略若干
<collection property="goodsList" javaType="ArrayList" ofType="****OrderGoodsVo"
select="getOrderGoodsVoByOrderId" column="{orderId = id,goodsName = goodsName}"/>
</resultMap>
<select id="findBuyerOrderListByPage" resultMap="OrderInfoListResultMap">
select temp.* from (
SELECT distinct (og.order_id) as maskkey,
oi.*,#{orderInfo.goodsName} as goodsName
FROM
`order_info` oi LEFT JOIN `order_goods` og ON oi.`id` = og.`order_id`
<where>
<if test="orderInfo.goodsName != null and orderInfo.goodsName != ''">and og.goods_name like concat('%',#{orderInfo.goodsName},'%')
</if>
</where>
) temp
</select>
<select id="getOrderGoodsVoByOrderId" resultType="*******OrderGoodsVo">
SELECT * FROM `order_goods` WHERE `order_id` = #{orderId}
<if test="goodsName != null and goodsName != ''">
and goods_name like concat('%',#{goodsName},'%')
</if>
</select>
重点:
- 将goodsName传递到子查询中
- 借用一对多的特性,利用左查询联合筛选订单信息,保证分页条数的准确性