深入理解Mybatis中的一对一、一对多映射关系

1、搭建实验环境

  • 1).新建数据库mybatis6
  • 2). 新建表:sys_user、sys_role、sys_user_role,sys_privilege、sys_role_privilege
    sql脚本如下:
create table sys_user(
  id bigint not null primary key auto_increment,
  user_name varchar(255) ,
  user_password varchar(255),
  user_email varchar(255),
  user_info text comment '用户简介',
  head_img blob comment '头像',
  create_time datetime
);

create table sys_role(
  id bigint not null primary key auto_increment,
  role_name varchar(255) ,
  enable int comment '有效标志',
  create_by bigint comment '创建人',
  create_time datetime

);

create table sys_privilege(
  id bigint not null primary key  auto_increment,
  privilege_name varchar(50) comment '权限名称',
  privilege_url varchar(255)
);

create table sys_user_role(
  user_id bigint ,
  role_id bigint
);

create table sys_role_privilege(
  role_id bigint,
  privilege bigint
);

insert into sys_user values('1','admin','123456','adimin@mybatis.tk','管理员',null,'2019-7-30 10:05:56');
insert into sys_user values('1002','testuser','123456','test@mybatis.tk','测试用户',null,'2019-7-30 10:05:56');


insert into sys_role values('1','管理员','1','1','2019-7-30 10:05:56');
insert into sys_role values('2','普通用户','1','1','2019-7-30 10:05:56');

insert into sys_user_role values ('1','1');
insert into sys_user_role values ('1','2');
insert into sys_user_role values ('1001','2');

insert into sys_privilege values('1','用户管理','/user');
insert into sys_privilege values('2','角色管理','/roles');
insert into sys_privilege values('3','系统管理','/system');
insert into sys_privilege values('4','系统维护','/system-a');
insert into sys_privilege values('5','单位管理','/company');

insert into sys_role_privilege values('1','1');
insert into sys_role_privilege values('1','2');
insert into sys_role_privilege values('1','3');
insert into sys_role_privilege values('2','4');
insert into sys_role_privilege values('2','5');
  • 3).新建每个 表对应的实体JavaBean

SysUser.java

package com.orecal.bean;

import java.util.Date;
import java.util.List;

public class SysUser {

    private Long id;
    private String userName;
    private String userPassword;
    private String userEmail;
    private String userInfo;
    private String headImg;
    private Date createTime;
    private SysRole role;   //用户的角色
    private List<SysRole> roleList;   //用户角色结合和
    //省略getter、setter....

SysRole.java

package com.orecal.bean;

import java.util.Date;
import java.util.List;

public class SysRole {

    private Long id;
    private String roleName;
    private int enable;
    private int createBy;
    private Date createTime;
    private List<SysPrivilege> privilegeList;
     //省略getter、setter....

SysUserRole.java

package com.orecal.bean;


public class SysUserRole {

    private Long userId;
    private Long roleId;
      //省略getter、setter....
 }

SysPrivilege.java

package com.orecal.bean;


public class SysPrivilege {

    private Long id;
    private String privilegeName;
    private String privilegeUrl;
     //省略getter、setter....
 }

SysRolePrivilege.java

package com.orecal.bean;


public class SysRolePrivilege {

    private Long roleId;
    private Long privilegeId;
     //省略getter、setter....
  • 4).新建SysUserMapper接口
  • 5). 新建mybaits配置文件

2、一对一映射

Mybatis中的映射方式有两种,一种是通过resultType自动映射,另一种是通过resultMap自己设置映射规则。resultMap又有两种映射方式:嵌套结果映射嵌套查询映射

