MyBatis从入门到精通(十二)：使用collection标签实现嵌套查询

最近在读刘增辉老师所著的《mybatis从入门到精通》一书，很有收获，于是将自己学习的过程以博客形式输出，如有错误，欢迎指正，如帮助到你，不胜荣幸！

本篇博客主要讲解使用collection标签实现嵌套查询的方法。

1. 需求升级

在上篇博客中，我们实现了需求：根据用户id查询用户信息的同时获取用户拥有的角色。

因为角色可以拥有多个权限，所以本篇博客我们升级需求为：根据用户id查询用户信息的同时获取用户拥有的角色以及角色包含的权限。

2. 实现方式

因为我们需要使用到权限表的映射，所以我们需要先在sysprivilegemapper.xml中添加如下映射：

<resultmap id="sysprivilegemap" type="com.zwwhnly.mybatisaction.model.sysprivilege">
    <id property="id" column="id"/>
    <result property="privilegename" column="privilege_name"/>
    <result property="privilegeurl" column="privilege_url"/>
</resultmap>

一般情况下不建议修改数据库表对应的实体类，所以这里我们新建类sysroleextend，让它继承sysrole类，并添加如下字段：

package com.zwwhnly.mybatisaction.model;

import java.util.list;

public class sysroleextend extends sysrole {
    /**
     * 角色包含的权限列表
     */
    private list<sysprivilege> sysprivilegelist;

    public list<sysprivilege> getsysprivilegelist() {
        return sysprivilegelist;
    }

    public void setsysprivilegelist(list<sysprivilege> sysprivilegelist) {
        this.sysprivilegelist = sysprivilegelist;
    }
}

然后在sysrolemapper.xml中新建如下映射：

<resultmap id="roleprivilegelistmap" extends="rolemap"
           type="com.zwwhnly.mybatisaction.model.sysroleextend">
    <collection property="sysprivilegelist" columnprefix="privilege_"
                resultmap="com.zwwhnly.mybatisaction.mapper.sysprivilegemapper.sysprivilegemap"/>
</resultmap>

这里的rolemap我们在之前的博客中已经定义过，代码如下：

<resultmap id="rolemap" type="com.zwwhnly.mybatisaction.model.sysrole">
    <id property="id" column="id"/>
    <result property="rolename" column="role_name"/>
    <result property="enabled" column="enabled"/>
    <result property="createby" column="create_by"/>
    <result property="createtime" column="create_time" jdbctype="timestamp"/>
</resultmap>

com.zwwhnly.mybatisaction.mapper.sysprivilegemapper.sysprivilegemap就是我们刚刚在sysprivilegemapper.xml中新建的映射sysprivilegemap。

然后，需要将上篇博客中的userrolelistmap修改为：

<resultmap id="userrolelistmap" type="com.zwwhnly.mybatisaction.model.sysuserextend" extends="sysusermap">
    <collection property="sysrolelist" columnprefix="role_"
                resultmap="com.zwwhnly.mybatisaction.mapper.sysrolemapper.roleprivilegelistmap">
    </collection>
</resultmap>

并且要修改上篇博客中id为selectalluserandroles的select标签代码，因为要关联角色权限关系表和权限表：

<select id="selectalluserandroles" resultmap="userrolelistmap">
    select  u.id,
            u.user_name,
            u.user_password,
            u.user_email,
            u.create_time,
            r.id role_id,
            r.role_name role_role_name,
            r.enabled role_enabled,
            r.create_by role_create_by,
            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 sys_user u
    inner join sys_user_role ur on u.id = ur.user_id
    inner join sys_role r on ur.role_id = r.id
    inner join sys_role_privilege rp on rp.role_id = r.id
    inner join sys_privilege p on p.id = rp.privilege_id
</select>

注意事项：

这里sys_privilege表的列名的别名前缀为role_privilege_，这是因为userrolelistmap中collection的columnprefix属性为role_，并且指定的com.zwwhnly.mybatisaction.mapper.sysrolemapper.roleprivilegelistmap中collection的columnprefix属性为privilege_，所以这里的前缀需要叠加，就变成了role_privilege_。

3. 单元测试

修改上篇博客中建的测试方法testselectalluserandroles()代码为：

@test
public void testselectalluserandroles() {
    sqlsession sqlsession = getsqlsession();

    try {
        sysusermapper sysusermapper = sqlsession.getmapper(sysusermapper.class);

        list<sysuserextend> sysuserlist = sysusermapper.selectalluserandroles();
        system.out.println("用户数：" + sysuserlist.size());
        for (sysuserextend sysuser : sysuserlist) {
            system.out.println("用户名：" + sysuser.getusername());
            for (sysroleextend sysroleextend : sysuser.getsysrolelist()) {
                system.out.println("角色名：" + sysroleextend.getrolename());
                for (sysprivilege sysprivilege : sysroleextend.getsysprivilegelist()) {
                    system.out.println("权限名：" + sysprivilege.getprivilegename());
                }
            }
        }
    } finally {
        sqlsession.close();
    }
}

