创建二张测试表：订单表，订单关联的商品表
订单表实体类
订单商品表实体类
一、第一种分页：利用mybatis集合属性collection一对多映射嵌套查询，（执行多次sql）跟hibernate一对多映射差不多。
<!--订单分页begin-->
<!-- 查询结果集映射 -->
<resultMap id="queryOrderListMap" type="com.aag3.web.system.dto.DemoOrder">
<result column="order_no" property="orderNo"/>
<result column="user_id" property="userId"/>
<result column="contact_name" property="contactName"/>
<result column="contact_phone" property="contactPhone"/>
<result column="status" property="status"/>
<result column="total_price" property="totalPrice"/>
<result column="create_time" property="createTime"/>
<collection property="orderGoodslist" ofType="com.aag3.web.system.dto.DemoOrderGoods" select="queryOrderChild" column="order_no">
</collection>
</resultMap>
<!--主表查询-->
<select id="queryOrderList" resultMap="queryOrderListMap">
SELECT order_no, user_id, contact_name, contact_phone, status, total_price, create_time FROM demo_order
</select>
<!--子表查询-->
<select id="queryOrderChild" resultType="com.aag3.web.system.dto.DemoOrderGoods">
SELECT order_goods_id, goods_name, price, count FROM demo_order_goods WHERE order_no=#{orderNo}
</select>
<!--订单分页end-->
使用分页插件所以没有写LIMIT, 查询结果：
{
"pageNo":1,
"pageSize":2,
"totalCount":5,
"totalPages":3，
"result":[
{
"orderNo":"00001",
"userId":"1",
"contactName":"诸葛亮",
"contactPhone":"123456",
"status":0,
"totalPrice":99.00,
"createTime":1595674470000,
"orderGoodslist":[
{
"orderGoodsId":1,
"goodsName":"长剑",
"price":350.00,
"count":2
}
]
},
{
"orderNo":"00002",
"userId":"1",
"contactName":"曹操",
"contactPhone":"111111",
"status":0,
"totalPrice":299.00,
"createTime":1595674498000,
"orderGoodslist":[
{
"orderGoodsId":2,
"goodsName":"长剑",
"price":350.00,
"count":3
},
{
"orderGoodsId":3,
"goodsName":"无尽之刃",
"price":3800.00,
"count":1
}
]
}
],
}
二、第二种分页：纯关联查询结果再分组映射（这种容易导致最后一组订单商品数据丢失）
<!-- 结果集分组映射begin --> <resultMap id="queryOrderListMap2" type="com.aag3.web.system.dto.DemoOrder"> <result column="order_no" property="orderNo"/> <result column="user_id" property="userId"/> <result column="contact_name" property="contactName"/> <result column="contact_phone" property="contactPhone"/> <result column="status" property="status"/> <result column="total_price" property="totalPrice"/> <result column="create_time" property="createTime"/> <collection property="orderGoodslist" ofType="com.aag3.web.system.dto.DemoOrderGoods"> <result column="order_goods_id" property="orderGoodsId"/> <result column="goods_name" property="goodsName"/> <result column="price" property="price"/> <result column="count" property="count"/> </collection> </resultMap> <!--订单分组分页(不能以数据limit为维度分页,会导致最后一条订单数据商品缺失)--> <select id="queryOrderList2" resultMap="queryOrderListMap2"> SELECT t.order_no, t.user_id, t.contact_name, t.contact_phone, t.status, t.total_price, t.create_time, og.order_goods_id, og.goods_name, og.price, og.count FROM demo_order t LEFT JOIN demo_order_goods og on og.order_no = t.order_no </select> <!--结果集分组映射end-->
查询参数：页码=1，每页显示=2。
查询结果：第二条订单是显示了一个商品数据，订单不完整
{
"pageNo":1,
"pageSize":2,
"totalCount":10,
"totalPages":5，
"result":[
{
"orderNo":"00001",
"userId":"1",
"contactName":"诸葛亮",
"contactPhone":"123456",
"status":0,
"totalPrice":99.00,
"createTime":1595674470000,
"orderGoodslist":[
{
"orderGoodsId":1,
"goodsName":"长剑",
"price":350.00,
"count":2
}
]
},
{
"orderNo":"00002",
"userId":"1",
"contactName":"曹操",
"contactPhone":"111111",
"status":0,
"totalPrice":299.00,
"createTime":1595674498000,
"orderGoodslist":[
{
"orderGoodsId":2,
"goodsName":"长剑",
"price":350.00,
"count":3
}
]
}
]
}
三、第三种分页：以时间维度关联查询所有结果集再分组映射（这种只实行一次sql数据不会丢失，时间间隔按具体业务量调整）
<!-- 结果集分组映射begin -->
<resultMap id="queryOrderListMap2" type="com.aag3.web.system.dto.DemoOrder">
<result column="order_no" property="orderNo"/>
<result column="user_id" property="userId"/>
<result column="contact_name" property="contactName"/>
<result column="contact_phone" property="contactPhone"/>
<result column="status" property="status"/>
<result column="total_price" property="totalPrice"/>
<result column="create_time" property="createTime"/>
<collection property="orderGoodslist" ofType="com.aag3.web.system.dto.DemoOrderGoods">
<result column="order_goods_id" property="orderGoodsId"/>
<result column="goods_name" property="goodsName"/>
<result column="price" property="price"/>
<result column="count" property="count"/>
</collection>
</resultMap>
<!-- 以时间维度分组映射 -->
<select id="queryOrderList3" resultMap="queryOrderListMap2">
SELECT t.order_no, t.user_id, t.contact_name, t.contact_phone, t.status, t.total_price, t.create_time,
og.order_goods_id, og.goods_name, og.price, og.count
FROM demo_order t
LEFT JOIN demo_order_goods og on og.order_no = t.order_no
WHERE t.create_time >= #{beginTime} AND t.create_time < #{endTime}
</select>
<!-- 结果集分组映射begin -->
查询结果：查询页码=1，每页显示数据=2，第二个订单是显示了一个商品数据，订单不完整
查询参数：beginTime=2020-07-25，endTime=2020-07-26
查询结果：跟第一种方式一样完整显示订单
[
{
"orderNo":"00001",
"userId":"1",
"contactName":"诸葛亮",
"contactPhone":"123456",
"status":0,
"totalPrice":99.00,
"createTime":1595674470000,
"orderGoodslist":[
{
"orderGoodsId":1,
"goodsName":"长剑",
"price":350.00,
"count":2
}
]
},
{
"orderNo":"00002",
"userId":"1",
"contactName":"曹操",
"contactPhone":"111111",
"status":0,
"totalPrice":299.00,
"createTime":1595674498000,
"orderGoodslist":[
{
"orderGoodsId":2,
"goodsName":"长剑",
"price":350.00,
"count":3
},
{
"orderGoodsId":3,
"goodsName":"无尽之刃",
"price":3800.00,
"count":1
}
]
}
]
第一种方式最常用吧，因为一般订单数据都不会变化，只是状态变化，查询一次都会放进redis缓存