java 查询一个用户并且显示他的部门树结构

首先需要明确的是，部门树结构是由部门之间的上下级关系构成的，而一个用户只属于一个部门，因此查询用户并显示其所在的部门树结构需要分两步进行：

1. 查询用户所在的部门
2. 根据部门的上下级关系构建部门树结构，并将用户所在的部门及其祖先部门一起显示出来

下面是一个示例代码，假设有如下的部门表：

| id | name | parent_id |
| ---- | -------- | --------- |
| 1 | 公司 | null |
| 2 | 技术部 | 1 |
| 3 | 开发组 | 2 |
| 4 | 测试组 | 2 |
| 5 | 市场部 | 1 |
| 6 | 销售组 | 5 |

以及如下的用户表：

| id | name | department_id |
| ---- | ------ | ------------- |
| 1 | 张三 | 3 |
| 2 | 李四 | 4 |
| 3 | 王五 | 6 |

代码如下：

import java.sql.*;
import java.util.*;

public class UserDepartmentTree {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/test";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "root";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            // 查询用户所在的部门
            String queryUserSql = "SELECT u.name, d.id, d.name AS department_name, d.parent_id FROM user u JOIN department d ON u.department_id = d.id WHERE u.id = ?;";
            PreparedStatement queryUserStmt = conn.prepareStatement(queryUserSql);
            queryUserStmt.setInt(1, 1); // 假设查询用户id为1
            ResultSet userResult = queryUserStmt.executeQuery();
            if (!userResult.next()) {
                System.out.println("User not found!");
                return;
            }
            String userName = userResult.getString("name");
            int departmentId = userResult.getInt("id");
            String departmentName = userResult.getString("department_name");
            int parentDepartmentId = userResult.getInt("parent_id");

            // 根据部门的上下级关系构建部门树结构
            String queryDepartmentSql = "SELECT id, name, parent_id FROM department;";
            Statement queryDepartmentStmt = conn.createStatement();
            ResultSet departmentResult = queryDepartmentStmt.executeQuery(queryDepartmentSql);

            Map<Integer, Department> departmentMap = new HashMap<>();
            while (departmentResult.next()) {
                int id = departmentResult.getInt("id");
                String name = departmentResult.getString("name");
                int parentId = departmentResult.getInt("parent_id");
                departmentMap.put(id, new Department(id, name, parentId));
            }

            Department department = departmentMap.get(departmentId);
            List<String> departmentNames = new ArrayList<>();
            departmentNames.add(department.getName());
            while (department.getParentId() != 0) {
                department = departmentMap.get(department.getParentId());
                departmentNames.add(0, department.getName());
            }

            // 将用户所在的部门及其祖先部门一起显示出来
            StringBuilder sb = new StringBuilder();
            sb.append(userName).append("所在的部门：");
            for (String name : departmentNames) {
                sb.append(name).append(" -> ");
            }
            sb.delete(sb.length() - 4, sb.length());
            System.out.println(sb.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    static class Department {
        private int id;
        private String name;
        private int parentId;

        public Department(int id, String name, int parentId) {
            this.id = id;
            this.name = name;
            this.parentId = parentId;
        }

        public int getId() {
            return id;
        }

        public String getName() {
            return name;
        }

        public int getParentId() {
            return parentId;
        }
    }
}

执行结果：

张三所在的部门：公司 -> 技术部 -> 开发组