7. MyBatis多表查询 - 一对一 - 一对多 - 多对多
前言
在前面的篇章，我们已经熟悉了单表查询，下面我们来看看如何进行 多表查询。
数据准备
create database if not exists `db03`;
USE `db03`;
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` datetime default NULL COMMENT '生日',
`sex` varchar(10) default NULL COMMENT '性别',
`address` varchar(256) default NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user` */
insert into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (41,'老王','2019-05-27 17:47:08','男','北京'),(42,'王小二','2019-03-02 15:09:37','女','北京金燕龙'),(43,'老李','2019-03-04 11:34:34','女','北京修正'),(45,'传智播客','2019-03-04 12:04:06','男','北京金燕龙'),(46,'王小二','2018-09-07 17:37:26','男','北京TBD'),(48,'小马宝莉','2019-03-08 11:44:00','女','北京修正');
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT NULL,
`ordertime` datetime DEFAULT NULL,
`money` double DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `orders` */
insert into `orders`(`id`,`uid`,`ordertime`,`money`) values (1,41,'2019-05-20 02:58:02',999.5),(2,45,'2019-02-14 07:58:00',1399),(3,41,'2019-06-01 21:00:02',1666);
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(11) NOT NULL COMMENT '编号',
`role_name` varchar(30) default NULL COMMENT '角色名称',
`role_desc` varchar(60) default NULL COMMENT '角色描述',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `role` */
insert into `role`(`ID`,`role_name`,`role_desc`) values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校');
/*Table structure for table `user_role` */
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`uid` int(11) NOT NULL COMMENT '用户编号',
`rid` int(11) NOT NULL COMMENT '角色编号',
PRIMARY KEY (`uid`,`rid`),
KEY `FK_Reference_10` (`rid`),
CONSTRAINT `FK_Reference_10` FOREIGN KEY (`rid`) REFERENCES `role` (`id`),
CONSTRAINT `FK_Reference_9` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user_role` */
insert into `user_role`(`uid`,`rid`) values (41,1),(45,1),(41,2);
表与表之间的关系如下：
1589091428909
多表关系
在上面我们已经准备好了 sql 表结构，在 sql 中具有 一对一、一对多、多对多 三种关系。而如果在 mybatis 的角度来看，却只有 一对一、一对多 两种关系，这是为什么呢？
主要是因为 mybatis 是基于 javabean 来进行关系定义的，而 javabean 不需要 多对多 这种中间表的操作，直接根据属性 定义 集合 即可完成 一对多 的情况。
# 多表关系
1. sql中
a. 一对一
b. 一对多 : 用户 和 订单
c. 多对多 : 用户 和 角色
2. mybatis中
a. 一对一
b. 一对多
下面是 javabean 一对多的 示例：
# user表和orders表: 一对多
public class User implements Serializable {
private Integer id;
private String username;
private String birthday;
private String sex;
private String address;
private List<Orders> list; //一个用户可以拥有多个订单
}
public class Orders implements Serializable {
private Integer id;
private String ordertime;
private Double money;
private Integer uid; //外键
private User user; // 一个订单属于一个用户
}
# 1. mybatis看问题的角度不同
// a. 从user表的角度看问题 : 一对多(一个用户有多个订单)
// b. 从orders表的角度看问题 : 一对一(一个订单只能属于一个用户)
# 2. mybatis解决问题的方式不同
// a. sql中是用外键建立表关系
// b. mybatis中用属性
现在我们大概了解了 sql 与 mybatis 多表关系之间的不同，下面来看看 mybatis 如何实现的。
一对一
一对一查询模型
用户表和订单表的关系为，一个用户有多个订单（一对多），一个订单只从属于一个用户（一对一）
一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户
1589091522985
1. 实体和表映射关系
下面我们首先来写一下查询订单以及对应用户的SQL，如下：
# 查询id=1的订单以及对应的用户
select * from orders o inner join user u
on o.uid = u.id
where o.id = 1;
执行如下：
image-20210318233255370
而 Orders 类对应查询结果的对应关系如下：
1596503528686
2. Orders实体类
首先我们编写 Orders 订单类，用属性 uid 来作为 User 表 id 的外键，用属性 User 类 来保存 对应查询出来的 User 对象。
image-20210318234527759
public class Orders implements Serializable {
private Integer id;
private String ordertime;
private Double money;
private Integer uid; //外键
private User user; // 订单对用户: 一对一
}
3. OrderMapper接口
下面再编写一个 一对一 的查询方法。
image-20210318234706869
public interface OrdersMapper {
/*
# 查询id=?的订单以及对应的用户
select * from orders o inner join user u
on o.uid = u.id
where o.id = ?;
1. 参数类型: Integer id
2. 返回值类型 Orders
* */
Orders findOrderByIdWithUser(Integer id);
}
4. OrdersMapper.xml
下面再映射xml 来编写 一对一 查询的 sql
image-20210319000516013
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
实体类的映射文件
namespace 指定接口的类全名
-->
<mapper namespace="com.lijw.dao.OrdersMapper">
<!--
autoMapping = true
自动映射: 当javabean的属性名和结果集的字段名一致, 自动映射
-->
<resultMap id="myorder" type="orders" autoMapping="true">
<!--
id标签 : 主键字段映射(必须要写)
result标签: 非主键字段映射 (同名可不写,推荐写)
-->
<!--
最左原则: 如果结果集中存在同名字段,默认使用左边的那个
-->
<id property="id" column="id"/>
<result property="ordertime" column="ordertime"/>
<!--
重点在: orders.user 属性 跟 结果集某一段(user表查出来)的映射
1. 一对一: association (使用)
2. 一对多: collection
# association 标签
1. properties : 属性名
2. javaType : 此属性的类型
子标签 : 设置user和结果集之间的映射
注意 最左原则
-->
<association property="user" javaType="user" autoMapping="true">
<id property="id" column="uid"/>
<result property="username" column="username"/>
</association>
</resultMap>
<!--
1. 直接写resultType=orders, 返回类型=orders
2. 要写成 resultMap : 结果集映射
指定查询结果集 跟 javabean (orders) 之间的映射关系
-->
<select id="findOrderByIdWithUser" resultMap="myorder">
select * from orders o inner join user u
on o.uid = u.id
where o.id = #{id}
</select>
</mapper>
5.sqlMapConfig.xml 配置 映射文件
image-20210319000619430
<!-- 加载其他的映射文件 -->
<mappers>
<mapper resource="com.mapper/OrdersMapper.xml"/>
<mapper resource="com.mapper/UserMapper.xml"/>
</mappers>
配置完毕之后，才能读取到 映射关系。
6. 测试
下面编写一个测试方法：
image-20210319000757835
可以从结果来看，成功读取到了订单信息以及对应的 user 对象信息。
public class OrdersMapperTest {
@Test
public void test01(){
SqlSession session = MyBatisUtil.getSqlSession();
OrdersMapper mapper = session.getMapper(OrdersMapper.class);
// 订单编号 = 1
Orders orders = mapper.findOrderByIdWithUser(1);
System.out.println("订单:" + orders);
MyBatisUtil.commitAndClose(session);
}
}
一对多
一对多查询模型
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单
1589095235966
1. 实体和表关系
SELECT *,o.id AS oid FROM `user` u INNER JOIN orders o ON u.`id` = o.`uid` WHERE u.`id`=41
执行如下：
image-20210319001313527
而查询结果对应的 User 类的属性关系：
1589095492155
2. User实体类
image-20210319001603615
public class User {
private Integer id;
private String username;
private String birthday;
private String sex;
private String address;
// 一个用户具有多个订单
private List<Order> list;
}
3. UserMapper接口
image-20210319002020981
public interface UserMapper {
/*
* # 查询id=? 的用户以及拥有的订单
select * from user u inner join orders o
on u.id = o.uid
where u.id = ?;
* 1. 参数类型: Integer
* 2. 返回值类型: User
* */
User findUserByIdWithOrders(Integer uid);
}
4. UserMapper.xml
image-20210319002419221
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lijw.dao.UserMapper">
<resultMap id="myuser" type="user" autoMapping="true">
<id property="id" column="id"/>
<!--
重点: user.list<Orders> list 属性 跟 结果集的一部分进行映射
关系: 一个用户对应多个订单 (一对多)
1. association : 一对一
2. collection : 一对多
# collection 标签
1. property : 指定属性名
2. ofType : 指定集合的元素类型
注意: 结果集同名字段的最左原则
-->
<collection property="list" ofType="orders" autoMapping="true">
<id property="id" column="oid"/>
</collection>
</resultMap>
<select id="findUserByIdWithOrders" resultMap="myuser">
select u.*,o.id as oid,o.uid,o.ordertime,o.money from user u inner join orders o
on u.id = o.uid
where u.id = #{uid}
</select>
</mapper>
5. 测试
image-20210319002737154
@Test
public void test15(){
SqlSession session = MyBatisUtil.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
// 查询用户id=41的用户以及拥有的订单
User user = mapper.findUserByIdWithOrders(41);
System.out.println(user);
MyBatisUtil.commitAndClose(session);
}
多对多（由二个一对多组成）
多对多查询的模型
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色
在mybatis中多对多实现，跟一对多步骤是一样，区别就在于sql语句
1589096723649
1. 实体和表关系
-- 查询角色id=1 的角色以及对应的用户
select * from role r inner join user_role ur inner join user u
on r.id = ur.rid and u.id = ur.uid
where r.id = 1;
执行如下：
image-20210319003251397
2. User和Role实体
image-20210319003603698
public class Role implements Serializable {
private Integer id;
private String role_name;
private String role_desc;
private List<User> list; // 一个角色对应多个用户: 一对多
}
image-20210319003642314
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Orders> list; // 用户对订单: 一对多
}
3. RoleMapper接口
image-20210319003843692
public interface RoleMapper {
/*
# 查询角色id=? 的角色以及对应的用户
select * from role r inner join user_role ur inner join user u
on r.id = ur.rid and u.id = ur.uid
where r.id = ?;
1. 参数类型: Integer
2. 返回类型: Role
* */
Role findRoleByIdWithUsers(Integer rid);
}
4. RoleMapper.xml
image-20210319004323455
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
实体类的映射文件
namespace 指定接口的类全名
-->
<mapper namespace="com.lijw.dao.RoleMapper">
<resultMap id="myrole" type="role" autoMapping="true">
<id property="id" column="id"/>
<result property="role_desc" column="role_desc"/>
<collection property="list" ofType="user" autoMapping="true">
<id property="id" column="uid"/>
</collection>
</resultMap>
<select id="findRoleByIdWithUsers" resultMap="myrole">
select * from role r inner join user_role ur inner join user u
on r.id = ur.rid and u.id = ur.uid
where r.id = #{rid}
</select>
</mapper>
5. sqlMapConfig.xml 配置 映射文件
image-20210319004127635
<!-- 加载其他的映射文件 -->
<mappers>
<mapper resource="com.mapper/OrdersMapper.xml"/>
<mapper resource="com.mapper/UserMapper.xml"/>
<mapper resource="com.mapper/RoleMapper.xml"/>
</mappers>
6. 测试
image-20210319004414408
@Test
public void test01(){
SqlSession session = MyBatisUtil.getSqlSession();
RoleMapper mapper = session.getMapper(RoleMapper.class);
//查询角色id=1的角色以及对应的用户
Role role = mapper.findRoleByIdWithUsers(1);
System.out.println(role);
MyBatisUtil.commitAndClose(session);
}
小结
# mybatis中的多表联合查询
0. 从当前javabean角度考虑关系
1. 一对一配置：使用<resultMap>+<association>做配置
association:
property：关联的实体类属性名
javaType：关联的实体类型（别名）
2. 一对多配置：使用<resultMap>+<collection>做配置
collection：
property：关联的集合属性名
ofType：关联的集合元素类型（别名）
多对多的配置跟一对多很相似，难度在于SQL语句的编写。