  • 嵌套结果映射:给数据库发一条复杂的sql语句把查询到的结果根据映射规则映射到不同的对象中
  • 嵌套查询映射:会发多条sql简单的语句,Mybatis会把多条sql语句的查询据结果封装到一个对象中。如果在mybatis全局配置中设置了延迟加载:
<setting name="lazyLoadingEnabled" value="true"/>
        <!--aggressvieLazyLoading当这个参数为true的时候,对任意延迟属性都会完全的加载,当为false时会按需加载-->
<setting name="aggressiveLazyLoading" value="false"/>
        那么还可以按需给数据库发sql语句,即当没有用到这个表中的数据的时候,Mybatis压根就不会给数据库法sql语句,即使已在xml文件中已经配置了sql语句,这是MyBatis中非常强大的一个功能。

配置xml文件:
通过resultMap一对一映射,在SysUser实体类中新增一个属性private SysRole role 标识用户的角色,然后在SysUserMapper.xml中写如下映射userRoleMap

SysUserMapper.xml
<resultMap type="com.orecla.bean.SysUser" id="userRoleMap">
        <!--sys_user表中原有的属性-->
        <!--
           id:id元素是一个很特殊的元素,如果设置了它,MyBayis会比较每次返回来的数据的id,如果id相同,MyBatis就会认为这是同一条数据,然后就会把这两条数据合并;如果没有设置id,MyBatis会比较resultMap下所有的字段属性,只要有一个不同就不会合并。
         -->
        <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"/>
        <result property="createTime" column="create_time"/>
        <!--role字段在sys_user表中没有,而且role他是一个复杂的类型,没法直接映射,最简单的可以通过如下这种方式来映射-->
        <result property="role.roleName" column="role_name"/>
        <result property="role.enable" column="enable"/>
        <result property="role.createTime" column="create_time"/>
        <result property="role.createBy" column="create_by"/>
   </resultMap>
    <select id="selectUserAndRoleById" resultMap="userRoleMap">
       select
          sys_user.*,sys_role.role_name
        from
          sys_user,sys_role
        where
       sys_user.id=#{id} and sys_role.role_name=sys_user.user_info
</select>

在SysUserMapper接口中增加方法:public SysUser selectUserAndRoleById(Long id);
测试方法:

 @Test
    public void test1() {
        SqlSession session = sqlSessionFactory.openSession(true);
        try {
            SysUserMapper sum = session.getMapper(SysUserMapper.class);
            SysUser user = sum.selectUserAndRoleById(1002L);
            System.out.println(user);
            //log.info(sum);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

这是日志的打印结果:

  2019-07-31 10:16:52 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById] ==>  Preparing: select sys_user.*,sys_role.role_name from sys_user,sys_role where sys_user.id=? and sys_role.role_name=sys_user.user_info 
  2019-07-31 10:16:52 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById] ==> Parameters: 1002(Long)
  2019-07-31 10:16:53 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById] <==      Total: 1
  SysUser{id=1002, userName='testuser', userPassword='123456', userEmail='test@mybatis.tk', userInfo='普通用户', headImg='null', createTime=Tue Jul 30 10:05:56 CST 2019, role=SysRole{id=null, roleName='普通用户', enable=0, createBy=0, createTime=Tue Jul 30 10:05:56 CST 2019, privilegeList=null}, roleList=null}
2019-07-31 10:16:53 [DEBUG]-[org.apache.ibatis.transaction.jdbc.JdbcTransaction] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2ed0fbae]
  2019-07-31 10:16:53 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Returned connection 785447854 to pool.

改进上面的映射方法:上面的映射方法虽然可以使用,但是耦合性太高,而且最大的问题是配置文件不能复用,啥意思?就是说,如果我现在需要一个单独查用户的方法,那我还得专门为查用户写一个sql配置,这样做非常的糟糕,当项目非常大的时候,配置文件的重复配置代码将会非常的多,那么如何解决这个问题呢?我们接着往下看:
在SysUserMapper.xml中增加一个专门为查用户的映射userMap

SysUserMapper.xml
<resultMap type="com.orecal.bean.SysUser" id="userMap">
    <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"/>
    <result property="createTime" column="create_time"/>
 </resultMap>
 <select id="selectUser" resultMap="userMap">
      select * from sys_user where id=#{id};
 </select>

然后刚才的上面的userRoleMap就可以修改成下面的样子:

SysUserMapper.xml
 <!--
      使用resultMap可以自定义结果集的映射关系
         type:这个样映射到那个pojo
         id:给这个映射关系起的一个唯一的标识
         extends:resultMap可以继承别的map已经定义好的关系,比如下面的userMap在上门已经定义了,在下面可以继承后直接使用
    -->
    <resultMap type="com.orecal.bean.SysUser" id="userRoleMap" extends="userMap">
        <result property="role.roleName" column="role_name"/>
        <result property="role.enable" column="enable"/>
        <result property="role.createTime" column="create_time"/>
        <result property="role.createBy" column="create_by"/>
    </resultMap>
    <select id="selectUserAndRoleById" resultMap="userRoleMap">
        select
          sys_user.*,sys_role.role_name
        from
          sys_user,sys_role
        where
          sys_user.id=#{id} and sys_role.role_name=sys_user.user_info
    </select>

