前言
最近碰到了Mybatis一对多查询的场景,在这里总结对比下常见的两种实现方式。
本文以常见的订单表和订单详情表来举例说明;
数据库表准备(sql在文末)
订单表 tbl_order
订单详情表 tlb_order_detail
ps: 一个订单关联多个订单详情,通过order_no订单号关联;
实例演示
方法一:联合查询ResultMap映射
sql直接关联查询,然后结果集通过resultMap的collection映射
例如 查询订单列表,包括订单详情
Order.java 中新增字段orderDetailList,用于存详情列表
public class Order {
private Integer id;
private String orderNo;
private Date orderTime;
private Date payTime;
private String remark;
/**订单详情*/
private List<OrderDetail> orderDetailList;
//省略get、set
OrderMapper.java 新增查询方法
List<Order> queryOrderList(Map map);
OrderMapper.xml
<resultMap id="BaseResultMap" type="com.chouxiaozi.mybatisdruid.entity.Order" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="order_no" property="orderNo" jdbcType="VARCHAR" />
<result column="order_time" property="orderTime" jdbcType="TIMESTAMP" />
<result column="pay_time" property="payTime" jdbcType="TIMESTAMP" />
<result column="remark" property="remark" jdbcType="VARCHAR" />
<collection property="orderDetailList" ofType="com.chouxiaozi.mybatisdruid.entity.OrderDetail">
<id column="d_id" property="id" jdbcType="INTEGER" />
<result column="d_order_no" property="orderNo" jdbcType="VARCHAR" />
<result column="good_name" property="goodName" jdbcType="VARCHAR" />
<result column="good_id" property="goodId" jdbcType="INTEGER" />
<result column="good_count" property="goodCount" jdbcType="INTEGER" />
</collection>
</resultMap>
<select id="queryOrderList" resultMap="BaseResultMap">
SELECT
o.*, d.id as d_id,d.order_no as d_order_no,d.good_name,d.good_id,d.good_count
FROM
tbl_order o
LEFT JOIN tbl_order_detail d ON d.order_no = o.order_no
where 1=1
<if test="orderNo != null and orderNo != ''">
and o.order_no = #{orderNo}
</if>
ORDER BY o.order_time desc
</select>
查询结果展示
[
{
"id": 2,
"orderNo": "DD000002",
"orderTime": "2021-05-09 12:25:57",
"payTime": "2021-05-09 12:25:59",
"remark": "2号订单",
"orderDetailList": [
{
"id": 5,
"orderNo": "DD000002",
"goodName": "耳机",
"goodId": 5,
"goodCount": 1
},
{
"id": 4,
"orderNo": "DD000002",
"goodName": "手机",
"goodId": 4,
"goodCount": 1
}
]
},
{
"id": 1,
"orderNo": "DD000001",
"orderTime": "2021-05-09 12:25:37",
"payTime": "2021-05-09 12:25:41",
"remark": "1号订单",
"orderDetailList": [
{
"id": 2,
"orderNo": "DD000001",
"goodName": "饮料",
"goodId": 2,
"goodCount": 2
},
{
"id": 1,
"orderNo": "DD000001",
"goodName": "瓜子",
"goodId": 1,
"goodCount": 1
},
{
"id": 3,
"orderNo": "DD000001",
"goodName": "矿泉水",
"goodId": 3,
"goodCount": 2
}
]
}
]
原理:sql直接关联查询,然后结果集通过resultMap的collection映射,将order_detail表对应的字段映射到orderDetailList字段中。
优点:条件查询方便;无论是订单表还是详情表如果要进行一些条件过滤的话,非常方便,直接写在where中限制就行。
不足:因为是先关联查询,后映射;如果需要进行分页查询的话,这种方式就无法满足。主表2条数据,详情表5条数据,关联之后就是10条,无法得主表进行分页;解决方法,就是先给主表套个子查询limit分页后,然后结果集再跟详情表进行关联查询;
方法二:子查询映射
通过resultMap中collection标签的select属性去执行子查询
还以查询订单列表为例
OrderMapper.java
List<Order> queryOrderList2(Map map);
OrderMapper.xml
<!--主查询的resultMap-->
<resultMap id="BaseResultMap2" type="com.chouxiaozi.mybatisdruid.entity.Order" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="order_no" property="orderNo" jdbcType="VARCHAR" />
<result column="order_time" property="orderTime" jdbcType="TIMESTAMP" />
<result column="pay_time" property="payTime" jdbcType="TIMESTAMP" />
<result column="remark" property="remark" jdbcType="VARCHAR" />
<!--select子查询, column 传给子查询的参数-->
<collection property="orderDetailList" ofType="com.chouxiaozi.mybatisdruid.entity.OrderDetail"
select="queryDetail" column="order_no">
</collection>
</resultMap>
<!--主查询的sql-->
<select id="queryOrderList2" resultMap="BaseResultMap2">
SELECT
o.*
FROM
tbl_order o
where 1=1
<if test="orderNo != null and orderNo != ''">
and o.order_no = #{orderNo}
</if>
ORDER BY o.order_time desc
</select>
<!--子查询的resultMap-->
<resultMap id="detailResuleMap" type="com.chouxiaozi.mybatisdruid.entity.OrderDetail">
<id column="id" property="id" jdbcType="INTEGER" />
<result column="order_no" property="orderNo" jdbcType="VARCHAR" />
<result column="good_name" property="goodName" jdbcType="VARCHAR" />
<result column="good_id" property="goodId" jdbcType="INTEGER" />
<result column="good_count" property="goodCount" jdbcType="INTEGER" />
</resultMap>
<!--子查询的sql-->
<select id="queryDetail" resultMap="detailResuleMap">
SELECT
*
FROM
`tbl_order_detail` where order_no = #{order_no}
</select>
查询结果同上个例子一样;
原理:通过collection的select方法去调用子查询;所需参数通过column传递;
优点:无论是分页还是普通查询都能满足;主表增加过滤条件也很方便,直接在主查询的sql中增加where条件就行
缺点:子查询不好增加过滤条件;column只能传递主表已有的字段。下面提供解决方式;
ps:column传递多个参数 column=“{prop1=col1,prop2=col2}”
例如:实际场景中,详情表有个状态字段,只展示状态正常的详情,需要过滤详情记录。
本例子没有状态字段,就查询订单列表,详情中不展示瓜子,即详情记录中过滤掉good_id = 1的;
在上个例子基础上修改如下:
调用层传参
Map map = new HashMap();
map.put("goodId", 1);
orderMapper.queryOrderList2(map);
orderMapper.xml中增加传参过滤
展示结果如下:详情中已成功过滤掉瓜子;记住,过滤子查询不会影响主表记录;
[
{
"id": 2,
"orderNo": "DD000002",
"orderTime": "2021-05-09 12:25:57",
"payTime": "2021-05-09 12:25:59",
"remark": "2号订单",
"orderDetailList": [
{
"id": 4,
"orderNo": "DD000002",
"goodName": "手机",
"goodId": 4,
"goodCount": 1
},
{
"id": 5,
"orderNo": "DD000002",
"goodName": "耳机",
"goodId": 5,
"goodCount": 1
}
]
},
{
"id": 1,
"orderNo": "DD000001",
"orderTime": "2021-05-09 12:25:37",
"payTime": "2021-05-09 12:25:41",
"remark": "1号订单",
"orderDetailList": [
{
"id": 2,
"orderNo": "DD000001",
"goodName": "饮料",
"goodId": 2,
"goodCount": 2
},
{
"id": 3,
"orderNo": "DD000001",
"goodName": "矿泉水",
"goodId": 3,
"goodCount": 2
}
]
}
]
总结
方式 | 联合查询映射 | 子查询映射 |
---|---|---|
原理 | sql查询完成后再通过resultmap映射结果 | 主表的数据集循环调用子查询 |
分页 | 不支持分页查询,主表套子查询也能实现 | 支持分页 |
条件过滤 | 方便条件过滤 | 传参也能实现,复杂参数例如list不好传递给子查询 ;子查询过滤不影响主表数据 |
sql
CREATE TABLE `tbl_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` varchar(100) DEFAULT NULL COMMENT '订单号',
`order_time` datetime DEFAULT NULL COMMENT '下单时间',
`pay_time` datetime DEFAULT NULL COMMENT '支付日期',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
CREATE TABLE `tbl_order_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` varchar(100) DEFAULT NULL COMMENT '订单号',
`good_name` varchar(100) DEFAULT NULL COMMENT '商品名称',
`good_id` int(11) DEFAULT NULL COMMENT '商品id',
`good_count` int(11) DEFAULT NULL COMMENT '商品数量',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单详情表';
INSERT INTO `tbl_order` (`order_no`, `order_time`, `pay_time`, `remark`) VALUES ( 'DD000001', '2023-03-24 09:19:18', '2023-03-24 09:19:22', '1号订单');
INSERT INTO `tbl_order` (`order_no`, `order_time`, `pay_time`, `remark`) VALUES ('DD000002', '2023-03-24 09:19:46', '2023-03-24 09:19:49', '2号订单');
INSERT INTO `tbl_order_detail` (`order_no`, `good_name`, `good_id`, `good_count`) VALUES ( 'DD000001', '瓜子', 1, 1);
INSERT INTO `tbl_order_detail` (`order_no`, `good_name`, `good_id`, `good_count`) VALUES ( 'DD000001', '饮料', 2, 2);
INSERT INTO `tbl_order_detail` (`order_no`, `good_name`, `good_id`, `good_count`) VALUES ('DD000001', '矿泉水', 3, 2);
INSERT INTO `tbl_order_detail` (`order_no`, `good_name`, `good_id`, `good_count`) VALUES ('DD000002', '手机', 4, 1);
INSERT INTO `tbl_order_detail` ( `order_no`, `good_name`, `good_id`, `good_count`) VALUES ('DD000002', '耳机', 5, 1)