演示多对多需要创建一个新的角色表,也就是演示用户和角色之间的多对多关系,因为:

  • 一个用户可以拥有多个角色
  • 一个角色可可以赋给多个用户

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;

insert  into `role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校');

然后创建中间表:

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);

由于用户表之前已经创建过了,故这里省略。

2.创建实体类

各自的实体类中都要引用对方的集合对象。

角色d额实体类:

    private Integer roleId;
    private String roleName;
    private String roleDesc;

    //配置多对多关系映射:一个角色可以赋予多个用户
    private List<User> users;

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = 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;
    }

    @Override
    public String toString() {
        return "Role{" +
                "roleId=" + roleId +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }
}

用户的实体类:

package com.eastnotes.domian;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * 用户的实体类
 */
public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    // 一对多关系映射:主表实体应该包含从表实体的集合引用
    private List<Role> roles;

    public List<Role> getRoles() {
        return roles;
    }

    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }

    public Integer getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public String getSex() {
        return sex;
    }

    public String getAddress() {
        return address;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

3.创建DAO

创建role的dao文件:

public interface IRoleDao {

    /**
     * 查询所有角色
     * @return
     */
    List<Role> findAll();
}

3.创建配置文件

  • 查询角色时,得到拥有该角色的所有用户
<?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.eastnotes.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="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>
    </resultMap>

    <!--查询所有-->
    <select id="findAll" resultMap="roleMap">
        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
    </select>
</mapper>
  • 查询用户时得到相应角色信息:
<?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="com.eastnotes.dao.IUserDao">

    <!--映射配置文件开启二级缓存-->
    <cache/>

    <!--定义User的UserMap-->
    <resultMap id="userRoleMap" type="user">
        <!--封装user数据-->
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <result property="birthday" column="birthday"></result>

        <!--配置user对象中accounts集合映射-->
        <collection property="roles"  ofType="role">
            <id property="roleId" column="id"></id>
            <result property="roleName" column="role_name"></result>
            <result property="roleDesc" column="role_desc"></result>
        </collection>
    </resultMap>

    <!--配置查询所有-->
    <select id="findAll" resultMap="userRoleMap" useCache="true">
        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 u.id = ur.uid
    </select>
</mapper>

4.测试类

  • 测试查询角色时,得到拥有该角色的所有用户
/**
 * 测试查询所有角色
 */
@Test
public void testFindAll(){
    List<Role> roles = roleDao.findAll();
    for(Role role : roles){
        System.out.println(role);
        System.out.println(role.getUsers());
    }
}

输出结果为:

Role{roleId=1, roleName='院长', roleDesc='管理整个学院'}
[User{id=41, username='老王', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}, User{id=45, username='传智播客', birthday=Sun Mar 04 12:04:06 CST 2018, sex='男', address='北京金燕龙'}]
Role{roleId=2, roleName='总裁', roleDesc='管理整个公司'}
[User{id=41, username='老王', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}]
Role{roleId=3, roleName='校长', roleDesc='管理整个学校'}
[]

  • 测试查询用户时,得到拥有该用户的所有角色
/**
 * 查询所有用户,同时查询所对应的角色信息
 */
@Test
public void testFindAllRoles(){
    List<User> users = userDao.findAll();
    for(User u : users){
        System.out.println(u);
        System.out.println(u.getRoles());
    }
}

输出j结果为:

User{id=41, username='老王', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}
[Role{roleId=41, roleName='院长', roleDesc='管理整个学院'}]
User{id=45, username='传智播客', birthday=Sun Mar 04 12:04:06 CST 2018, sex='男', address='北京金燕龙'}
[Role{roleId=45, roleName='院长', roleDesc='管理整个学院'}]
User{id=42, username='小二王', birthday=Fri Mar 02 15:09:37 CST 2018, sex='女', address='北京金燕龙'}
[Role{roleId=42, roleName='null', roleDesc='null'}]
User{id=43, username='小二王', birthday=Sun Mar 04 11:34:34 CST 2018, sex='女', address='北京金燕龙'}
[Role{roleId=43, roleName='null', roleDesc='null'}]
User{id=46, username='老王', birthday=Wed Mar 07 17:37:26 CST 2018, sex='男', address='北京'}
[Role{roleId=46, roleName='null', roleDesc='null'}]
User{id=48, username='小马宝莉', birthday=Thu Mar 08 11:44:00 CST 2018, sex='女', address='北京修正'}
[Role{roleId=48, roleName='null', roleDesc='null'}]
User{id=51, username='xiangdong', birthday=Fri Nov 01 15:30:23 CST 2019, sex='男', address='北京市·朝阳区'}
[Role{roleId=51, roleName='null', roleDesc='null'}]
User{id=52, username='xiangdong', birthday=Sat Nov 02 21:30:44 CST 2019, sex='男', address='北京市·朝阳区'}
[Role{roleId=52, roleName='null', roleDesc='null'}]

5.总结

到这里,Mybatis的多表查询就告一段落,总而言之,要想使用Mybatis完成多表查询,关键点有如下几点:

  • 确定查什么:这一步是确定你要使用多表查询中的一对一还是一对多,还是多对多
  • 确定怎么查:这一步有两个步骤:
    • 构建SQL语句,先用数据库管理系统实现你想要的数据查询
    • 编写映射配置文件,配置select标签、resultMap标签
  • 确定怎么封装:查询出来的结果需要有实体类进行封装
    • 一对一的话需要在实体类中包含对应实体类对象的引用
    • 一对多或多对多的话,需要在实体类中包含相实体类的集合引用