mybatis一次性查询出部门树等结构数据的简单示例（递归方式）

前言

本例以部门树为演示，其他树形结构大同小异。

一、前端页面的数据展示结构如下：

查看数据库：

说明：每个部门在数据库里都是一条记录，都有父节点parentId，这里根节点股东会的值是-1。

二、编写后端代码

1. controller层

/**
 * 部门管理
 *
 * @author chao.zhang
 * @version 1.0.0
 * @date 2020/8/24 10:13
 */
@RestController
@RequestMapping("/system/basic/department")
public class DepartmentController {

    @Autowired
    private IDepartmentService departmentService;

    @GetMapping("/")
    public Res getAllDepartments() {
        return Res.ok(departmentService.getAllDepartments());
    }
}

2. service和serviceImpl层，因为递归查所有的，所以把根节点-1传进去：

/**
 * <p>
 *  部门管理服务接口类
 * </p>
 *
 * @author zhangchao
 * @since 2020-05-21
 */
public interface IDepartmentService extends IService<Department> {

    Res getAllDepartments();
}

/**
 * <p>
 *  部门管理服务接口实现类
 * </p>
 *
 * @author zhangchao
 * @since 2020-05-21
 */
@Service
public class DepartmentServiceImpl extends ServiceImpl<DepartmentMapper, Department> implements IDepartmentService {

    @Override
    public Res getAllDepartments() {
        return Res.ok(baseMapper.getDepartmentsByParentId(-1));
    }
}

3. Mapper层

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author zhangchao
 * @since 2020-05-21
 */
public interface DepartmentMapper extends BaseMapper<Department> {

    List<Department> getDepartmentsByParentId(Integer pid);
}

4. xml中定义BaseResultMap和编写SQL

<?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="com.junya.mapper.DepartmentMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.junya.entity.Department">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="parentId" property="parentId" />
        <result column="depPath" property="depPath" />
        <result column="enabled" property="enabled" />
        <result column="isParent" property="isParent" />
    </resultMap>

    <resultMap id="DepartmentWithChildren" type="com.junya.entity.Department" extends="BaseResultMap">
        <collection property="children" ofType="com.junya.entity.Department"
                    select="com.junya.mapper.DepartmentMapper.getDepartmentsByParentId" column="id">
        </collection>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name, parentId, depPath, enabled, isParent
    </sql>

    <select id="getDepartmentsByParentId" resultMap="DepartmentWithChildren">
        SELECT
            *
        FROM
            `department`
        WHERE
            parentId = #{pid}
    </select>

</mapper>

自定义DepartmentWithChildren，继承BaseResultMap。在collection 标签的select属性继续调用自身，并且把它的id传进去查询它所属的子部门。

5. 打开postman测试：

扩展：如果知道一共有几个层级的话可以不用递归，直接写，比如我项目里的菜单权限这块，一共就3层：

 <resultMap id="MenuWithChildren" type="com.junya.entity.Menu" extends="BaseResultMap">
        <id column="id1" property="id"/>
        <id column="name1" property="name"/>
        <collection property="children" ofType="com.junya.entity.Menu">
            <id column="id2" property="id"/>
            <id column="name2" property="name"/>
            <collection property="children" ofType="com.junya.entity.Menu">
                <id column="id3" property="id"/>
                <id column="name3" property="name"/>
            </collection>
        </collection>
    </resultMap>

    <select id="getAllMenus" resultMap="MenuWithChildren">
        SELECT
            m1.id id1,
            m1.`name` name1,
            m2.id id2,
            m2.`name` name2,
            m3.id id3,
            m3.`name` name3
        FROM
            menu m1,
            menu m2,
            menu m3
        WHERE
            m1.id = m2.parentId
            AND m2.id = m3.parentId
            AND m3.enabled = 1
        ORDER BY
            m1.id,
            m2.id,
            m3.id
    </select>