使用association元素替代上面的role.XXX:

SysUserMapper.xml
 <!--使用association和一个复杂的类型进行关联
       property:pojo中对应的属性名,必填
       javaType:这个属性对应的pojo类型
       resultMap:可以使用这个属性配置已有的map,
                 如果要引用当前mapper中的resultMap,直接引用
                 如果引用别的mapper中的resultMap,要指定namespace以及引用的那个map的id
      -->
<resultMap id="userRoleMap2" type="com.orecal.bean.SysUser" extends="userMap">
         <association property="role" javaType="com.orecal.bean.SysRole">
             <result property="id" column="id"/>
             <result property="roleName" column="role_name"/>
             <result property="enable" column="enable"/>
             <result property="createTime" column="create_time"/>
             <result property="createBy" column="create_by"/>
         </association>
</resultMap>

可是这样还是不行,实际的开发中,肯定会有关于单独查询sys_role的需求,而且人家sys_role肯定也会有单独的mapper,这样就又会存在重复配置的问题,解决这个问题需要用到association元素的另一个功能,具体看代码:
新建SysRoleMapper.xml,并配置roleMap如下:

SysRoleMapper.xml
<!--根据id查询角色-->
<resultMap type="com.orecal.bean.SysRole" id="roleMap">
        <result property="id" column="id"/>
        <result property="roleName" column="role_name"/>
        <result property="enable" column="enable"/>
        <result property="createTime" column="create_time"/>
        <result property="createBy" column="create_by"/>
</resultMap>
<select id="selectRoleById" resultMap="roleMap">
        select * from sys_role where id=#{id};
</select>

这样我们在SysUserMapper.xml就可以把刚才的配置彻底抽取了出来:

SysUserMapper.xml
<resultMap id="userRoleMap" type="com.orecal.bean.SysUser" extends="userMap">
        <!--在association中使用resultMap属性指定要关联的Map
            resultMap=目标map的namespace.目标map的id   在同一个mapper中也可以这么使用,但是只用指定id就够了
         -->
      <association property="role" resultMap="com.orecal.mapper.SysRoleMapper.roleMap"/>
</resultMap>

这样就彻底把模块与模块分开了,当然我们也可以顺便实现以下selectRoleById,下面是配置后的运行时打印的日志的部分:

  2019-07-31 10:57:08 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById2] ==> Preparing: select sys_user.*,sys_role.role_name from sys_user,sys_role where sys_user.id=? and sys_role.role_name=sys_user.user_info 
  2019-07-31 10:57:08 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById2] ==> Parameters: 1002(Long)
  2019-07-31 10:57:08 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById2] <==      Total: 1
  SysUser{id=1002, userName='testuser', userPassword='123456', userEmail='test@mybatis.tk', userInfo='普通用户', headImg='null', createTime=Tue Jul 30 10:05:56 CST 2019, role=SysRole{id=1002, roleName='普通用户', enable=0, createBy=0, createTime=Tue Jul 30 10:05:56 CST 2019, privilegeList=null}, roleList=null}
2019-07-31 10:57:08 [DEBUG]-[org.apache.ibatis.transaction.jdbc.JdbcTransaction] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2ed0fbae]
  2019-07-31 10:57:08 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Returned connection 785447854 to pool.
  
Process finished with exit code 0

可以看到,日志的打印结果相同,但是修改后的方式肯定比一开始的方法要好,因为这样就把各个查询模块化了,就像搭积木,一个个简单的“积木块”最后通过合理的组织,就可以实现不同的复杂查询。
一对一的嵌套查询映射
上面这种方法是嵌套结果映射,就是直接给数据库发一条sql语句,数据库返回数据后Mybatis根据映射规则,把数据映射到不同的对象中。而嵌套查询映射则是多次给数据库发简单的sql语句,然后把不同的数据映射到一个对象中。

