需求:查询角色及角色赋予的用户信息。
分析:一个用户可以拥有多个角色,一个角色也可以赋予多个用户,用户和角色为双向的一对多关系,多对多关系其实我们看成是双向的一对多关系。
user(uid, username):王贺、万通
? user_role(uid, rid)
role(rid, ):校长、老师、学生
public class Role {
private Integer id;
private String roleName;
private String roleDesc;
//多方
private List<User> userList;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
public List<User> getUserList() {
return userList;
}
public void setUserList(List<User> userList) {
this.userList = userList;
}
@Override
public String toString() {
return "Role{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
", userList=" + userList +
'}';
}
}
public interface RoleMapper {
Role getRoleById(Integer id);
}
<resultMap id="getRoleByIdResultMap" type="cn.fpl1116.pojo.Role">
<id column="rid" property="id"></id>
<result column="role_name" property="roleName"></result>
<result column="role_desc" property="roleDesc"></result>
<!--
一对多使用collection标签指定数据的封装规则
property="userList":pojo的属性
ofType="com.by.pojo.User":集合的泛型,等价于resultType
-->
<collection property="userList" ofType="cn.fpl1116.pojo.User">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="address" property="address"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
</collection>
</resultMap>
<select id="getRoleById" parameterType="int" resultMap="getRoleByIdResultMap">
SELECT
r.id as rid,r.role_name,r.role_desc,
u.*
FROM
user_role ur
JOIN role r ON ur.rid=r.id
JOIN user u ON ur.uid=u.id
WHERE
r.id=#{id}
</select>
@Test
public void testGetRoleById(){
RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
Role role = roleMapper.getRoleById(1);
System.out.println(role);
}