Mybatis查询树形数据的两种方法

最近开发中遇到了很多树形结构数据的需要，利用mybatis提供嵌套查询功能，基本上可以完美解决，但是对于其中的原理并不理解，导致在使用的时候像瞎猫碰死耗子一样，照着先前成功的例子copy，后来遇到了莫名奇怪的报错迟迟不能解决，于是百度了一番，大致了解了背后的原理，整理如下。

以简单的角色-菜单为例

表结构

其中menu为菜单表，role为角色表，roleandmenu是中间表，角色和菜单为多对多的关系，现在我们需要下图所示的实体类

 import java.util.List;
 public class RoleInfo {
     private Integer roleid;
     private String rolename;
     private List<Menu> menulist;
     public Integer getRoleid() {
         return roleid;
     }
     public void setRoleid(Integer roleid) {
         this.roleid = roleid;
     }
     public String getRolename() {
         return rolename;
     }
     public void setRolename(String rolename) {
         this.rolename = rolename;
     }
     public List<Menu> getMenulist() {
         return menulist;
     }
     public void setMenulist(List<Menu> menulist) {
         this.menulist = menulist;
     }
     @Override
     public String toString() {
         return "RoleInfo [roleid=" + roleid + ", rolename=" + rolename + ", menulist=" + menulist + "]";
     }
 }

第一种方法:利用嵌套语句查询

 <resultMap type="com.test.mybatis.model.RoleInfo" id="roleModel">
         <id column="id" property="roleid"/>
         <result column="name" property="rolename"/>
         <collection property="menulist" select="getMenu" column="id">
         </collection>
     </resultMap>
     <select id="getRoleInfo" resultMap="roleModel">
         select id,name from role
     </select>
     <select id="getMenu" resultType="com.test.mybatis.model.Menu">
         select m.id,m.name 
         from menu m join roleandmenu ram on m.id=ram.menuId
         where ram.roleId=#{id}
     </select>

     @Test
     public void testRoleAndMenu() throws IOException {
         Reader reader = Resources.getResourceAsReader("mybatis.xml");
         SqlSessionFactory sqlsessionfac = new SqlSessionFactoryBuilder().build(reader);
         SqlSession sqlsession = sqlsessionfac.openSession();
         try {
             RoleAndMenuMapper mapper = sqlsession.getMapper(RoleAndMenuMapper.class);
             System.out.println(JSONObject.toJSON(mapper.getRoleInfo()));
         } catch (Exception e) {
             // TODO: handle exception
             e.printStackTrace();
         } finally {
             sqlsession.close();
         }
     }

结果：

原理如下：

1.mybatis先执行getRoleInfo这个查询，获取结果集

2.从ResultSet中逐一取出记录，构建RoleInfo对象并为映射属性赋值

3.赋值过程中发现目标menulist属性配置了一个关联集合（collection），此时执行id为collection标签中select属性值（getMenu）的查询，并将当前记录中的id属性作为此查询的参数。（association标签同理）

4.将关联查询返回的结果映射到meunlist属性

5.执行步骤2，直至ResultSet.next=false

6.返回查询结果

这种方式的好处在于简单易懂，通过简单的配置就可以达到目标效果。不足之处在于如果结果集记录条数过大，会造成较大的数据库访问消耗，因为在从ResultSet中取出记录的时候每取一条，便执行一次关联查询，假设一次查询的结果集有10条记录，则数据库的访问次数为：关联查询次数(10)+返回结果集的查询(1)=11次。

需要注意的地方

1.collection/association标签的column属性：当向关联查询传递的参数个数为1时，column的值应为结果集中的列名，而不是映射属性名（property），上面的例子中，向关联查询传递id值，column的值应为id而不是roleid。

可以向关联查询传递多个参数，此时column的值为多个键值对，如下图

此时向关联查询传递了两个参数id和name，此时还应该将关联的查询的parameterType改为java.util.Map，否则关联查询无法接受参数

2.在进行单一类型树形结构查询的时候，需要注意关联查询的结果集中的列是否有作为查询条件的列

这样说可能比较别扭，以上面的menu表为例，有一个parent列用于存储父部门的ID，使用嵌套查询获取以下实体类

 import java.util.List;
 public class MenuTree {
     private Integer id;
     private String name;
     private List<Menu> children;
     public Integer getId() {
         return id;
     }
     public void setId(Integer id) {
         this.id = id;
     }
     public String getName() {
         return name;
     }
     public void setName(String name) {
         this.name = name;
     }
     public List<Menu> getChildren() {
         return children;
     }
     public void setChildren(List<Menu> children) {
         this.children = children;
     }
 }

此时会产生一个问题：每个顶级菜单(parent为空的菜单)的子菜单只有一个查询结果，这是因为在关联查询getSubMenu中没有将id查询出来，而关联查询和主查询的resultMap一样，所以关联查询在映射结果集的时候就会再次去执行关联查询，而由于本次关联查询并没有取出id这个作为参数的属性，所以实际上只执行了N（结果集记录数）次_关联查询_。

因此，在这种情况中，必须在关联查询中查询出id这个列，否则会查询不出预期结果。

第二种方法：使用嵌套结果集

 <resultMap type="com.test.mybatis.model.RoleInfo" id="roleModel">
         <id column="id" property="roleid"/>
         <result column="name" property="rolename"/>
         <collection property="menulist" ofType="com.test.mybatis.model.Menu">
             <id column="menuid" property="id"/>
             <result column="menuname" property="name"/>
             <result column="description" property="description"/>
             <result column="parent" property="parent"/>
             <result column="createdate" property="createdate"/>
             <result column="modifydate" property="modifydate"/>
         </collection>
     </resultMap>
     <select id="getRoleInfo" resultMap="roleModel">
         select 
             ram.roleid as id,
             ro.name as name,
             me.id as menuid,
             me.name as menuname,
             me.description,
             me.parent,
             me.createdate,
             me.modifydate 
         from roleandmenu ram
             left outer join role ro on ram.roleid=ro.id
             left outer join menu me on ram.menuid=me.id
     </select>

原理是通过关联查询，一次性将数据查询出来，然后根据resultMap的配置进行转换，构建目标实体类。

显然，这种方法更为直接，只需要访问一次数据库就可以了，不会造成严重的数据库访问消耗。

但是以上是查询出全部数据的情况，因为只有查询出全部数据，才能得到最终结果。如果直接分页的话，会导致数据被截断，也就是collection中的数据残缺。

这种情况最好的处理方式就是手动分页，对主表分页，对其他连接的表不分页。

将上面的SQL改为

SELECT
    base.id,
    ro. NAME AS NAME,
    me.id AS menuid,
    me. NAME AS menuname,
    me.description,
    me.parent,
    me.createdate,
    me.modifydate
FROM
    (
        SELECT
            roleid AS id,
            menuid
        FROM
            roleandmenu
        LIMIT $ { pageNum }, $ { pageSize }
    ) AS base
LEFT OUTER JOIN role ro ON base.id = ro.id
LEFT OUTER JOIN menu me ON base.menuid = me.id

这里手动传入pageNum,pageSize，对主表roleandmenu分页，从表role和menu不分页。

这样就可以得到正确的数据。

此外，还有一种情况，如果把嵌套结果集的返回值类型全部改成HashMap的话，会导致menulist里只有一行数据

解决的办法是，给collection标签的javaType赋值为目标集合类型

找了很久，终于在官方文档里找到了解释