一对多的映射只有两种配置方式,都是使用collection标签进行的。
- collection集合的嵌套结果映射
和association类似,集合的嵌套结果就是指通过一次SQL查询将所有的结果查询出来,然后通过配置的结果映射,将结果映射到不同的对象中去。在一对多的关系中,主表的一条数据会对应关联表中的多条数据,因此一般查询时会查询出多个结果,按照一对多的数据结构存储数据的时候,最终的结果数会小于等于查询的总记录数。在我们设定的虚拟系统中,一个用户拥有多个角色,每个角色又是多个权限的集合,所以要渐进式的去实现一个SQL,查询出所有用户和用户拥有的角色,以及角色所包含的所有权限信息的两层嵌套结果。
我们先来实现第一层嵌套结果。为了能够存储一对多的数据,我们得先对User类进行修改,增加roleList属性用于存储用户对应的多个角色。
package pojo;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
public class User {
private Long id; //用户ID
private String userName; //用户名
private String userPassword; //密码
private String userEmail; //Email
private String userInfo; //简介
private byte[] headImg; //头像
private Date createTime; //创建时间
private List<Role> roleList;
public User() {
}
public User(Long id, String userName, String userPassword, String userEmail,
String userInfo, byte[] headImg, Date createTime, List<Role> roleList) {
this.id = id;
this.userName = userName;
this.userPassword = userPassword;
this.userEmail = userEmail;
this.userInfo = userInfo;
this.headImg = headImg;
this.createTime = createTime;
this.roleList = roleList;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPassword() {
return userPassword;
}
public void setUserPassword(String userPassword) {
this.userPassword = userPassword;
}
public String getUserEmail() {
return userEmail;
}
public void setUserEmail(String userEmail) {
this.userEmail = userEmail;
}
public String getUserInfo() {
return userInfo;
}
public void setUserInfo(String userInfo) {
this.userInfo = userInfo;
}
public byte[] getHeadImg() {
return headImg;
}
public void setHeadImg(byte[] headImg) {
this.headImg = headImg;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", userPassword='" + userPassword + '\'' +
", userEmail='" + userEmail + '\'' +
", userInfo='" + userInfo + '\'' +
", headImg=" + Arrays.toString(headImg) +
", createTime=" + createTime +
", roleList=" + roleList +
'}';
}
}
之后,在UserMapper.xml文件中创建resultMap。我们先创建一个原始的,之后再根据一对一映射中讲到的方法,进行该resultMap的简化。我们已经知道,User中的属性可以直接通过继承userMap来使用t_user的映射关系,其次在RoleMapper.xml中的roleMap映射包含了t_role的映射关系,因此可以直接引用roleMap,那么最终简化后的userRoleMapperList也是很简单的。
//原始resultMap
<?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="Interface.UserMapper">
<resultMap id="userRoleListMap" type="pojo.User">
<id property="id" column="id"/>
<result property="userName" column="user_name"/>
<result property="userPassword" column="user_password"/>
<result property="userEmail" column="user_email"/>
<result property="userInfo" column="user_info"/>
<result property="headImg" column="head_Img" jdbcType="BLOB"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<collection property="roleList" columnPrefix="role_" ofType="pojo.Role">
<id property="id" column="id"/>
<result property="roleName" column="role_name"/>
<result property="enabled" column="enabled"/>
<result property="createAuthor" column="create_author"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</collection>
</resultMap>
</mapper>
//简化后的resultMap
<?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="Interface.UserMapper">
<resultMap id="userMap" type="pojo.User">
<id property="id" column="id"/>
<result property="userName" column="user_name"/>
<result property="userPassword" column="user_password"/>
<result property="userEmail" column="user_email"/>
<result property="userInfo" column="user_info"/>
<result property="headImg" column="head_Img" jdbcType="BLOB"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
<resultMap id="userRoleListMap" extends="userMap" type="pojo.User">
<collection property="roleList" columnPrefix="role_" resultMap="Interface.RoleMapper.roleMap"/>
</resultMap>
</mapper>
之后,我们新建一个方法,来查找所有用户的所有角色,并写出相应SQL代码。
package Interface;
import pojo.User;
import java.util.List;
public interface UserMapper {
List<User> selectAllUserAndRoles();
}
<?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="Interface.UserMapper">
<resultMap id="userMap" type="pojo.User">
<id property="id" column="id"/>
<result property="userName" column="user_name"/>
<result property="userPassword" column="user_password"/>
<result property="userEmail" column="user_email"/>
<result property="userInfo" column="user_info"/>
<result property="headImg" column="head_Img" jdbcType="BLOB"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
<resultMap id="userRoleListMap" extends="userMap" type="pojo.User">
<collection property="roleList" columnPrefix="role_" resultMap="Interface.RoleMapper.roleMap"/>
</resultMap>
<select id="selectAllUserAndRoles" resultMap="userRoleListMap">
select u.id, u.user_name, u.user_password, u.user_email, u.user_info, u.head_img,
u.create_time, r.id role_id, r.role_name role_role_name, r.enabled role_enabled,
r.create_author role_create_author, r.create_time role_create_time
from t_user u
inner join t_user_role ur on u.id = ur.user_id
inner join t_role r on ur.role_id = r.id
</select>
</mapper>
按照之前的代码内容,编写此方法的测试方法,并运行验证:
@Test
public void testSelectAllUserAndRoles() {
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.selectAllUserAndRoles();
System.out.println("用户数:" + userList.size());
for (User user : userList) {
System.out.println("用户名:" + user.getUserName());
for (Role role : user.getRoleList()) {
System.out.println("角色名:" + role.getRoleName());
}
}
} finally {
sqlSession.close();
}
}
运行结果符合预期。我们可以看到,SQL语句的执行结果有3条,但是后面输出的用户数是2,也就是本来查询出来的3条数据经过MyBatis对collection数据的处理后,将其中的两条数据合并了。因为我们设置的是张三用户拥有两个角色,所以转换为一对多的数据结构后也就变成了两条结果,但是MyBatis是如何知道要将数据处理成这样的呢?
MyBatis在处理结果的时候,会判断结果是否相同,如果是相同的结果,则会保留第一个结果,所以这个问题的关键就变成了MyBatis如何判断结果是否相同。MyBatis判断结果是否相同时,最简单的情况就是在映射配置中至少有一个id标签,如:
<id property = "id" column = "id"/>
我们对id(构造方法中是idArg)的理解一般是它配置的字段为表的主键,联合主键时可以配置多个id标签,因为MyBatis的resultMap只用于配置结果如何映射,并不知道这个表具体如何。id的唯一作用就是在嵌套的映射配置时判断数据是否相同,当配置id标签时,MyBatis只需要逐条比较所有数据中id标签配置的字段值是否相同即可。在配置嵌套结果查询时,配置id标签可以提高处理效率。这样一来,我们就理解了,因为SQL查询结果中的前两条数据的userMap部分的id相同,所以它们属于同一个用户,因此这两条数据就会合并到同一个用户中。
接下来,我们再看第二层嵌套,即查询角色对应的权限。首先,在PrivilegeMapper.xml文件中添加privilege的映射配置。
<?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="Interface.PrivilegeMapper">
<!--添加映射配置-->
<resultMap id="privilegeMap" type="pojo.Privilege">
<id property="id" column="id"/>
<result property="privilegeName" column="privilege_name"/>
<result property="privilegeUrl" column="privilege_url"/>
</resultMap>
</mapper>
之后,修改实体类Role的内容:
public class Role {
private Long id; //角色ID
private String roleName; //角色名
private int enabled; //有效标志
private Long createAuthor; //创建者
private Date createTime; //创建时间
List<Privilege> privilegeList;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public int getEnabled() {
return enabled;
}
public void setEnabled(int enabled) {
this.enabled = enabled;
}
public Long getCreateAuthor() {
return createAuthor;
}
public void setCreateAuthor(Long createAuthor) {
this.createAuthor = createAuthor;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public List<Privilege> getPrivilegeList() {
return privilegeList;
}
public void setPrivilegeList(List<Privilege> privilegeList) {
this.privilegeList = privilegeList;
}
@Override
public String toString() {
return "Role{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", enabled=" + enabled +
", createAuthor=" + createAuthor +
", createTime=" + createTime +
", privilegeList=" + privilegeList +
'}';
}
}
然后再在RoleMapper.xml文件中,增加resultMap的配置
<?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="Interface.RoleMapper">
<resultMap id="roleMap" type="pojo.Role">
<result property="id" column="id"/>
<result property="roleName" column="role_name"/>
<result property="enabled" column="enabled"/>
<result property="createAuthor" column="create_author"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
<resultMap id="rolePrivilegeListMap" extends="roleMap" type="pojo.Role">
<collection property="privilegeList" columnPrefix="privilege_" resultMap="Interface.PrivilegeMapper.privilegeMap"/>
</resultMap>
</mapper>
我们创建角色权限映射,继承了roleMap,嵌套了privilegeList属性,直接使用了PrivilegeMapper.xml中的privilegeMap。之后,再修改UserMapper.xml中的userRoleListMap,并修改之前的查询语句:
<?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="Interface.UserMapper">
<resultMap id="userMap" type="pojo.User">
<id property="id" column="id"/>
<result property="userName" column="user_name"/>
<result property="userPassword" column="user_password"/>
<result property="userEmail" column="user_email"/>
<result property="userInfo" column="user_info"/>
<result property="headImg" column="head_Img" jdbcType="BLOB"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
<resultMap id="userRoleListMap" extends="userMap" type="pojo.User">
<collection property="roleList" columnPrefix="role_" resultMap="Interface.RoleMapper.rolePrivilegeListMap"/>
</resultMap>
<select id="selectAllUserAndRoles" resultMap="userRoleListMap">
select u.id, u.user_name, u.user_password, u.user_email, u.user_info, u.head_img,
u.create_time, r.id role_id, r.role_name role_role_name, r.enabled role_enabled,
r.create_author role_create_author, r.create_time role_create_time, p.id role_privilege_id,
p.privilege_name role_privilege_privilege_name, p.privilege_url role_privilege_privilege_url
from t_user u
inner join t_user_role ur on u.id = ur.user_id
inner join t_role r on ur.role_id = r.id
inner join t_role_privilege rp on rp.role_id = r.id
inner join t_privilege p on p.id = rp.privilege_id
</select>
</mapper>
然后,对之前的测试类文件稍加修改,并启动,得到最终结果:
@Test
public void testSelectAllUserAndRoles() {
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userMapper.selectAllUserAndRoles();
System.out.println("用户数:" + userList.size());
for (User user : userList) {
System.out.println("用户名:" + user.getUserName());
for (Role role : user.getRoleList()) {
System.out.println("角色名:" + role.getRoleName());
for (Privilege privilege : role.getPrivilegeList()) {
System.out.println("权限名:" + privilege.getPrivilegeName());
}
}
}
} finally {
sqlSession.close();
}
}
运行结果与预期一致。
- collection集合的嵌套查询
之前我们看了一下collection集合的嵌套结果映射,而关于collection集合的嵌套查询其实和其嵌套结果映射相差不了多少。我们仍然以关联的嵌套结果映射中的selectAllUserAndRoles()为基础,将该方法修改为集合的嵌套查询方式。我们需要以自下而上的过程来实现这样一个两层嵌套的功能,并且这个自下而上的过程中的每一个方法都是一个独立可用的方法,最后的结果都是以前一个方法为基础的。把所有的对象设置为延迟加载,因此每个方法都能单独作为一个普通的、不存在嵌套的查询存在。
首先在PrivilegeMapper文件中添加方法,然后再在PrivilegeMapper.xml中添加查询的SQL语句:
package Interface;
import pojo.Privilege;
public interface PrivilegeMapper {
Privilege selectPrivilegeByRoleId(Long id);
}
<?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="Interface.PrivilegeMapper">
<!--添加映射配置-->
<resultMap id="privilegeMap" type="pojo.Privilege">
<id property="id" column="id"/>
<result property="privilegeName" column="privilege_name"/>
<result property="privilegeUrl" column="privilege_url"/>
</resultMap>
<select id="selectPrivilegeByRoleId" resultMap="privilegeMap">
select * from t_privilege p
inner join t_role_privilege rp on rp.privilege_id = p.id
where role_id = #{id}
</select>
</mapper>
这个查询方法中通过角色的id号来获取该角色所对应的权限的信息。然后我们在RoleMapper文件中添加相应方法并在RoleMapper.xml中配置映射和对应的查询方法:
package Interface;
import pojo.Role;
public interface RoleMapper {
Role selectRoleByUserId(Long id);
}
<?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="Interface.RoleMapper">
<resultMap id="roleMap" type="pojo.Role">
<result property="id" column="id"/>
<result property="roleName" column="role_name"/>
<result property="enabled" column="enabled"/>
<result property="createAuthor" column="create_author"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
<resultMap id="rolePrivilegeListMapSelect" extends="roleMap" type="pojo.Role">
<collection property="privilegeList" fetchType="lazy" column="{id=id}"
select="Interface.PrivilegeMapper.selectPrivilegeByRoleId"/>
</resultMap>
<select id="selectRoleByUserId" resultMap="rolePrivilegeListMapSelect">
select r.id, r.role_name, r.enabled, r.create_author, r.create_time
from t_role r
inner join t_user_role ur on ur.role_id = r.id
where ur.user_id = #{id}
</select>
</mapper>
然后,我们要在顶层的用户信息UserMapper和UserMapper.xml中添加内容:
package Interface;
import pojo.User;
public interface UserMapper {
User selectAllUserAndRolesSelect(Long id);
}
<?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="Interface.UserMapper">
<resultMap id="userMap" type="pojo.User">
<id property="id" column="id"/>
<result property="userName" column="user_name"/>
<result property="userPassword" column="user_password"/>
<result property="userEmail" column="user_email"/>
<result property="userInfo" column="user_info"/>
<result property="headImg" column="head_Img" jdbcType="BLOB"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
</resultMap>
<select id="selectById" resultMap="userMap">
select * from t_user where id = #{id}
</select>
<resultMap id="userRoleListMapSelect" extends="userMap" type="pojo.User">
<collection property="roleList" fetchType="lazy" select="Interface.RoleMapper.selectRoleByUserId" column="{id=id}"/>
</resultMap>
<select id="selectAllUserAndRolesSelect" resultMap="userRoleListMapSelect">
select u.id, u.user_name, u.user_password, u.user_email, u.user_info, u.head_img,
u.create_time
from t_user u
where u.id = #{id}
</select>
</mapper>
之后进行测试,运行,其结果与之前一样,符合预期:
@Test
public void testSelectAllUserAndRolesSelect() {
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectAllUserAndRolesSelect(1001L);
System.out.println("用户名:" + user.getUserName());
for (Role role : user.getRoleList()) {
System.out.println("角色名:" + role.getRoleName());
for (Privilege privilege : role.getPrivilegeList()) {
System.out.println("权限名:" + privilege.getPrivilegeName());
}
}
} finally {
sqlSession.close();
}
}
其实,这种一对多的查询主要的难点应该是在实体类的整合和resultMap的书写上,只要把这些弄懂,SQL语句其实并不难写。