级联/嵌套查询

在真实业务背景中的系统中，对于用户的认证、授权以及鉴权的问题，通常不只依靠一张用户表来完成。一般还涉及用户角色表，用户-角色关系表，权限表，以及角色-权限关系表。此时查询数据库，除了使用关联查询还有一种方式，级联查询，也是这篇博客的主题。

级联查询也叫嵌套查询，在sql中，一个SELECT-FROM-WHERE语句成为一个查询语句块，那么将一个语句块嵌套在另一个语句块的WHERE字句的条件中的查询称为嵌套查询，下文将通过上述讲述的五表案例来具体描述。

构建数据库表

1.用户基本信息表

该表中共有4个字段，如下图所示，其中盐值是使用SHA加密算法生成的随机串。

 2.角色表

用于描述一组权限的集合。

 3.用户角色关系表

记录用户所对应的角色，其中一个用户可以有多个角色，一个角色也可以被多个用户持有。

4.权限表

描述使用系统功能所需要的权限

5.权限-角色关系表

用于描述角色权限之间的对应关系，其中一个角色可以对应多个权限，一个权限也可以为多个角色持有

 构建实体类

实体类包含用户类、权限类以及角色类，其中 用户类中包含一个HashSet类型的属性用于存放角色集合，角色类中包含一个HashSet类型的属性用于存放权限。

查询数据库

1.分析一下

在这里，无论是查询数据库还是封装结果集都基于注解的方式进行。首先分析一下：

在根据用户唯一标时Id查询用户详细信息时，需要封装一个roles属性，这个属性详细信息查询是一定要经过角色表的。在查询的时候也不能盲目的查，一定是有条件的，这个条件就是我们要查那个用户的详细信息，就要查这个用户所对应的角色有哪些。所以这个时候就需要根据用户-角色关系表找到二者的对应关系。对于角色属性中的权限属性也是一样的，不再赘述。

2.实现

首先是UserDao中的根据用户id查询用户详细信息

    @Select("select * from pe_user where id=#{id}")
    @Results({
            @Result(column = "id",property ="id" ),
            @Result(column = "username",property ="username" ),
            @Result(column = "password",property ="password" ),
            @Result(column = "salt",property ="salt" ),
            @Result(column = "id",property ="roles",many = @Many(select = "com.my.springsecurity3.dao.RoleDao.findRoleByUserId"))
    })
    User findUserDetailById(Integer id);

对于上述代码有这样几个点需要注意一下：

1.这里使用@Results注解封装结果集，其中一个@Result注解代表一个字段，该注解中的column属性对应表中的字段，而property属性对应实体类中的字段。

2.对于最后一个属性roles,它所对应的@result注解中的column代表将要调用方法要传下去的参数是表中字段为id的值。也就是说我们在调用RoleDao中的方法封装该字段的值时，需要将该用户的id传下去，用以查询。

3.最后一个@Result中的many属性，它主要用于多对多的关联查询，相当于xml文件中封装结果集时使用collection标签。

其次是RoleDao中的根据用户id查询用户详细信息

  @Select("select * from pe_role where id in((select role_id from pe_user_role where user_id=#{userId}))")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "name",property = "name"),
            @Result(column = "description",property = "description"),
            @Result(column = "id",property = "permissions"
                    ,many = @Many(select = "com.my.springsecurity3.dao.PermissionDao.findPermissionByRoleId")),

    })
    List<Role> findRoleByUserId(Integer userId);

 翻译一下上面的sql查询语句：

从pe_role表中查询一个id(这里的id是角色表的id)在某个范围内的所有数据，这个某个范围是指从用户角色关系表中根据用户id查到的一组role_id。

最后是PermissionDao中的根据用户id查询用户详细信息

 @Select("select * from pe_permission where id in((select permission_id from pe_role_permission where role_id=#{roleId}))")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "name", property = "name"),
            @Result(column = "code", property = "code"),
            @Result(column = "description", property = "description")
    })
    List<Permission> findPermissionByRoleId(Integer roleId);

测试一下

    @Test
    public void testSelect(){
        User userDetail = userDao.findUserDetailById(2);
        System.out.println(userDetail);
    }

 运行结果