运行测试代码，测试通过，输出日志如下：

debug [main] - ==> preparing: select u.id, u.user_name, u.user_password, u.user_email, u.create_time, r.id role_id, r.role_name role_role_name, r.enabled role_enabled, r.create_by role_create_by, 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 sys_user u inner join sys_user_role ur on u.id = ur.user_id inner join sys_role r on ur.role_id = r.id inner join sys_role_privilege rp on rp.role_id = r.id inner join sys_privilege p on p.id = rp.privilege_id

debug [main] - ==> parameters:

trace [main] - <== columns: id, user_name, user_password, user_email, create_time, role_id, role_role_name, role_enabled, role_create_by, role_create_time, role_privilege_id, role_privilege_privilege_name, role_privilege_privilege_url

trace [main] - <== row: 1, admin, 123456, [e-mail], 2019-06-27 18:21:07.0, 1, 管理员, 1, 1, 2019-06-27 18:21:12.0, 1, 用户管理, /users

trace [main] - <== row: 1, admin, 123456, [e-mail], 2019-06-27 18:21:07.0, 1, 管理员, 1, 1, 2019-06-27 18:21:12.0, 2, 角色管理, /roles

trace [main] - <== row: 1, admin, 123456, [e-mail], 2019-06-27 18:21:07.0, 1, 管理员, 1, 1, 2019-06-27 18:21:12.0, 3, 系统日志, /logs

trace [main] - <== row: 1, admin, 123456, [e-mail], 2019-06-27 18:21:07.0, 2, 普通用户, 1, 1, 2019-06-27 18:21:12.0, 4, 人员维护, /persons

trace [main] - <== row: 1, admin, 123456, [e-mail], 2019-06-27 18:21:07.0, 2, 普通用户, 1, 1, 2019-06-27 18:21:12.0, 5, 单位维护, /companies

trace [main] - <== row: 1001, test, 123456, [e-mail], 2019-06-27 18:21:07.0, 2, 普通用户, 1, 1, 2019-06-27 18:21:12.0, 4, 人员维护, /persons

trace [main] - <== row: 1001, test, 123456, [e-mail], 2019-06-27 18:21:07.0, 2, 普通用户, 1, 1, 2019-06-27 18:21:12.0, 5, 单位维护, /companies

debug [main] - <== total: 7

用户数：2

用户名：admin

角色名：管理员

权限名：用户管理

权限名：角色管理

权限名：系统日志

角色名：普通用户

权限名：人员维护

权限名：单位维护

用户名：test

角色名：普通用户

权限名：人员维护

权限名：单位维护

从日志可以看出，不仅查询出了用户拥有的角色信息，也查询出了角色包含的权限信息。

4. 延迟加载

有的同学可能会说，返回的角色信息和权限信息我不一定用啊，每次关联这么多表查询一次数据库，好影响性能啊，能不能在我使用到角色信息即获取sysrolelist属性时再去数据库查询呢？答案当然是能，那么如何实现呢？

实现延迟加载需要使用collection标签的fetchtype属性，该属性有lazy和eager两个值，分别代表延迟加载和积极加载。

由于需要根据角色id获取该角色对应的所有权限信息，所以我们要先在sysprivilegemapper.xml中定义如下查询：

<select id="selectprivilegebyroleid" resultmap="sysprivilegemap">
    select p.*
    from sys_privilege p
    inner join sys_role_privilege rp on rp.privilege_id = p.id
    where rp.role_id = #{roleid}
</select>

然后在sysrolemapper.xml中添加如下查询：

<resultmap id="roleprivilegelistmapselect" extends="rolemap"
           type="com.zwwhnly.mybatisaction.model.sysroleextend">
    <collection property="sysprivilegelist" fetchtype="lazy"
                column="{roleid=id}"
                select="com.zwwhnly.mybatisaction.mapper.sysprivilegemapper.selectprivilegebyroleid"/>
</resultmap>

<select id="selectrolebyuserid" resultmap="roleprivilegelistmapselect">
    select
          r.id,
          r.role_name,
          r.enabled,
          r.create_by,
          r.create_time
    from sys_role r
    inner join sys_user_role ur on ur.role_id = r.id
    where ur.user_id = #{userid}
</select>

上面的column="{roleid=id}"中，roleid指的是select指定的方法selectprivilegebyroleid的参数，id指的是查询selectrolebyuserid中查询出的角色id。

然后在sysusermapper.xml中添加如下查询：

<resultmap id="userrolelistmapselect" extends="sysusermap"
           type="com.zwwhnly.mybatisaction.model.sysuserextend">
    <collection property="sysrolelist" fetchtype="lazy"
                select="com.zwwhnly.mybatisaction.mapper.sysrolemapper.selectrolebyuserid"
                column="{userid=id}"/>
</resultmap>

<select id="selectalluserandrolesselect" resultmap="userrolelistmapselect">
    select
          u.id,
          u.user_name,
          u.user_password,
          u.user_email,
          u.create_time
    from sys_user u
    where u.id = #{id}
