mybatis高级映射——集合的嵌套结果映射 之一对多映射之一个用户拥有多个角色，每个角色拥有多个权限

一个用户拥有多个角色，每个角色拥有多个权限

一、先实现一个用户拥有多个角色

1. 首先在SysUser中加入SysRole字段

private List<SysRole> roleList;

    public List<SysRole> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<SysRole> roleList) {
        this.roleList = roleList;
    }

 2. RoleMapper.xml中：

<resultMap id="roleMap" type="com.example.simple.model.SysRole">
        <result property="id" column="id"/>
        <result property="roleName" column="role_name"/>
        <result property="enabled" column="enabled"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
    </resultMap>

3. UserMapper.xml中：

 <resultMap id="userMap" type="com.example.simple.model.SysUser">
        <id property="id" column="id"/>
<!--            <id property="userPassword" column="user_password"/>-->
        <result property="userName" column="user_name"/>
        <result property="userPassword" column="user_password"/>
        <result property="userMail" column="user_mail"/>
        <result property="userInfo" column="user_info"/>
        <result property="headImg" column="head_img" jdbcType="BLOB"/>
        <result property="createTime"
                column="creat_time" jdbcType="TIMESTAMP"/>
    </resultMap>

 <resultMap id="userRoleListMap" extends="userMap"
               type="com.example.simple.model.SysUser">
        <collection property="roleList" columnPrefix="role_"
                    resultMap="com.example.simple.mapper.RoleMapper.roleMap"/>

    </resultMap>

<select id="selectAllUsersAndRoles" resultMap="userRoleListMap">
        select
            u.id,
            u.user_name,
            u.user_password,
            u.user_email ,
            u.user_info ,
            u.head_img,
            u.create_time ,
            r.id role_id,
            r.role_name  role_role_name,
            r.enabled role_enabled ,
            r.create_by role_create_by ,
            r.create_time role_create_time
            from sys_user u
            inner join sys_user_role ur on u.id = ur.user_id
            inner join sys_role r on ur.role_id = r.id
    </select>

4. UserMapper.java中添加接口 ：

 /*
    * 获取所有用户以及对应的所有角色
    * */
    List<SysUser> selectAllUsersAndRoles();

5. 添加测试代码：

@Test
    public void testSelectAllUsersAndRoles(){
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<SysUser> userList = userMapper.selectAllUsersAndRoles();
            System.out.println("用户数为：" + userList.size());
            for (SysUser user: userList
                 ) {
                System.out.println("用户名：" + user.getUserName());
                for (SysRole role: user.getRoleList()
                     ) {
                    System.out.println("角色名：" + role.getRoleName());
                }
            }

        }finally {
            sqlSession.close();
        }

    }

测试结果如下：

 二、接下来实现一个用户拥有多个角色，每个角色又拥有多个权限 

1. 首先在SysUser中加入roleList字段

private List<SysRole> roleList;

    public List<SysRole> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<SysRole> roleList) {
        this.roleList = roleList;
    }

2. 在SysRole中加入privilegeList字段

 /*
    * 角色包含的权限列表
    * */
    List<SysPrivilege> privilegeList;

3. PrivilegeMapper.xml中：

<resultMap id="privilegeMap"
               type="com.example.simple.model.SysPrivilege">
        <id property="id" column="id"/>
        <result property="privilegeName" column="privilege_name"/>
        <result property="privilegeUrl" column="privilege_url"/>
    </resultMap>

4. RoleMapper.xml中：

<resultMap id="rolePrivilegeListMap" extends="roleMap"
               type="com.example.simple.model.SysRole">
        <collection property="privilegeList" columnPrefix="privilege_"
                    resultMap="com.example.simple.mapper.PrivilegeMapper.privilegeMap"/>
    </resultMap>

<select id="selectAllRoleAndPrivileges" resultMap="rolePrivilegeListMap">
        select
        r.id ,
        r.role_name ,
        r.enabled,
        r.create_by,
        r.create_time,
        p.id privilege_id,
        p.privilege_name privilege_privilege_name,
        p.privilege_url privilege_privilege_url,
        from sys_role r
        inner join sys_role_privilege rp on rp.privilege_id = r.id
        inner join sys_privilege p on p.id = rp.privilege_id
    </select>

5. UserMapper.xml中：

<resultMap id="userRoleListMap1" extends="userMap"
               type="com.example.simple.model.SysUser">
        <collection property="roleList" columnPrefix="role_"
                    resultMap="com.example.simple.mapper.RoleMapper.rolePrivilegeListMap"/>

    </resultMap>

<select id="selectAllUsersAndRoles1" resultMap="userRoleListMap1">
        select
            u.id,
            u.user_name,
            u.user_password,
            u.user_email ,
            u.user_info ,
            u.head_img,
            u.create_time ,
            r.id role_id,
            r.role_name  role_role_name,
            r.enabled role_enabled ,
            r.create_by role_create_by ,
            r.create_time role_create_time,
            p.id role_privilege_id ,
            p.privilege_name role_privilege_privilege_name ,
            p.privilege_url role_privilege_privilege_url
            from sys_user u
            inner join sys_user_role ur on u.id = ur.user_id
            inner join sys_role r on ur.role_id = r.id
            inner join sys_role_privilege rp on rp.role_id = r.id
            inner join sys_privilege p on p.id = rp.privilege_id

    </select>

6. UserMapper.java中添加接口：

/*
    * 获取所有用户对应的所有角色，以及每个角色对应的权限
    * */
    List<SysUser> selectAllUsersAndRoles1();

7. 添加测试方法：

@Test
    public void testSelectAllUsersAndRoles1(){
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<SysUser> userList = userMapper.selectAllUsersAndRoles1();
            System.out.println("用户数为：" + userList.size());
            for (SysUser user: userList
            ) {
                System.out.println("用户名：" + user.getUserName());
                for (SysRole role: user.getRoleList()
                ) {
                    System.out.println("角色名：" + role.getRoleName());
                    role.getPrivilegeList();
                    for(SysPrivilege privilege:role.getPrivilegeList()){
                        System.out.println("权限名：" + privilege.getPrivilegeName());
                    }

                }
            }

        }finally {
            sqlSession.close();
        }

    }

测试结果：

 

 至此，告一段落。