1.association元素的嵌套查询:
    select :另一个映射查询map的id
    column:将主查询的那个列的结果作为嵌套查询的参数传给嵌套查询方法
    fetchType:数据加载的方式[lazy或eager],即延迟加载或积极加载,
                配置这个属性会覆盖全局配置中飞lazyLoadingEnabled
2.MyBatis的嵌套查询可以实现懒加载,简单点的说就是不用的时候就不给你加载,等用的时候才去给你加载,这样做的好处是可以降低数据库的压力,做到按需响应。那么要用懒加载必须在全局配置文件中设置如下:
<settings> <setting name="aggressiveLazyLoading" value="false"/> <!--vallue=false时按需加载-,否者全部加载-> <setting name="lazyLoadingEnabled" value="true"/> <!--是否开启懒加载,true表示开启--> <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/> <!--懒加载模式下如果调用value后的方法将全部加载--> </settings>

在SysUserMapper.xml中写一个id为userRoleMapSelecct的新的映射关系,并写SQL查询语句如下:

SysUserMapper.xml
<resultMap id="userRoleMapSelect" extends="userMap" type="com.orecal.bean.SysUser">
     <!--
         association的select元素中指定另一个嵌套的子查询
         select=子查询Mapper接口中对应方法的全类名
      -->
     <association property="role"
           select="com.orecal.mapper.SysRoleMapper.selectRoleById"
           column="{id=role_id}"/>
</resultMap>
<select id="selectUserAndRoleById3" resultMap="userRoleMapSelect">
SELECT
	sys_user.id,
	sys_user.user_name,
	sys_user.user_password,
	sys_user.user_email,
	sys_user.user_info,
	sys_user.head_img,
	sys_user.create_time,
	sys_user_role.role_id 
FROM
	sys_user,
	sys_user_role 
WHERE
	sys_user.id = sys_user_role.user_id 
	AND sys_user.id = #{id}
</select>

配置好后我们在SysUserMapper接口中增加selectUserAndRoleById3方法,然后写测试:

 @Test
public void test4() {
        SqlSession session = sqlSessionFactory.openSession(true);
        try {
            SysUserMapper sum = session.getMapper(SysUserMapper.class);
            List<SysUser> user = sum.selectUserAndRoleById3(1002L);
            for (SysUser u : user) {
                 System.out.println(u);
                //System.out.println(u.getUserName() + "," + u.getUserEmail() + "," + u.getCreateTime());
            }
            log.info(sum);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
}

日志的打印结果:

 2019-07-31 11:29:54 [DEBUG]-[org.apache.ibatis.transaction.jdbc.JdbcTransaction] Opening JDBC Connection
  2019-07-31 11:29:54 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Created connection 785447854.
  2019-07-31 11:29:54 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById3] ==>  Preparing: select sys_user.id, sys_user.user_name, sys_user.user_password, sys_user.user_email, sys_user.user_info, sys_user.head_img, sys_user.create_time, sys_user_role.role_id from sys_user,sys_user_role where sys_user.id=sys_user_role.user_id and sys_user.id=? 
  2019-07-31 11:29:54 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById3] ==> Parameters: 1002(Long)
  2019-07-31 11:29:54 [WARN]-[org.apache.ibatis.session.AutoMappingUnknownColumnBehavior] Unknown column is detected on 'com.orecal.mapper.SysUserMapper.selectUserAndRoleById3' auto-mapping. Mapping parameters are [columnName=role_id,propertyName=role_id,propertyType=null]
  2019-07-31 11:29:54 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById3] <==      Total: 1
  2019-07-31 11:29:54 [DEBUG]-[com.orecal.mapper.SysRoleMapper.selectRoleById] ==>  Preparing: select * from sys_role where id=?; 
  2019-07-31 11:29:54 [DEBUG]-[com.orecal.mapper.SysRoleMapper.selectRoleById] ==> Parameters: 2(Long)
  2019-07-31 11:29:54 [DEBUG]-[com.orecal.mapper.SysRoleMapper.selectRoleById] <==      Total: 1
  SysUser{id=1002, userName='testuser', userPassword='123456', userEmail='test@mybatis.tk', userInfo='普通用户', headImg='null', createTime=Tue Jul 30 10:05:56 CST 2019, role=SysRole{id=2, roleName='普通用户', enable=1, createBy=1, createTime=Tue Jul 30 10:05:56 CST 2019, privilegeList=null}, roleList=null}
