目录
十一、Mybatis 多表查询之多对多查询
11.1 准备工作
这里我们新建一个角色表(role),并且创建 Role.java 、RoleDao.xml 等文件。
11.1.1 角色表
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),(48,1),(41,2);
11.1.2 Role.java
package com.cpz.domain;
import java.util.ArrayList;
import java.util.List;
public class Role {
private Integer id;
private String roleName;
private String roleDesc;
@Override
public String toString() {
return "Role{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
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;
}
}
11.2 多对多查询,查询角色同时关联用户
修改Role.java
package com.cpz.domain;
import java.util.ArrayList;
import java.util.List;
public class Role {
private Integer id;
private String roleName;
private String roleDesc;
private List<User> users = new ArrayList<User>();
@Override
public String toString() {
return "Role{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
", users=" + users +
'}';
}
public List<User> getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
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;
}
}
RoleDao.java
package com.cpz.dao;
import com.cpz.domain.Role;
import java.util.List;
public interface RoleDao {
public List<Role> findRoleUser();
}
RoleDao.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="com.cpz.dao.RoleDao">
<resultMap id="roleUserMap" type="role">
<id column="rid" property="id"></id>
<result column="role_name" property="roleName"></result>
<result column="role_desc" property="roleDesc"></result>
<collection property="users" ofType="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</collection>
</resultMap>
<select id="findRoleUser" resultMap="roleUserMap">
SELECT u.*,r.id rid,r.role_name,r.role_desc FROM role r LEFT JOIN user_role ur ON r.id = ur.rid LEFT JOIN USER u ON ur.uid = u.id
</select>
</mapper>
注意:sql语句如果有换行,需要在sql的后面尽量加一个空格
TestUser.java
@Test
public void findRoleUser(){
List<Role> list = roleDao.findRoleUser();
for (Role role : list) {
System.out.println(role);
}
}
11.3 多对多查询,查询用户同时关联角色
User.java
package com.cpz.domain;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Role> roles = new ArrayList<Role>();
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
", roles=" + roles +
'}';
}
public List<Role> getRoles() {
return roles;
}
public void setRoles(List<Role> roles) {
this.roles = 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;
}
}
UserDao.java
package com.cpz.dao;
import com.cpz.domain.User;
import java.util.List;
public interface UserDao {
public List<User> findUserRole();
}
UserDao.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="com.cpz.dao.UserDao">
<resultMap id="userRoleMap" type="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<collection property="roles" ofType="role">
<id column="rid" property="id"></id>
<result column="role_name" property="roleName"></result>
<result column="role_desc" property="roleDesc"></result>
</collection>
</resultMap>
<select id="findUserRole" resultMap="userRoleMap">
SELECT u.*,r.id rid,r.role_name,r.role_desc FROM USER u LEFT JOIN user_role ur ON u.id = ur.uid LEFT JOIN role r ON ur.rid = r.id
</select>
</mapper>
TestUser.java
@Test
public void findUserRole(){
List<User> list = userDao.findUserRole();
for (User user : list) {
System.out.println(user);
}
}