MYBATIS多层嵌套查询

基于权限管理时的用户角色权限处理。

实体类：

　　1.User

public class User{

private Integer uid;

private String username;

private String password;

private Set roles=new HashSet<>();

2.Role

public class Role {

private Integer rid;

private String rname;

private Set modules=new HashSet<>();

3.Module

Mapper 文件。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

<resultMap id="userMap2" type="com.example.mapper.entity.User">

       <id property="uid" column="uid"/>

       <result property="username" column="username"/>

       <result property="password" column="password"/>

       <collection property="roles" ofType="roleMap" column="uid" select="findRole"></collection>

   </resultMap>

   <resultMap id="roleMap" type="com.example.mapper.entity.Role">

       <id property="rid" column="rid"/>

       <result property="rname" column="rname"/>

       <collection property="modules" ofType="com.example.mapper.entity.Module" column="rid" select="findModule">

       </collection>

   </resultMap>

     <select id="findUserByUserName" parameterType="java.lang.String" resultMap="userMap2">

         SELECT  * from USER WHERE username=#{username}

     </select>

   <select id="findRole" parameterType="java.lang.Integer" resultMap="roleMap">

       SELECT r.* from Role r LEFT  JOIN  user_role ur on ur.rid =r.rid where ur.uid=#{uid}

   </select>

   <select id="findModule" parameterType="java.lang.Integer" resultType="com.example.mapper.entity.Module">

       SELECT m.* from module m LEFT  JOIN  module_role mr on mr.mid =m.mid where mr.rid=#{rid}

   </select>

　　测试结果。

User{uid=1, username='hlhdidi', password='123', roles=[Role{rid=1, rname='admin',modules=[Module{mid=2, mname='delete'}, Module{mid=3, mname='query'}, Module{mid=4, mname='update'}, Module{mid=1, mname='add'}]}]}