MyBatis多对多

描述：就是用户-角色-权限的关联查询，一共5张表

User表---UserRole表---Role表---RolePopdom表---Popedom表

这里只记录一下用户角色的多对多，角色权限也是同样的。

数据库UML截图：

实体类（与数据库字段只是大小写上的区别）

User.java

public class User {
    private String userid;
    private String comid;
    private String username;
    private String logonname;
    private String logonpwd;
            .......
//数据库中没有的字段，新增
    private List<Role> roleList;//角色列表
    private List<UserFile> userFileList;//用户文件列表

//getter、setter方法

Role.java

public class Role {
    private String roleid;
    private String rolename;
//新增，数据库中没有的字段
    private List<User> userList;//用户列表
//getter、setter方法

Popedom.java

public class Popedom {
    private String popedomid;
    private String parentid;
    private String pname;
    private String url;
    private String icon;
    private String target;
    private Boolean isparent;
    private Boolean ismenu;

        提示：没有中间关联表的实体类

MyBatis的Mapper文件

UserMapper.xml第一种写法

<?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.biobase.mapper.UserMapper">
<resultMap id="UserMap" type="com.biobase.entity.User">
    <id property="userid" column="userID"/>
    <result property="comid" column="comID" />
    <result property="username" column="userName" />
    <result property="logonname" column="logonName" />
    <result property="logonpwd" column="logonPwd" />
    <result property="sexid" column="sexID" />
    <result property="birthday" column="birthday" />
    <result property="address" column="address" />
    <result property="contacttel" column="contactTel" />
    <result property="email" column="email" />
    <result property="mobile" column="mobile" />
    <result property="wechat" column="weChat" />
    <result property="isduty" column="isDuty" />
    <result property="postid" column="postID" />
    <result property="remark" column="remark" />
    <result property="isdelete" column="isDelete" />
    <result property="createempid" column="createEmpID" />
    <result property="createdate" column="createDate" />
    <result property="lastempid" column="lastEmpID" />
    <result property="lastdate" column="lastDate" />
    <collection property="roleList" ofType="com.biobase.entity.Role" >
      <id property="roleid" column="roleID"/>
      <result property="rolename" column="roleName"/>
    </collection>
  </resultMap>

注意：<collection property="roleList" ofType="com.biobase.entity.Role" >中roleList与实体类中的一致，否则得不到结果，我就是在这里出的错。

同样

<collection property="roleList" ofType="com.biobase.entity.Role" >
      <id property="roleid" column="roleID"/>
      <result property="rolename" column="roleName"/>
    </collection>

可以分开写：(没有区别，只是换了种写法)

<resultMap id="UserRoleMap" type="com.biobase.entity.User" extends="UserMap">
  <collection property="rolelist" ofType="com.biobase.entity.Role">
    <id property="roleid" column="roleID"/>
    <result property="rolename" column="roleName"/>
  </collection>
</resultMap>

执行的SQL语句部分

<select id="findUserByLogonName" parameterType="java.lang.String" resultMap="UserMap">
    SELECT
	u.userID,comID,	userName,logonName,logonPwd,sexID,birthday,address,contactTel,email,mobile,weChat,
	isDuty,	postID,	remark,	isDelete,createEmpID,createDate,lastEmpID,lastDate,r.roleID AS roleID,r.roleName AS roleName
FROM
	biobase_user u,
	biobase_user_role ur,
	biobase_role r
WHERE
	u.logonName=#{logonname,jdbcType=VARCHAR} AND u.userID=ur.userID AND ur.roleID=r.roleID
  </select>

UserMapper.xml第二种写法

<?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.biobase.mapper.UserMapper">
<resultMap id="UserMap" type="com.biobase.entity.User">
    <id property="userid" column="userID"/>
    <result property="comid" column="comID" />
    <result property="username" column="userName" />
    <result property="logonname" column="logonName" />
    <result property="logonpwd" column="logonPwd" />
    <result property="sexid" column="sexID" />
    <result property="birthday" column="birthday" />
    <result property="address" column="address" />
    <result property="contacttel" column="contactTel" />
    <result property="email" column="email" />
    <result property="mobile" column="mobile" />
    <result property="wechat" column="weChat" />
    <result property="isduty" column="isDuty" />
    <result property="postid" column="postID" />
    <result property="remark" column="remark" />
    <result property="isdelete" column="isDelete" />
    <result property="createempid" column="createEmpID" />
    <result property="createdate" column="createDate" />
    <result property="lastempid" column="lastEmpID" />
    <result property="lastdate" column="lastDate" />
    <collection property="roleList" column="roleID" select="com.biobase.mapper.RoleMapper.byId" />
  </resultMap>

注意：<collection property="roleList" column="roleID" select="com.biobase.mapper.RoleMapper.byId" />

这里column="Role的一个字段"，select="mapper接口中的某个方法，不是resultMap"

这样，需要在RoleMapper.java接口中添加byId方法，BaseMapper是我自己写的公共方法，就是统一的一些增删改查操作

public interface RoleMapper extends BaseMapper<Role> {
    Role byId(String role);
}

RoleMapper.xml文件中，

<?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.biobase.mapper.RoleMapper">
  <resultMap id="RoleMap" type="com.biobase.entity.Role">
    <result property="roleid" column="roleID" />
    <result property="rolename" column="roleName" />
  </resultMap>
  <select id="byId" parameterType="java.lang.String" resultMap="RoleMap">
    SELECT * from biobase_role where roleID = #{roleid,jdbcType=VARCHAR}
  </select>
</mapper>

本人刚入行，文章主要记录自己遇到的问题与解决的方法，因为参考了很多网上资源，如有雷同，还望见谅。

希望大家批评指正