spring boot mybatis 连表查询返回树状结构的数据

在使用 Spring Boot 和 MyBatis 进行连表查询时，可以通过递归的方式将查询结果转换成树状结构的数据。

假设有两张表：部门表和员工表，部门表中有一个 parent_id 字段表示该部门的上级部门，员工表中有一个 dept_id 字段表示该员工所在的部门。现在需要查询出所有部门以及下属员工，并将其转换成树状结构的数据。

首先，需要定义一个部门实体类和一个员工实体类，代码如下：

public class Department {
    private Long id;
    private String name;
    private Long parentId;
    private List<Employee> employees;
    // getter 和 setter 略
}

public class Employee {
    private Long id;
    private String name;
    private Long deptId;
    // getter 和 setter 略
}

接下来，可以使用 MyBatis 进行连表查询，代码如下：

<select id="getDepartments" resultMap="departmentResultMap">
    select d.id, d.name, d.parent_id, e.id as employee_id, e.name as employee_name, e.dept_id as employee_dept_id
    from department d
    left join employee e on d.id = e.dept_id
    order by d.id, e.id
</select>

<resultMap id="departmentResultMap" type="Department">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="parentId" column="parent_id"/>
    <collection property="employees" ofType="Employee" resultMap="employeeResultMap" columnPrefix="employee_"/>
</resultMap>

<resultMap id="employeeResultMap" type="Employee">
    <id property="id" column="employee_id"/>
    <result property="name" column="employee_name"/>
    <result property="deptId" column="employee_dept_id"/>
</resultMap>

注意，这里使用了 MyBatis 的 resultMap 来映射查询结果到实体类中。

接下来，可以编写一个递归方法，将查询结果转换成树状结构的数据，代码如下：

private List<Department> buildTree(List<Department> departments) {
    Map<Long, Department> map = new HashMap<>();
    for (Department department : departments) {
        map.put(department.getId(), department);
    }
    List<Department> roots = new ArrayList<>();
    for (Department department : departments) {
        Long parentId = department.getParentId();
        if (parentId == null || parentId == 0) {
            roots.add(department);
        } else {
            Department parent = map.get(parentId);
            if (parent != null) {
                parent.getEmployees().add(department.getEmployees().get(0));
            }
        }
    }
    for (Department root : roots) {
        buildChildren(map, root);
    }
    return roots;
}

private void buildChildren(Map<Long, Department> map, Department department) {
    List<Department> children = new ArrayList<>();
    for (Employee employee : department.getEmployees()) {
        Department child = map.get(employee.getDeptId());
        if (child != null) {
            children.add(child);
            buildChildren(map, child);
        }
    }
    department.setChildren(children);
}

最后，在控制器中调用这个递归方法即可，代码如下：

@GetMapping("/departments")
public List<Department> getDepartments() {
    List<Department> departments = departmentMapper.getDepartments();
    return buildTree(departments);
}

这样就可以返回树状结构的数据了。