</select>

上面的column="{userid=id}"中，userid指的是select指定的方法selectrolebyuserid的参数，id指的是查询selectalluserandrolesselect中查询出的用户id。

然后，在sysusermapper接口中，添加如下方法：

/**
 * 通过嵌套查询获取指定用户的信息以及用户的角色和权限信息
 *
 * @param id
 * @return
 */
sysuserextend selectalluserandrolesselect(long id);

最后，在sysusermappertest类中添加如下测试方法：

@test
public void testselectalluserandrolesselect() {
    sqlsession sqlsession = getsqlsession();

    try {
        sysusermapper sysusermapper = sqlsession.getmapper(sysusermapper.class);

        sysuserextend sysuserextend = sysusermapper.selectalluserandrolesselect(1l);
        system.out.println("用户名：" + sysuserextend.getusername());
        for (sysroleextend sysroleextend : sysuserextend.getsysrolelist()) {
            system.out.println("角色名：" + sysroleextend.getrolename());
            for (sysprivilege sysprivilege : sysroleextend.getsysprivilegelist()) {
                system.out.println("权限名：" + sysprivilege.getprivilegename());
            }
        }
    } finally {
        sqlsession.close();
    }
}

运行测试方法，输出日志如下：

debug [main] - ==> preparing: select u.id, u.user_name, u.user_password, u.user_email, u.create_time from sys_user u where u.id = ?

debug [main] - ==> parameters: 1(long)

trace [main] - <== columns: id, user_name, user_password, user_email, create_time

trace [main] - <== row: 1, admin, 123456, [e-mail], 2019-06-27 18:21:07.0

debug [main] - <== total: 1

用户名：admin

debug [main] - ==> preparing: select r.id, r.role_name, r.enabled, r.create_by, r.create_time from sys_role r inner join sys_user_role ur on ur.role_id = r.id where ur.user_id = ?

debug [main] - ==> parameters: 1(long)

trace [main] - <== columns: id, role_name, enabled, create_by, create_time

trace [main] - <== row: 1, 管理员, 1, 1, 2019-06-27 18:21:12.0

trace [main] - <== row: 2, 普通用户, 1, 1, 2019-06-27 18:21:12.0

debug [main] - <== total: 2

角色名：管理员

debug [main] - ==> preparing: select p.* from sys_privilege p inner join sys_role_privilege rp on rp.privilege_id = p.id where rp.role_id = ?

debug [main] - ==> parameters: 1(long)

trace [main] - <== columns: id, privilege_name, privilege_url

trace [main] - <== row: 1, 用户管理, /users

trace [main] - <== row: 2, 角色管理, /roles

trace [main] - <== row: 3, 系统日志, /logs

debug [main] - <== total: 3

权限名：用户管理

权限名：角色管理

权限名：系统日志

角色名：普通用户

debug [main] - ==> preparing: select p.* from sys_privilege p inner join sys_role_privilege rp on rp.privilege_id = p.id where rp.role_id = ?

debug [main] - ==> parameters: 2(long)

trace [main] - <== columns: id, privilege_name, privilege_url

trace [main] - <== row: 4, 人员维护, /persons

trace [main] - <== row: 5, 单位维护, /companies

debug [main] - <== total: 2

权限名：人员维护

权限名：单位维护

仔细分析上面的日志，会发现只有在使用到了角色信息和权限信息时，才执行了对应的数据库查询。

需要注意的是，延迟加载依赖于mybatis全局配置中的aggressivelazyloading，在之前的博客讲解association标签时，我们已经将其配置为了false，所以这里的执行结果符合我们的预期：

<settings>
    <!--其他配置-->
    <setting name="aggressivelazyloading" value="false"/>
</settings>

关于该参数的详细讲解，请查看mybatis从入门到精通(十)：使用association标签实现嵌套查询。

5. 总结

使用collection标签实现嵌套查询，用到的属性总结如下：

1)select：另一个映射查询的id，mybatis会额外执行这个查询获取嵌套对象的结果。

2)column：将主查询中列的结果作为嵌套查询的参数，配置方式如column="{prop1=col1,prop2=col2}",prop1和prop2将作为嵌套查询的参数。

3)fetchtype：数据加载方式，可选值为lazy和eager，分别为延迟加载和积极加载。

4)如果要使用延迟加载，除了将fetchtype设置为lazy，还需要注意全局配置aggressivelazyloading的值应该为false。这个参数在3.4.5版本之前默认值为ture，从3.4.5版本开始默认值改为false。

5)mybatis提供的lazyloadtriggermethods参数，支持在触发某方法时直接触发延迟加载属性的查询，如equals()方法。

6. 源码及参考

源码地址：，欢迎下载。

刘增辉《mybatis从入门到精通》

7. 最后

打个小广告，欢迎扫码关注微信公众号：「申城异乡人」，不定期分享java技术干货，让我们一起进步。