mybatis学习——多表查询(三)

Mybatis 多表查询之多对多

实现 Role 到 User 多对多

通过前面的学习，我们使用 Mybatis 实现一对多关系的维护。多对多关系其实我们看成是双向的一对多关系。

1.1 用户与角色的关系模型

用户与角色的多对多关系模型如下：

 在 MySQL 数据库中添加角色表，用户角色的中间表。

角色表

用户角色中间表

1.2 业务要求及实现 SQL

需求： 

　　1. 实现查询所有角色并且加载它所分配的用户信息。

　　2. 实现查询所有用户并且加载它所分配的角色信息。

分析： 

　　查询角色我们需要用到Role表，但角色分配的用户的信息我们并不能直接找到用户信息，而是要通过中 间表(USER_ROLE 表)才能关联到用户信息。反之，查询用户我们需要用到User表，查找对应角色也要用到中间表。 下面是实现的 SQL 语句：

//查询role表并通过中间表找出角色对应的用户信息
select r.*,u.id as uid,u.username,u.birthday,u.sex,u.address from role r
          left outer join user_role ur on r.id=ur.rid
          left outer join user u on ur.uid=u.id

//查询user表并通过中间表找出用户对应的角色信息
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

1.3 编写角色实体类

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 + '\'' +
                '}';
    }
}

1.4 编写用户实体类

public class User implements Serializable {

    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    //多对多的关系映射：一个用户可以赋予多个角色
    private List<Role> roles;

    private List<Account> accounts;

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

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

1.5 编写 Role 、User持久层接口

public interface IRoleDao {
    /**
     * 查询所有角色
     */
    List<Role> findAll();
}

============================

public interface IUserDao {
    /**
     * 查询所有用户，同时获取出每个用户下的所有角色信息
     */
    List<User> findAll2();
}

1.6 编写映射文件

IRoleDao.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.churujianghudezai.dao.IRoleDao">

    <resultMap id="roleUserMap" type="role">
        <id column="id" property="roleId"/>
        <result column="role_name" property="roleName"/>
        <result column="role_desc" property="roleDesc"/>
        <collection property="users" ofType="user">
            <id column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="birthday" property="birthday"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="roleUserMap">
        select r.*,u.id as uid,u.username,u.birthday,u.sex,u.address from role r
          left outer join user_role ur on r.id=ur.rid
          left outer join user u on ur.uid=u.id
    </select>
</mapper>

IUserDao.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.churujianghudezai.dao.IUserDao">
    <resultMap id="userRoleMap" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="address" property="address"/>
        <collection property="roles" ofType="role">
            <id column="rid" property="roleId"/>
            <result column="role_name" property="roleName"/>
            <result column="role_desc" property="roleDesc"/>
        </collection>
    </resultMap>

    <select id="findAll2" resultMap="userRoleMap">
        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
    </select>
    
</mapper>

1.7 编写测试类

RoleTest测试类：

public class RoleTest {

    private InputStream in;
    private SqlSessionFactory factory;
    private SqlSession session;
    private IRoleDao roleDao;

    @Before//在测试方法执行前执行
    public void init() throws Exception{
        //1.读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建构建者对象
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        //3.创建SqlSession工厂对象
        factory = builder.build(in);
        //4.创建SqlSession对象
        session = factory.openSession();
        //5.创建Dao的代理对象
        roleDao = session.getMapper(IRoleDao.class);
    }

    @After//测试方法执行完成之后执行
    public void destroy() throws Exception{
        //7.提交事务
        session.commit();
        //8.释放资源
        session.close();
        in.close();
    }

    @Test
    public void testfindAll(){
        List<Role> roles = roleDao.findAll();
        for (Role role:roles){
            System.out.println("------每个角色对应的用户------");
            System.out.println(role);
            System.out.println(role.getUsers());
        }
    }

}

结果为：

UserTest测试类：

public class UserTest {

    private InputStream in;
    private SqlSessionFactory factory;
    private SqlSession session;
    private IUserDao userDao;

    @Before//在测试方法执行前执行
    public void init() throws Exception{
        //1.读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建构建者对象
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        //3.创建SqlSession工厂对象
        factory = builder.build(in);
        //4.创建SqlSession对象
        session = factory.openSession();
        //5.创建Dao的代理对象
        userDao = session.getMapper(IUserDao.class);
    }

    @After//测试方法执行完成之后执行
    public void destroy() throws Exception{
        //7.提交事务
        session.commit();
        //8.释放资源
        session.close();
        in.close();
    }

    @Test
    public void testfindAll2(){
        List<User> us = userDao.findAll2();
        for (User u:us){
            System.out.println("------每个用户对应角色------");
            System.out.println(u);
            System.out.println(u.getRoles());
        }
    }

}

结果为：