小粥学Mybatis(14)之多表操作----多对多
用户有多个角色
角色赋予多个用户
数据库环境
让用户表和角色表具有多对多的关系,
需要使用中间表,
中间表中包含各自的主键,
在中间表中是外键
创建角色表
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;
角色表插入数据
insert into `role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校');
创建中间表
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;
中间表插入数据
insert into `user_role`(`UID`,`RID`) values (41,1),(45,1),(41,2);
创建实体类
各自包含一个对方集合的引用
用户实体
/**
*@ClassName User
*@Description TODO
*@Author Yin.Liu8
*@Date 2021/3/1 10:43
*@Version 1.0
*/
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Role> roles;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public List<Role> getRoles() {
return roles;
}
public void setRoles(List<Role> roles) {
this.roles = roles;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", roles=" + roles +
'}';
}
}
角色实体
/**
*@ClassName Role
*@Description TODO
*@Author Yin.Liu8
*@Date 2021/3/2 13:45
*@Version 1.0
*/
public class Role implements Serializable {
private Integer roleId;
private String roleName;
private String roleDesc;
private List<User> users;
public Integer getRoleId() {
return roleId;
}
public void setRoleId(Integer roleId) {
this.roleId = roleId;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
public List<User> getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
@Override
public String toString() {
return "Role{" +
"roleId=" + roleId +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
", users=" + users +
'}';
}
}
DAO
IUserDAO
/**
* Description:用户的持久层接口
* <p>
* ClassName: IUserDAO
* date: 2021/3/1 10:47
*
* @author Yin.Liu
* @version 1.0
* @since JDK 1.8
*/
public interface IUserDAO {
/**
* 查询所有操作
* @return
*/
List<User> findAll();
}
IRoleDAO
/**
* Description:
* <p>
* ClassName: IRoleDAO
* date: 2021/3/2 13:48
*
* @author Yin.Liu
* @version 1.0
* @since JDK 1.8
*/
public interface IRoleDAO {
/**
* 查询所有角色
* @return
*/
List<Role> findAll();
}
mapper.xml
IRoleMapper.xml
<?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="xyz.zhouzhousag.dao.IRoleDAO">
<resultMap id="roleMap" type="role">
<id property="roleId" column="id"></id>
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
</resultMap>
<select id="findAll" resultMap="roleMap">
select * from role
</select>
</mapper>
IUserMapper.xml
<?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必须是dao接口的全限定类名-->
<mapper namespace="xyz.zhouzhousag.dao.IUserDAO">
<select id="findAll" resultType="user">
select * from user
</select>
</mapper>
测试类
/**
*@ClassName RoleTest
*@Description TODO
*@Author Yin.Liu8
*@Date 2021/3/1 11:25
*@Version 1.0
*/
public class RoleTest {
private InputStream in;
private SqlSession sqlSession;
private IUserDAO userDAO;
private IAccountDAO accountDAO;
private IRoleDAO roloDAO;
@Before //用于测试方法之前使用
public void init() throws Exception {
//1.读取配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建SqlSessionFactory工厂
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//3.使用工厂生产 SqlSession对象
sqlSession = factory.openSession(true);
//4.使用SqlSession创建DAO接口的代理对象
userDAO = sqlSession.getMapper(IUserDAO.class);
accountDAO = sqlSession.getMapper(IAccountDAO.class);
roloDAO = sqlSession.getMapper(IRoleDAO.class);
}
@After //用于测试方法之后
public void destroy() throws Exception {
//6.释放资源
sqlSession.close();
in.close();
}
/**
* 查询所有
* @param
*/
@Test
public void mybatisTest() {
List<Role> accounts = roloDAO.findAll();
for (Role Role :accounts){
System.out.println(Role.toString());
}
}
}
查询角色获取角色下的用户信息
修改IRoleMapper.xml
<?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="xyz.zhouzhousag.dao.IRoleDAO">
<resultMap id="roleMap" type="role">
<id property="roleId" column="rid"></id>
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
<collection property="users" ofType="user">
<id property="id" column="uid"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="roleMap">
select
t1.ID rid,
t1.ROLE_NAME ,
t1.ROLE_DESC,
t3.id uid,
t3.username,
t3.birthday,
t3.sex,
t3.address
from
`role` t1
left join
user_role t2
on t1.ID = t2.RID
left join
`user` t3
on t2.UID = t3.id
</select>
</mapper>
查询用户获取用户下的角色信息
修改IUserMapper.xml
<?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必须是dao接口的全限定类名-->
<mapper namespace="xyz.zhouzhousag.dao.IUserDAO">
<resultMap id="userMap" type="user">
<id property="" column=""></id>
<id property="id" column="uid"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></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>
<select id="findAll" resultMap="userMap">
select
t1.ID rid,
t1.ROLE_NAME ,
t1.ROLE_DESC,
t3.id uid,
t3.username,
t3.birthday,
t3.sex,
t3.address
from
`user` t3
left join
user_role t2
on t3.id = t2.UID
left join
`role` t1
on t2.RID = t1.ID
</select>
</mapper>