mybatis用户角色权限一对多关联查询

mybatis一对多关联查询案例

• 1.需求
• 2.数据库说明
• 3.实体说明
• 4.mybatis SQL语句编写

---

一.需求
在做角色权限管理时，需要根据用户名查询其对应的所有角色以及拥有的所有权限

---

二.数据库说明
数据库有如下几个表

用户表（users）

id,用户名，真是姓名，密码，性别，出生日期，描述，创建日期

角色表(roles)

id,角色描述

权限表(permissions)

id,权限名称，权限资源

---

除了这三张表外，还要两张表来将三者关联起来：

用户角色关联表(users_roles)

用户id、角色id

角色权限关联表(roles_permissions)

用户角色关联表(users_roles)

三.实体说明

1.用户实体说明

用户与角色为多对多的关系，则用户实体中应该包含多个角色，此处用list封装

public class Users {    private Integer id;    private String username;    private String name;    private List<Educate> educates;     private String password;    private Byte sex;    @DateTimeFormat(pattern = "yyyy-MM-dd")//前段到后台注解    private Date birthday;    private Date createtime;    private String content;    private List<Roles> rolesList;    public List<Educate> getEducates() {        return educates;    }    public void setEducates(List<Educate> educates) {        this.educates = educates;    }    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username == null ? null : username.trim();    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password == null ? null : password.trim();    }    public Byte getSex() {        return sex;    }    public void setSex(Byte sex) {        this.sex = sex;    }    public Date getBirthday() {        return birthday;    }    public void setBirthday(Date birthday) {        this.birthday = birthday;    }    public Date getCreatetime() {        return createtime;    }    public void setCreatetime(Date createtime) {        this.createtime = createtime;    }    public String getContent() {        return content;    }    public void setContent(String content) {        this.content = content == null ? null : content.trim();    }    public List<Roles> getRolesList() {        return rolesList;    }    public void setRolesList(List<Roles> rolesList) {        this.rolesList = rolesList;    }    public void setName(String name) {        this.name = name;    }    public String getName() {        return name;    }    @Override    public String toString() {        return "Users [id=" + id + ", username=" + username + ", name=" + name + ", educates=" + educates                + ", password=" + password + ", sex=" + sex + ", birthday=" + birthday + ", createtime=" + createtime                + ", content=" + content + ", rolesList=" + rolesList + "]";    }}

2.角色实体说明

角色与权限为多对多的关系，即一个角色对应多个权限，此处用list属性进行权限封装

public class Roles {    private Integer id;    private String description;    private List<Permissions> permissionsList;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getDescription() {        return description;    }    public void setDescription(String description) {        this.description = description == null ? null : description.trim();    }    public List<Permissions> getPermissionsList() {        return permissionsList;    }    public void setPermissionsList(List<Permissions> permissionsList) {        this.permissionsList = permissionsList;    }    @Override    public String toString() {        return "Roles [id=" + id + ", description=" + description + ", permissionsList=" + permissionsList + "]";    }}

3.权限实体说明

public class Permissions {    private Integer id;    private String url;    private String name;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getUrl() {        return url;    }    public void setUrl(String url) {        this.url = url == null ? null : url.trim();    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name == null ? null : name.trim();    }    @Override    public String toString() {        return "Permissions [id=" + id + ", url=" + url + ", name=" + name + "]";    }}

---

• 四.mybatis SQL语句编写

在mapper映射文件中，编写根据用户名查找用户的方法（findUsersByName），将用户对应的角色以及拥有的权限封装

<resultMap id="users" type="Users">    <result property="id" column="id" />    <result property="username" column="username" jdbcType="VARCHAR"/>    <result property="name" column="name" jdbcType="VARCHAR"/>    <result property="password" column="password" jdbcType="VARCHAR"/>    <result property="sex" column="sex" jdbcType="VARCHAR"/>    <result property="birthday" column="birthday" jdbcType="TIMESTAMP"/>    <result property="createtime" column="createtime" jdbcType="TIMESTAMP" />  <!--  <result property="isadmin" column="isadmin" jdbcType="VARCHAR"/>-->    <result property="content" column="content" jdbcType="VARCHAR"/></resultMap>

<resultMap type="com.jointem.hrm.entity.Roles" id="RoleMap">    <id column="id" property="id" jdbcType="INTEGER" />    <result column="description" property="description" jdbcType="VARCHAR" />    <collection property="permissionsList" column="id" javaType="list" select="selectAllpermissions"></collection></resultMap><select id="selectAllpermissions" resultType="com.jointem.hrm.entity.Permissions" parameterType="java.lang.Integer">    select id, url,name  from permissions where id in (select roles_permissions.permission_id from roles_permissions where roles_permissions.role_id=#{id})</select><select id="selectAllRoles" resultMap="RoleMap" parameterType="java.lang.Integer">    select id, description from roles where id in (select users_roles.role_id from users_roles where users_roles.user_id=#{id})</select><select id="findUsersByName" parameterType="String" resultMap="usersMap">    select id,username,password,sex,birthday,createtime,content from        users where username=#{username}</select><!-- 关联查询角色集合与权限集合 -->

“`
总结
1.关联查询首先要建立对应的需求实体
2.了解resultmap属性