java 查詢資料庫並生成多層children

Liu66~發表於2024-08-24
Java資料庫

首先，定義一個表示組織結構的簡單類：

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 欄位。

相關文章