MYBATIS多层嵌套查询

原网址：https://www.cnblogs.com/hnsunlong/p/7195594.html

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

实体类：

User

public class User{
	 private Integer uid;
	 private String username;
	 private String password;
	 private Set<Role> roles=new HashSet<>();
}

Role

public class Role {
    private Integer rid;
    private String rname;
    private Set<Module> modules=new HashSet<>();
}

Module

public class Module {
    private Integer mid;
    private String mname;
}

Mapper文件

<?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.zzhua.dao.UserMapper">
	<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>
</mapper>

测试结果

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'}
						 ]
				}
		   ]
    }