2019-07-31 11:29:54 [INFO]-[com.orecal.AppTest] org.apache.ibatis.binding.MapperProxy@6580cfdd
  2019-07-31 11:29:54 [DEBUG]-[org.apache.ibatis.transaction.jdbc.JdbcTransaction] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2ed0fbae]
  2019-07-31 11:29:54 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Returned connection 785447854 to pool.
  
Process finished with exit code 0

可以看到,MyBatis分别给数据库发了两条sql语句,这是因为直接打印,在配置文件的setting中有一个元素lazyLoadTriggerMethods 默认值value="equals,clone,hashCode,toString",当程序中调用这些方法的时就会全部加载。但是如果我们在程序中只是用到User的一些属性,那么Mybatis就只发查user的sql语句,把测试代码中的System.out.println(u);改成System.out.println(u.getUserName() + "," + u.getUserEmail() + "," + u.getCreateTime());

再次运行打印的日志部分如下:

  2019-07-31 11:27:13 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById3] ==>  Preparing: select sys_user.id, sys_user.user_name, sys_user.user_password, sys_user.user_email, sys_user.user_info, sys_user.head_img, sys_user.create_time, sys_user_role.role_id from sys_user,sys_user_role where sys_user.id=sys_user_role.user_id and sys_user.id=? 
  2019-07-31 11:27:13 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById3] ==> Parameters: 1002(Long)
  2019-07-31 11:27:13 [WARN]-[org.apache.ibatis.session.AutoMappingUnknownColumnBehavior] Unknown column is detected on 'com.orecal.mapper.SysUserMapper.selectUserAndRoleById3' auto-mapping. Mapping parameters are [columnName=role_id,propertyName=role_id,propertyType=null]
  2019-07-31 11:27:13 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectUserAndRoleById3] <==      Total: 1
  testuser,test@mybatis.tk,Tue Jul 30 10:05:56 CST 2019
2019-07-31 11:27:13 [INFO]-[com.orecal.AppTest] org.apache.ibatis.binding.MapperProxy@6a400542
  2019-07-31 11:27:13 [DEBUG]-[org.apache.ibatis.transaction.jdbc.JdbcTransaction] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2ed0fbae]
  2019-07-31 11:27:13 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Returned connection 785447854 to pool.
  
Process finished with exit code 0

可以看到,只发了一条sql语句。这就是MyBatis的延迟加载(懒加载),也就是说,当你没用到的时候,MyBatis压根不会帮你去查这个数据。这样一来的好处是会减轻数据库的压力。

3、一对多映射

使用collection实现一对多映射,collection的属性和用法与association基本是一样的,只是collection是专门用来映射数据库中一对多的多方元素的一个集合。比如现在有这样的需求:查询所有用户以及每个用户在本系统中所拥有的角色。这是一个很典型的一对多的例子,一个用户在系统中有多个角色。
举个栗子:
在SysUser.java中增加属性List<SysRole> roleList

    //其他的属性不变
    private List<SysRole> roleList;   //用户角色结合

    public List<SysRole> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<SysRole> roleList) {
        this.roleList = roleList;
    }

在SysUserMapper.xml中增加reultMapuserRoleListMap,由于roleMap在前面已经定义过了,这里就可以直接使用

SysUserMapper.xml
<resultMap id="userRoleListMap" extends="userMap" type="com.orecal.bean.SysUser">
   <collection property="roleList" resultMap="com.orecal.mapper.SysRoleMapper.roleMap"/>
</resultMap>
<select id="selectAllUserAndRole" resultMap="userRoleListMap">
SELECT
	sys_user.*,
	sys_role.* 
FROM
	sys_user,
	sys_role,
	sys_user_role 
WHERE
	sys_user_role.user_id = sys_user.id 
	AND sys_user_role.role_id = sys_role.id;
</select>

