第一: UserMenuPrivilegeMapper 映射 (此为中间关联表)
如: User --------------Privilege ----------------Menu
UserPrivilege --------MenuPrivilege (分别关联 User+Privilege , Menu+Privilege )
UserMenuPrivilege (关联 UserPrivilege + MenuPrivilege 表)
关于 Collection 内部嵌套 collection 问题-- 需在每个 Collection 关联对象的 resultMap 映射中 继续配置 Collection ,才能实现Collection 的嵌套问题
<mapper namespace="com.xjc.dao.UserMenuPrivilegeDao"> // Dao
<resultMap id="BaseResultMap" type="com.xjc.association.UserMenuPrivilege"> // Javabean
<id column="id" property="umpID" jdbcType="INTEGER" />
<result column="userCode" property="userCode" jdbcType="VARCHAR" /> // 关联列
<result column="menu_code" property="menu_code" jdbcType="VARCHAR" /> // 关联列
<result column="pricode" property="pricode" jdbcType="VARCHAR" /> // 关联列
<!--用户权限表 -->
<collection property="userPrivilegeList"
ofType="com.xjc.association.UserPrivilege"
column="{userCode=userCode,pricode=pricode}" //关联列,多参数 (体现关联字段)
select="selectUserPrivilege" /> //通过上述参数 查询映射表
<!-- 模块权限表 --><collection property="menuPrivilegeList"
ofType="com.xjc.association.MenuPrivilege"
column="{menu_code=menu_code,pricode=pricode}"
select="selectMenuPrivilege"/>
</resultMap><!--查找用户权限表 -->
<select id="selectUserPrivilege" resultMap=" UserPrivilegeResultMap" parameterType=" java.util.Map"> //多参数
select id,userCode,pricode from [dbo].[userPrivilege]
where userCode = #{userCode,jdbcType=VARCHAR} and pricode= #{pricode,jdbcType=VARCHAR}
</select>
<!--关联用户权限表 -->
<resultMap id=" UserPrivilegeResultMap" type="com.xjc.association.UserPrivilege">
<id column="id" property="userPreID" jdbcType="INTEGER" />
<result column="pricode" property="pricode" jdbcType="VARCHAR" />
<result column="userCode" property="userCode" jdbcType="VARCHAR" />
<!--用户表 -->
<collection property="userList"
ofType="com.xjc.user.User"
column="userCode"
select="selectUserList" />
<!--权限表 --><collection property="privilegeList"
ofType="com.xjc.entity.Privilege"
column="pricode"
select="selectPrivilegeList" />
</resultMap><!--查找模块权限表 -->
<select id=" selectMenuPrivilege" resultMap="MenuPrivilegeResultMap" parameterType=" java.util.Map">
select id,menu_code, pricode from [dbo].[menuPrivilege]
where menu_code = #{menu_code,jdbcType=VARCHAR} and pricode= #{pricode,jdbcType=VARCHAR}
</select>
<!--关联模块权限表 -->
<resultMap id="MenuPrivilegeResultMap" type="com.xjc.association.MenuPrivilege">
<id column="id" property="menuPreID" jdbcType="INTEGER" />
<result column="menu_code" property="menu_code" jdbcType="VARCHAR" />
<result column="pricode" property="pricode" jdbcType="VARCHAR" />
<!--模块表 -->
<collection property="menuList"
ofType="com.xjc.entity.Menu"
column="menu_code"
select="selectMenuList" />
<!--权限表 --><collection property="privilegeList"
ofType="com.xjc.entity.Privilege"
column="pricode"
select="selectPrivilegeList" />
</resultMap><!--查找用户表 -->
<select id="selectUserList" resultMap="userListMap"
parameterType="java.lang.String">
select id,userName, userCode, headImg
from [dbo].[ouser]
where userCode = #{userCode,jdbcType=VARCHAR}
</select>
<!--关联用户表 -->
<resultMap id="userListMap" type="com.xjc.user.User">
<id column="id" property="userID" jdbcType="INTEGER" />
<result column="userName" property="userName" jdbcType="VARCHAR" />
<result column="userCode" property="userCode" jdbcType="VARCHAR" />
<result column="headImg" property="headImg" jdbcType="VARCHAR" />
</resultMap>
<!--查找权限表 -->
<select id="selectPrivilegeList" resultMap="privilegeListMap"
parameterType="java.lang.String">
select id, preName, pricode
from [dbo].[privilege]
where pricode = #{pricode,jdbcType=VARCHAR}
</select>
<!-- 关联权限表-->
<resultMap id="privilegeListMap" type="com.xjc.entity.Privilege">
<id column="id" property="preID" jdbcType="INTEGER" />
<result column="preName" property="preName" jdbcType="VARCHAR" />
<result column="pricode" property="pricode" jdbcType="VARCHAR" />
</resultMap>
<!--查找模块表 -->
<select id="selectMenuList" resultMap="menuListMap"
parameterType="java.lang.String">
select id,menu_name, menu_code, menu_url
from [dbo].[menus]
where menu_code = #{menu_code,jdbcType=VARCHAR}
</select>
<!--关联模块表 -->
<resultMap id="menuListMap" type="com.xjc.entity.Menu">
<id column="id" property="menu_id" jdbcType="INTEGER" />
<result column="menu_name" property="menu_name" jdbcType="VARCHAR" />
<result column="parent_id" property="parent_id" jdbcType="INTEGER" />
<result column="menu_code" property="menu_code" jdbcType="VARCHAR" />
<result column="menu_url" property="menu_url" jdbcType="VARCHAR" />
</resultMap>
<!--通过用户编码 查询用户权限 -->
<select id="findUserMenuPrivilege" resultMap="BaseResultMap"
parameterType="java.lang.Integer">
b.menu_name,b.menu_code,b.menu_url,a.preName,a.pricode
from
( select
u.id, u.userName, u.userCode, u.headImg, p.preName,p.pricode
from
[dbo].[ouser] u
inner join [dbo].[userPrivilege] usp
on u.userCode =
usp.userCode
inner join [dbo].[privilege] p
on p.pricode = usp.pricode ) a
inner join
( select m.menu_name,m.menu_code,m.menu_url,
pr.preName,pr.pricodefrom [dbo].[menus] m
inner join
[dbo].[menuPrivilege] mp
on m.menu_code = mp.menu_code
inner join
[dbo].[privilege] pr
on mp.pricode = pr.pricode ) b
on a.pricode =b.pricodewhere a.id = #{userID,jdbcType=INTEGER}
</select>
<!--删除 用户权限 -->
<delete id="delUserMenuPrivilege" parameterType="java.util.Map">
delete from
[dbo].[userMenuPrivilege]
where userCode = #{userCode,jdbcType=VARCHAR}
and pricode = #{pricode,jdbcType=VARCHAR} and menu_code =
#{menu_code,jdbcType=VARCHAR}
</delete>
<!--通过增加用户权限 -->
<insert id="addUserMenuPrivilege" parameterType="java.util.Map">
insert into
[dbo].[userMenuPrivilege]
(userCode,menu_code, pricode)
values
( #{userCode,jdbcType=VARCHAR}, #{menu_code,jdbcType=VARCHAR},
#{pricode,jdbcType=VARCHAR})
</insert>
</mapper>
第二: Javabean
public class UserMenuPrivilege {
private int umpID; // 三表主键
private String pricode;
private String userCode;
private String menu_code;
private List<UserPrivilege> userPrivilegeList;
private List<MenuPrivilege> menuPrivilegeList;
// 省略 GET + SET 方法
}