java - 从表构造树

Question

我的数据库中有一个像这样的表:

等等...

如您所见，有多个根父级(没有parent_id 的根父级)，每个类别都有 n 个子级。

我想使用此类将其转换为 Java 中的树结构:

private int id;
private String name;
private int parent;
private List<Category> children;

我通过此查询获取数据，我认为它可以改进:

SELECT c.*, ca.name, NVL(ca.parent_id, -1) AS parent_id FROM 
( 
    SELECT id, name, parent_id FROM categories 
) ca, 
( 
    SELECT LISTAGG(id || ':' || name || ':' || DECODE(parent_id, NULL, 
    DECODE(id,    NULL, NULL, -1), parent_id), ';') 
    WITHIN GROUP (ORDER BY id) AS  children, parent_id AS id 
    FROM categories 
    GROUP BY parent_id HAVING parent_id IS NOT NULL 
) c 
WHERE c.id = ca.id

我获取每个类别(id、name 和parent_id)及其子项的字符串。

然后我循环抛出每个结果集

List<Category> categories = new ArrayList<Category>();

while (rs.next()) { 
    Category c = new Category();
    c = JdbcToModel.convertToCategory(rs); //
    if (c.getParent() == -1) { // parent_id is null in database
        categories.add(c);
     else {
        categories = JdbcToModel.addCategoryToTree(categories, c);

    }
}

方法convertToCategory:

公共(public)静态类别convertToCategory(ResultSet rs){

Category toRet = new Category();
List<Category> children = new ArrayList<Category>();
try {       
    children = parseCategoriesFromReview(rs.getString("children"));
    toRet.setId(rs.getInt("id"));
    toRet.setName(rs.getString("name"));
    toRet.setParent(rs.getInt("parent_id"));
    toRet.setChildren(children);

} catch (Exception e) {
    e.printStackTrace();
}
return toRet;   

}

当我解析子字符串时的方法parseCategoriesFromReview:

public static List<Category> parseCategoriesFromReview(String categoriesString) {

        List<Category> toRet = new ArrayList<Category>();
        try {       
            if (!categoriesString.equals("::")) {

                String [] categs = categoriesString.split(";");
                for (String categ : categs) {
                    String [] category = categ.split(":");
                    Category c = new Category(Integer.parseInt(category[0]), category[1], Integer.parseInt(category[2]), new ArrayList<Category>());
                    toRet.add(c);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return toRet;   
    }

以及递归方法addCategoryToTree:

public static List<Category> addCategoryToTree(List<Category> categories, Category c) { 
    try {       
        for (Category ct : categories) {
            if (ct.getId() == c.getParent()) {
                ct.getChildren().add(c);
                break;
            } else {
                return addCategoryToTree(ct.getChildren(), c);
            }
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
    return categories;

}

我认为最大的问题在于这个方法......我从来没有编写过内部有循环的递归方法，我不知道它是否正确。重点是我得到了一个树结构，但只有几个类别。最终的树没有那么多。

也许我让事情变得复杂，但我不知道如何以其他方式做到这一点..

有人帮忙吗？

问候!

Answer 1

Oracle 设置:

CREATE TABLE categories ( id, name, parent_id ) AS
SELECT  1, 'Restauracion', NULL FROM DUAL UNION ALL
SELECT  2, 'Desayuno',        1 FROM DUAL UNION ALL
SELECT  3, 'Calidad',         2 FROM DUAL UNION ALL
SELECT  4, 'Organizacion',    2 FROM DUAL UNION ALL
SELECT  5, 'Variedad',        2 FROM DUAL UNION ALL
SELECT  6, 'Personal',     NULL FROM DUAL UNION ALL
SELECT  7, 'Pisos',           6 FROM DUAL UNION ALL
SELECT  8, 'Falta de Personal', 7 FROM DUAL UNION ALL
SELECT  9, 'Trato',           7 FROM DUAL UNION ALL
SELECT 10, 'Informacion',     7 FROM DUAL UNION ALL
SELECT 11, 'Idiomas',         7 FROM DUAL UNION ALL
SELECT 12, 'Otros',           7 FROM DUAL;

Java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class Category {
    private final String name;
    private final int id;
    private final Category parent;
    private final ArrayList<Category> children = new ArrayList<>();

    private Category(final String name, final int id, final Category parent) {
        this.name   = name;
        this.id     = id;
        this.parent = parent;
        if ( parent != null )
            parent.children.add(this);
    }

    @Override
    public String toString(){
        final StringBuffer buffer = new StringBuffer();
        buffer.append( '<' );
        buffer.append(name);
        buffer.append(':');
        buffer.append(id);
        buffer.append(':');
        buffer.append(parent == null ? "" : parent.name );
        buffer.append( '>' );
        return buffer.toString();
    }

    public String toHierarchyString(){
        return toHierarchyString(0);
    }

    private String toHierarchyString( int level ){
        final StringBuffer buffer = new StringBuffer();
        for ( int i = 0; i < level; i++ )
            buffer.append('\t');
        buffer.append( toString() );
        buffer.append( '\n' );
        for ( final Category child : children )
            buffer.append( child.toHierarchyString(level+1));
        return buffer.toString();
    }
    public static ArrayList<Category> loadCategoriesFromDatabase(){
        try{

            Class.forName("oracle.jdbc.OracleDriver");

            final Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","TEST","TEST");
            final PreparedStatement st = con.prepareStatement(
                    "SELECT id, name, parent_id " +
                    "FROM  categories " +
                    "START WITH parent_id IS NULL " +
                    "CONNECT BY PRIOR id = PARENT_ID " +
                    "ORDER SIBLINGS BY name"
            );

            final ResultSet cursor = st.executeQuery();

            final HashMap<Integer,Category> categoryMap = new HashMap<>();
            final ArrayList<Category> categories = new ArrayList<>();

            while ( cursor.next() )
            {
                final String name       = cursor.getString("NAME");
                final int id            = cursor.getInt("ID");
                final Integer parent_id = cursor.getInt("PARENT_ID");
                final Category parent   = categoryMap.get( parent_id );
                final Category category = new Category( name, id, parent );
                categoryMap.put(id, category);
                if ( parent == null )
                    categories.add(category);
            }
            return categories;
        } catch(ClassNotFoundException | SQLException e) {
            System.out.println(e);
        }
        return null;
    }

    public static void main( final String[] args ){
        ArrayList<Category> categories = loadCategoriesFromDatabase();
        for ( final Category cat : categories )
            System.out.println( cat.toHierarchyString() );
    }
}

输出:

<Personal:6:>
    <Pisos:7:Personal>
        <Falta de Personal:8:Pisos>
        <Idiomas:11:Pisos>
        <Informacion:10:Pisos>
        <Otros:12:Pisos>
        <Trato:9:Pisos>

<Restauracion:1:>
    <Desayuno:2:Restauracion>
        <Calidad:3:Desayuno>
        <Organizacion:4:Desayuno>
        <Variedad:5:Desayuno>