测试代码:

    @Test
    public void test5() {
        SqlSession session = sqlSessionFactory.openSession(true);
        try {
            SysUserMapper sum = session.getMapper(SysUserMapper.class);
            List<SysUser> user = sum.selectAllUserAndRole();
            System.out.println("用户数:" + user.size());
            for (SysUser u : user) {
                System.out.println("用户名:" + u.getUserName());
                for (SysRole role : u.getRoleList()) {
                    System.out.println("角色名:" + role.getRoleName());
                }
            }
            log.info(sum);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

程序运行打印的日志:

 2019-07-31 14:47:06 [DEBUG]-[org.apache.ibatis.transaction.jdbc.JdbcTransaction] Opening JDBC Connection
  2019-07-31 14:47:06 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Created connection 741669172.
  2019-07-31 14:47:06 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectAllUserAndRole] ==>  Preparing: select sys_user.*,sys_role.*,sys_privilege.* from sys_user, sys_role,sys_user_role,sys_privilege,sys_role_privilege where sys_user_role.user_id=sys_user.id and sys_user_role.role_id=sys_role.id and sys_role_privilege.role_id=sys_role.id and sys_privilege.id=sys_role_privilege.privilege_id; 
  2019-07-31 14:47:06 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectAllUserAndRole] ==> Parameters: 
  2019-07-31 14:47:06 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectAllUserAndRole] <==      Total: 7
  用户数:2
用户名:admin
角色名:管理员
角色名:普通用户
用户名:testuser
角色名:普通用户
2019-07-31 14:47:06 [INFO]-[com.orecal.AppTest] org.apache.ibatis.binding.MapperProxy@38c5cc4c
  2019-07-31 14:47:06 [DEBUG]-[org.apache.ibatis.transaction.jdbc.JdbcTransaction] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2c34f934]
  2019-07-31 14:47:06 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Returned connection 741669172 to pool.
  
Process finished with exit code 0

前面这个实现了一层嵌套,就是一个主查询下面只有一个层子查询然后就结束了,下面我们尝试来实现一个两层嵌套:比如现在有这样的需求:查询所有的用户的角色,以及每个角色拥有的权限。很好想,就是一个用户可以有多个角色,每个角色又有不同的权限。
实现:
首先在SysRole中增加一个属性private List<SysPrivilege> privilegeList

    //其他的属性不变
    private List<SysPrivilege> privilegeList;

    public List<SysPrivilege> getPrivilegeList() {
        return privilegeList;
    }

    public void setPrivilegeList(List<SysPrivilege> privilegeList) {
        this.privilegeList = privilegeList;
    }

新建SysPrivilegeMapper.xml文件,增加一个resultMapprivilegeMap

 <resultMap type="com.orecal.bean.SysPrivilege" id="privilegeMap">
      <result property="privilegeName" column="privilege_name"/>
      <result property="privilegeUrl" column="privilege_url"/>
 </resultMap>
   SELECT
	sys_privilege.* 
FROM
	sys_privilege,
	sys_role_privilege 
WHERE
	sys_role_privilege.privilege_id = sys_privilege.id 
	AND sys_role_privilege.role_id = #{roleId}
    </select>

在SysRoleMapper.xml中增加一个resultMaprolePrivilegeListMap,由于roleMap在上面已经定义过了,,用extends继承他就可以直接使用了。

SysRoleMapper.xml
<resultMap id="rolePrivilegeListMap" extends="roleMap" type="com.orecal.bean.SysRole">
        <collection property="privilegeList" resultMap="com.orecal.mapper.SysPrivilegeMapper.privilegeMap"/>
</resultMap>
<select id="selectAllRoleAndPrivilege" resultMap="rolePrivilegeListMap">
   select 
      sys_role.*,
      sys_privilege.*
   from 
      sys_role,
      sys_privilege,
      sys_role_privilege
   where 
   sys_role_privilege.role_id=sys_role.id and                        sys_privilege.id=sys_role_privilege.privilege_id;
 </select>

在SysUserMapper.xml中增加reusltMap:userRoleListMapSelect

SysUserMapper.xml
<resultMap id="userRoleListMapSelect" extends="userMap" type="com.orecal.bean.SysUser">
   <collection property="roleList"
            resultMap="com.orecal.mapper.SysRoleMapper.rolePrivilegeListMap"/>
    </resultMap>
    <select id="selectAllUserAndRole2" resultMap="userRoleListMap">
          SELECT
	sys_user.*,
	sys_role.*,
	sys_privilege.* 
