java 查询数据库并生成多层children

首先，定义一个表示组织结构的简单类：

public class Organization {
    private int id;
    private int parentId;
    private String name;
    private List<Organization> children;
 
    // 省略构造函数、getter和setter
}

然后，编写一个方法来查询数据库并构建多层嵌套的children列表：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
 
public class OrganizationService {
 
    public List<Organization> getOrganizationHierarchy(int rootId) throws Exception {
        List<Organization> organizations = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/yourdatabase", "username", "password");
             PreparedStatement pstmt = conn.prepareStatement("SELECT id, parent_id, name FROM organization WHERE parent_id = ?");
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                Organization organization = new Organization();
                organization.setId(rs.getInt("id"));
                organization.setParentId(rs.getInt("parent_id"));
                organization.setName(rs.getString("name"));
                organization.setChildren(getChildren(organization.getId(), conn)); // 递归获取子组织
                organizations.add(organization);
            }
        }
        return organizations;
    }
 
    private List<Organization> getChildren(int parentId, Connection conn) throws Exception {
        List<Organization> children = new ArrayList<>();
        try (PreparedStatement pstmt = conn.prepareStatement("SELECT id, parent_id, name FROM organization WHERE parent_id = ?");) {
            pstmt.setInt(1, parentId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Organization child = new Organization();
                    child.setId(rs.getInt("id"));
                    child.setParentId(rs.getInt("parent_id"));
                    child.setName(rs.getString("name"));
                    child.setChildren(getChildren(child.getId(), conn)); // 递归获取子组织的子组织
                    children.add(child);
                }
            }
        }
        return children;
    }
}

在这个例子中，getOrganizationHierarchy 方法用于获取根节点的组织层级结构，而 getChildren 方法用于递归地获取一个组织的所有子组织。注意，这里假设你已经有了JDBC驱动和数据库配置信息，并且数据库中有一个名为 organization 的表，其中包含 id、parent_id 和 name 字段。