mybatis高级映射之一对多映射 collection 集合的嵌套查询 一个用户对应多个角色，每个角色对应多个权限

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

    <select id="selectPrivilegeByRoleId" resultMap="privilegeMap">
        select p.*
        from sys_privilege p
        inner join  sys_role_privilege rp on rp.privilege_id = p.id
        where role_id = #{roleId}
    </select>

<resultMap id="rolePrivilegeListMapSelect" extends="roleMap"
               type="com.example.simple.model.SysRole">
        <collection property="privilegeList"
                    fetchType="lazy"
                    column="{roleId = id}"
                    select="com.example.simple.mapper.PrivilegeMapper.selectPrivilegeByRoleId"/>
    </resultMap>

    <select id="selectRoleByUserId" resultMap="rolePrivilegeListMapSelect">
        select
        r.id ,
        r.role_name ,
        r.enabled,
        r.create_by,
        r.create_time
        from sys_role r
        inner join sys_user_role ur on ur.role_id = r.id
        where ur.user_id = #{userId}

    </select>

<resultMap id="userRoleListMapSelect" extends="userMap"
               type="com.example.simple.model.SysUser">
        <collection property="roleList"
                    fetchType="lazy"
                    column="{userId = id}"
                    select="com.example.simple.mapper.RoleMapper.selectRoleByUserId"/>
    </resultMap>
    <select id="selectAllUserAndRolesSelect" resultMap="userRoleListMapSelect">
        select
        u.id,
        u.user_name,
        u.user_password,
        u.user_email ,
        u.user_info ,
        u.head_img,
        u.create_time
        from sys_user u
        where u.id = #{id}

    </select>

/*
    * 通过嵌套查询获取指定用户的信息以及用户的角 色和权限信息
    * */
    SysUser selectAllUserAndRolesSelect(Long id);

@Test
    public void testSelectAllUserAndRolesSelect(){
        SqlSession sqlSession = getSqlSession();
        try{
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser user = userMapper.selectAllUserAndRolesSelect(1L);
            System.out.println("用户名：" + user.getUserName());
            for(SysRole role : user.getRoleList() ){
                System.out.println("角色名：" + role.getRoleName());
                for(SysPrivilege privilege : role.getPrivilegeList()){
                    System.out.println("权限："+ privilege.getPrivilegeName());
                }
            }

        }finally {
            sqlSession.close();
        }

    }