FROM
	sys_user,
	sys_role,
	sys_user_role,
	sys_privilege,
	sys_role_privilege 
WHERE
	sys_user_role.user_id = sys_user.id 
	AND sys_user_role.role_id = sys_role.id 
	AND sys_role_privilege.role_id = sys_role.id 
	AND sys_privilege.id = sys_role_privilege.privilege_id;
    </select>

在SysUserMapper接口中增加方法selectAllUserAndRole2,并且编写测试代码:

 @Test
    public void test6() {
      SqlSession session = sqlSessionFactory.openSession(true);
      try {
        SysUserMapper sum = session.getMapper(SysUserMapper.class);
        List<SysUser> user = sum.selectAllUserAndRole();
        System.out.println("用户数:" + user.size());
        for (SysUser u : user) {
          System.out.println("用户名:" + u.getUserName());
          for (SysRole role : u.getRoleList()) {
          System.out.println("角色名:" + role.getRoleName());
             for (SysPrivilege privilege : role.getPrivilegeList()) {
             System.out.println("权限:" + privilege.getPrivilegeName());
                    }
                }
           System.out.println("--------------------------------------------------------");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

程寻运行打印的日志的一部分:

2019-07-31 15:11:27 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Created connection 741669172.
  2019-07-31 15:11:27 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectAllUserAndRole] ==>  Preparing: select sys_user.*,sys_role.*,sys_privilege.* from sys_user, sys_role,sys_user_role,sys_privilege,sys_role_privilege where sys_user_role.user_id=sys_user.id and sys_user_role.role_id=sys_role.id and sys_role_privilege.role_id=sys_role.id and sys_privilege.id=sys_role_privilege.privilege_id; 
  2019-07-31 15:11:27 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectAllUserAndRole] ==> Parameters: 
  2019-07-31 15:11:27 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectAllUserAndRole] <==      Total: 7
  用户数:2
用户名:admin
角色名:管理员
权限:用户管理
权限:角色管理
权限:系统管理
角色名:普通用户
权限:系统维护
权限:单位管理
--------------------------------------------------------
用户名:testuser
角色名:普通用户
权限:系统维护
权限:单位管理
--------------------------------------------------------
2019-07-31 15:11:27 [INFO]-[com.orecal.AppTest] org.apache.ibatis.binding.MapperProxy@38c5cc4c
  2019-07-31 15:11:27 [DEBUG]-[org.apache.ibatis.transaction.jdbc.JdbcTransaction] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2c34f934]
  2019-07-31 15:11:27 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Returned connection 741669172 to pool.
  
Process finished with exit code 0

这样,我们就算是实现了一对多的两层嵌套结果映射的一个查询,这种方式在日常非常常见,也是MyBatis中非常强大的地方。最后我们看一下一对多的嵌套查询映射,和一对一的实现方法是类似的:
前面我们写过一个resultMaprolePrivilegeListMap,但是还没有为他写接口方法,这里我们首先来实现这个:

public interface SysPrivilegeMapper {

    /**
     * 通过角色ID获得它的权限
     * @return
     */
    public List<SysPrivilege> selectPrivilegeByRoleId(Long id);

}

在SysRoleMapper.xml增加一个resultMap:rolePrivilegeListMap2

<resultMap id="rolePrivilegeListMap2" extends="roleMap" type="com.orecal.bean.SysRole">
   <collection property="privilegeList"                               select="com.orecal.mapper.SysPrivilegeMapper.selectPrivilegeByRoleId"
     column="{roleId=id}">
    </collection>
    </resultMap>
    <select id="selectRoleByUserId" resultMap="rolePrivilegeListMap2">
      SELECT
	sys_role.* 
FROM
	sys_role,
	sys_user_role 
WHERE
	sys_user_role.user_id = sys_role.id 
	AND sys_user_role.user_id = 1;
    </select>

在SysRoleMapper接口中增加一个方法selectRoleByUserId

    /**
     * 根据用户id查他的权限
     * @param userId
     * @return
     */
    public  List<SysRole>  selectRoleByUserId(Long userId);

