场景:
使用mybatis的resultMap标签进行多表联表查询结果封装
实体类:
各个实体类代码:/**
* ①用户表包括:id、用户名和密码
* ②角色表包括:id和角色名
* ③权限表包括:id、权限名和路径
* ④用户角色关联表包括:用户id和角色id
* ⑤权限角色关联表包括:角色id和权限id
*/
/**
*对应实体类的设计
*/
@Data
public class User {
private Integer uid;
private String username;
private String password;
private Set<Role> roles = new HashSet<>();
}
@Data
public class Role {
private Integer rid;
private String rname;
private Set<User> users = new HashSet<>();
private Set<Permission> permissions = new HashSet<>();
}
@Data
public class Permission {
private Integer pid;
private String pname;
private String url;
}
mybatis映射文件:
代码:
<?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.shiro.mapper.UserMapper">
<resultMap id="userMap" type="com.shiro.model.User">
<id property="uid" column="uid"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<collection property="roles" ofType="com.shiro.model.Role">
<id property="rid" column="rid"/>
<result property="rname" column="rname"/>
<collection property="permissions" ofType="com.shiro.model.Permission">
<id property="pid" column="pid"/>
<result property="pname" column="pname"/>
<result property="url" column="url"/>
</collection>
</collection>
</resultMap>
<select id="findByUsername" parameterType="string" resultMap="userMap">
select u.*,r.*,p.*
from user u
join user_role ur on u.uid = ur.uid
join role r on r.rid = ur.rid
join permission_role pr on pr.rid = r.rid
join permission p on p.pid = pr.pid
where u.username = #{username}
</select>
</mapper>
结果:
查询结果打印:User{uid=2, username=‘demo’, password=‘demo’, roles=[Role{rid=2, rname=‘user’, users=[], permissions=[Permission{pid=1, pname=‘add’, url=’’}, Permission{pid=4, pname=‘query’, url=’’}]}]}