Mybatis | 06 Mybatis多表查询
Mybatis多表查询
1. 四种表的关系
1.1 一对多关系
用户和订单:一个用户可以有多个订单
1.2 多对一关系
订单和用户:多个订单可以属于同一个用户
Tips:但是对于特定的一个订单只能属于一个用户,所以多对一也可以理解为一对一
1.3 一对一关系
人和身份证号:一个人只能有一个身份证号
1.4 多对多关系
学生和老师:
-
一个学生可以有多个老师
-
一个老师也可以教多个学生
2. 示例:用户和账户的查询
特点:
一个用户可以有多个账户(一对多)
一个账户只能属于一个用户(一对一)
2.1 相关准备
2.1.1 数据库
分析:在账户表中使用外键添加用户的ID,使用户表和账户表之间具有一对多的关系
- 用户表
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(32) NOT NULL COMMENT '用户名',
`birthday` datetime default NULL COMMENT '生日',
`sex` char(1) default NULL COMMENT '性别',
`address` varchar(256) default NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
加入数据后…
- 账户表
CREATE TABLE `account` (
`ID` int(11) NOT NULL COMMENT '编号',
`UID` int(11) default NULL COMMENT '用户编号',
`MONEY` double default NULL COMMENT '金额',
PRIMARY KEY (`ID`),
KEY `FK_Reference_8` (`UID`),
CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
加入数据后…
2.1.2 代码
- 程序结构
2.1.2.1 用户和账户实体类
- 用户实体类
package org.example.domain;
public class User{
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
//省略了get和set方法
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", address='" + address + '\'' +
", sex='" + sex + '\'' +
'}';
}
}
- 账户实体类
package org.example.domain;
public class Account{
private Integer id;
private Integer uid;
private Double money;
//省略了get和set方法
@Override
public String toString() {
return "Account{" +
"id=" + id +
", uid=" + uid +
", money=" + money +
'}';
}
}
2.1.2.2 用户和账户的DAO层接口
- 用户DAO层接口
package org.example.dao;
public interface IUseDao{ }
- 账户DAO层接口
package org.example.dao;
public interface IAccountDao{ }
2.1.2.3 配置文件
- 主配置文件
<!--头文件省略-->
<configuration>
<!--properties配置省略-->
<!--domain包下的所有类都注册别名-->
<typeAliases>
<package name="org.example.domain"/>
</typeAliases>
<!--environments配置省略-->
<!--dao包下所有的接口都指定了映射器-->
<mappers>
<package name="org.example.dao"></package>
</mappers>
</configuration>
- 映射配置文件
- 用户的映射配置文件
<!--头文件省略-->
<mapper namespace="org.example.dao.IUserDao"></mapper>
- 账户的映射配置文件
<!--头文件省略-->
<mapper namespace="org.example.dao.IAccountDao"></mapper>
2.1.2.4 测试方法
public class test {
InputStream in = null;
SqlSessionFactory factory = null;
SqlSession session = null;
//根据DAO对象进行替换
IUserDao userDao = null;
@Before
public void init() throws IOException {
in = Resources.getResourceAsStream("SqlMapConfig.xml");
factory = new SqlSessionFactoryBuilder().build(in);
session = factory.openSession();
userDao = session.getMapper(IUserDao.class);
}
@After
public void destroy() throws IOException {
session.commit();
session.close();
in.close();
}
}
2.2 一对一查询
实现功能:在查询账户时获取所属用户的信息
在查询到用户的信息后有两种方式进行封装:
- 创建账户信息类的子类在其中添加属性封装用户信息
- 使用配置文件的方式定义封装用户信息的方式
2.2.1 使用创建子类的方式
2.2.1.1 创建账户子类
添加与用户信息有关的属性
package org.example.domain;
public class AccountUser extends Account{
private String username;
private String address;
//省略了get和set方法
@Override
public String toString() {
//先调用父类Account的toString方法
return super.toString() + " AccountUser{" +
"username='" + username + '\'' +
", address='" + address + '\'' +
'}';
}
}
2.2.1.2 账户DAO层接口
public interface IAccountDao{
List<AccountUser> findAll();
}
2.2.1.3 账户映射配置文件
<!--头文件省略-->
<mapper namespace="org.example.dao.IAccountDao">
<select id="findAll" resultType="accountuser">
select a.*,u.username,u.address from user u,account a where u.id=a.uid
</select>
</mapper>
2.2.1.4 查询结果
- SQL语句
select a.*,u.username,u.address from user u,account a where u.id=a.uid
查询结果
- 测试方法
@Test
public void findAllAccountUserTest() {
List<AccountUser> accountUsers = accountDao.findAll();
for(AccountUser accountUser : accountUsers){
System.out.println(accountUser);
}
}
查询结果
2.2.2 使用配置的方式
2.2.2.1 修改账户实体类
增加一对一映射,即用户实体类对象的引用
package org.example.domain;
public class Account{
private Integer id;
private Integer uid;
private Double money;
//一对一映射:包含映射对象的引用
private User user;
//省略了get和set方法
@Override
public String toString() {
return "Account{" +
"id=" + id +
", uid=" + uid +
", money=" + money +
'}';
}
}
2.2.2.2 账户DAO层接口
public interface IAccountDao{
List<Account> findAll();
}
2.2.2.3 账户映射配置文件
在resultMap标签中使用association标签
标签功能:对一对一映射的返回结果进行封装,即封装从属实体类对象
标签属性:
- javaType属性 指定所封装对象的全限定类名
<!--头文件省略-->
<mapper namespace="org.example.dao.IAccountDao">
<resultMap id="accountMap" type="account">
<id property="id" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!--一对一映射:封装用户实体类属性-->
<association property="user" column="uid" javaType="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
</association>
</resultMap>
<select id="findAll" resultMap="accountMap">
select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid = u.id
</select>
</mapper>
Tips 2.1 :两个坑
小心别掉坑:一定使用javaType属性指定封装对象的类型
错误示范:
<!--没有javaType属性-->
<association property="user" column="uid">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
</association>
报错…空指针异常,不知道要封装到哪里去
小心别掉坑:忘记使用resultMap而无法封装账户信息
错误示范:
<select id="findAll" resultType="account">
select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid = u.id
</select>
错误的结果:没得用户的信息
Tips 2.2:对column属性的理解
SQL语句执行后获取到了结果集,其中的列名可能被起了别名
例如:下面查询结果中的SQL语句给a.id起别名为aid,查询的结果集中该列就是aid形式
注意:在给column属性赋值时应该和最终查询到的结果集中的列名对应
2.2.2.4 查询结果
- SQL语句
select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid = u.id
查询结果
- 测试方法
@Test
public void findAllAccountTest() {
List<Account> accounts = accountDao.findAll();
for(Account account : accounts){
System.out.println(account);
System.out.println(account.getUser());
}
}
查询结果
2.3 一对多查询
实现功能:查询用户时获取该用户的所有账户信息
2.3.1 修改用户实体类
增加一对多映射,即账户实体类对象集合的引用
package org.example.domain;
public class User{
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
//一对多映射:映射对象集合的引用
List<Account> accounts;
//省略了get和set方法
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", address='" + address + '\'' +
", sex='" + sex + '\'' +
'}';
}
}
2.3.2 用户DAO层接口
public interface IUserDao{
List<User> findAll();
}
2.3.3 用户映射配置文件
在resultMap标签中使用 collection标签
标签功能:对一对多映射的返回结果进行封装,即封装从属实体类对象的集合
标签属性:
- ofType属性 指定集合中对象的全限定类名
<!--头文件省略-->
<mapper namespace="org.example.dao.IUserDao">
<resultMap id="userMap" type="user">
<!--封装User对象-->
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<!--一对多映射:封装账户实体类对象集合-->
<!--指定集合中元素的类型-->
<collection property="accounts" ofType="account">
<id property="id" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
</collection>
</resultMap>
<!--要显示所有的user信息所以使用左外连接-->
<select id="findAll" resultMap="userMap">
select * from user u left outer join account a on u.id=a.uid
</select>
</mapper>
2.3.4 查询结果
- SQL语句
select u.*,a.id as aid,a.uid,a.money from user u left outer join account a on u.id=a.uid;
查询结果
- 测试方法
@Test
public void findAllUserTest() {
List<User> users = userDao.findAll();
for (User user : users) {
System.out.println(user);
System.out.println(user.getAccounts());
}
}
查询结果
Tips:Mybatis会自动将查询的多个结果封装到集合中
3. 示例:用户和角色的查询
特点:
- 一个用户可以有多个角色
- 一个角色也可以赋予多个用户
3.1 相关准备
3.1.1 数据库
分析:角色表和用户表之间具有多对多的关系,需要使用中间表在其中添加两个表的主键作为外键
-
用户表:与用户账户中的相同
-
角色表
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;
加入数据后…
- 中间表
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;
加入数据后…
3.1.2 代码
- 程序结构
3.1.2.1 用户和角色实体类
- 用户实体类
package org.example.domain;
public class User{
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
//省略了get和set方法
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", address='" + address + '\'' +
", sex='" + sex + '\'' +
'}';
}
}
- 角色实体类
package org.example.domain;
public class Role{
private Integer roleId;
private String roleName;
private String roleDesc;
//省略了get和set方法
@Override
public String toString() {
return "Role{" +
"roleId=" + roleId +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
3.1.2.2 用户和角色的DAO层接口
- 用户DAO层接口
package org.example.dao;
public interface IUserDao{ }
- 角色DAO层接口
package org.example.dao;
public interface IRoleDao{ }
3.1.2.3 配置文件
- 主配置文件:和用户账户中的相同
- 映射配置文件
- 用户的映射配置文件
<!--头文件省略-->
<mapper namespace="org.example.dao.IUserDao"></mapper>
- 角色的映射配置文件
<!--头文件省略-->
<mapper namespace="org.example.dao.IRoleDao"></mapper>
3.1.2.4 测试方法
和用户账户中的相同
3.2 多对多查询
实现功能:
- 查询用户时获取用户的所有角色信息
- 查询角色时获取所有拥有该角色的用户信息
Tips:多对多查询对于两个实体类而言其实都是一对多查询,所有配置的过程和一对多相同
3.2.1 查询用户获取角色信息
3.2.1.1 修改用户实体类
增加多对多映射,即角色实体类对象集合的引用
public class User{
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
//多对多映射:包含映射对象集合的引用
private List<Role> roles;
//省略了get和set方法
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", address='" + address + '\'' +
", sex='" + sex + '\'' +
'}';
}
}
3.2.1.2 用户DAO层接口
public interface IUserDao{
List<User> findAll();
}
3.2.1.3 用户映射配置文件
和一对多查询一样使用collection标签
<!--头文件省略-->
<mapper namespace="org.example.dao.IUserDao">
<resultMap id="userMap" type="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
<collection property="roles" ofType="role">
<id property="roleId" column="rid"></id>
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
</collection>
</resultMap>
</mapper>
3.2.1.4 查询结果
- SQL语句
select u.*,r.id as rid,r.role_name,r.role_desc from user u
left outer join user_role ur on u.id=ur.uid
left outer join role r on ur.rid = r.id;
查询结果
- 测试方法
@Test
public void findAllUserTest(){
List<User> users = userDao.findAll();
for (User user : users) {
System.out.println(user);
System.out.println(user.getRoles());
}
}
查询结果
3.2.2 查询角色获取用户信息
配置方法与查询用户基本相同,SQL语句如下:
select u.*,r.id as rid,r.role_name,r.role_desc from role r
left outer join user_role ur on r.id=ur.rid
left outer join user u on u.id=ur.uid;