最后在SysUserMapper.xml中增加一个resultMap:userRoleListMap2

 <resultMap id="userRoleListMap2" extends="userMap" type="com.orecal.bean.SysUser">
  <collection property="roleList"
       select="com.orecal.mapper.SysRoleMapper.selectRoleByUserId"
               column="{userId=id}"/>
</resultMap>
<select id="selectAllUserAndRole2" resultMap="userRoleListMap2">
    select sys_user.* from sys_user where sys_user.id=#{id}
</select>

在SysUserMapper接口中增加一个方法selectAllUserAndRole2:

  
    /**
     * 根据用户的Id查询他拥有的角色和对应的权限
     * @param id
     * @return
     */
    public SysUser selectAllUserAndRole2(Long id);

测试代码:

 @Test
    public void test10() {
        SqlSession session = sqlSessionFactory.openSession(true);
        try {
            SysUserMapper sum = session.getMapper(SysUserMapper.class);
            SysUser user = sum.selectAllUserAndRole2(1002L);
            System.out.println("-------------------------------------------");
            System.out.println("用户名:" + user.getUserName());
            for (SysRole role : user.getRoleList()) {
                System.out.println("角色名:" + role.getRoleName());
                for (SysPrivilege privilege : role.getPrivilegeList()) {
                    System.out.println("权限:" + privilege.getPrivilegeName());
                }
                System.out.println("$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$");
            }
            System.out.println("-------------------------------------------");
            log.info(sum);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

程寻运行打印的日志:

2019-07-31 15:40:08 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Created connection 785447854.
  2019-07-31 15:40:08 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectAllUserAndRole2] ==>  Preparing: select sys_user.* from sys_user where sys_user.id=? 
  2019-07-31 15:40:08 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectAllUserAndRole2] ==> Parameters: 1(Long)
  2019-07-31 15:40:08 [DEBUG]-[com.orecal.mapper.SysUserMapper.selectAllUserAndRole2] <==      Total: 1
  -------------------------------------------
用户名:admin
2019-07-31 15:40:08 [DEBUG]-[com.orecal.mapper.SysRoleMapper.selectRoleByUserId] ==>  Preparing: select sys_role.* from sys_role,sys_user_role where sys_user_role.user_id=sys_role.id and sys_user_role.user_id=? 
  2019-07-31 15:40:08 [DEBUG]-[com.orecal.mapper.SysRoleMapper.selectRoleByUserId] ==> Parameters: 1(Long)
  2019-07-31 15:40:08 [DEBUG]-[com.orecal.mapper.SysRoleMapper.selectRoleByUserId] <==      Total: 2
  角色名:管理员
2019-07-31 15:40:08 [DEBUG]-[com.orecal.mapper.SysPrivilegeMapper.selectPrivilegeByRoleId] ==>  Preparing: select sys_privilege.* from sys_privilege,sys_role_privilege where sys_role_privilege.privilege_id=sys_privilege.id and sys_role_privilege.role_id=? 
  2019-07-31 15:40:08 [DEBUG]-[com.orecal.mapper.SysPrivilegeMapper.selectPrivilegeByRoleId] ==> Parameters: 1(Long)
  2019-07-31 15:40:08 [DEBUG]-[com.orecal.mapper.SysPrivilegeMapper.selectPrivilegeByRoleId] <==      Total: 3
  权限:用户管理
权限:角色管理
权限:系统管理
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
角色名:管理员
权限:用户管理
权限:角色管理
权限:系统管理
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
-------------------------------------------
2019-07-31 15:40:08 [INFO]-[com.orecal.AppTest] org.apache.ibatis.binding.MapperProxy@63355449
  2019-07-31 15:40:08 [DEBUG]-[org.apache.ibatis.transaction.jdbc.JdbcTransaction] Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@2ed0fbae]
  2019-07-31 15:40:08 [DEBUG]-[org.apache.ibatis.datasource.pooled.PooledDataSource] Returned connection 785447854 to pool.
  
Process finished with exit code 0

可以看到,程序运行时,MyBatis给数据库发了多条sql语句,最终通过预定的映射集合,把这些查出来的数据放进去,之后打包组合成一个List<SysRole>对象返回。

留言区

还能输入500个字符