数据库多层数据运用递归算法生成树形数据

第一步：准备表结构及对应的表数据

1.表结构

create table City
(
id char(32) not null,
name varchar2(30),
parent_id char(32) //父级字段
)

2.插入数据

insert into City (id, name, parent_id) values (1, '书籍', 0);
insert into City (id, name, parent_id) values (2, '科普书籍', 1);
insert into City (id, name, parent_id) values (3, '教科书籍', 1);
insert into City (id, name, parent_id) values (4, '童话书籍', 1);
insert into City (id, name, parent_id) values (5, '语文', 3);
insert into City (id, name, parent_id) values (6, '数学', 3);
insert into City (id, name, parent_id) values (7, '九年级上册语文书', 5);
insert into City (id, name, parent_id) values (8, '九年级下册语文书', 5);
insert into City (id, name, parent_id) values (9, '九年级上册数学书', 6);
insert into City (id, name, parent_id) values (10, '九年级下册数学书', 6);
insert into City (id, name, parent_id) values (11, '十万个为什么', 2);
insert into City (id, name, parent_id) values (12, '格林童话', 4);

第二步：建立Entity的Bean对象

1.建立Bean

public class CityEntity  implements Serializable {
private String id;
private String name;
private String parent_id;

//getter，setter
}

2.建立对外输出的响应Bean

public class CityResponse {
    private String id;
    private String name;
    private String parent_id;
    private List children = new ArrayList();

    //getter，setter
    public CityResponse() {
    }

    public CityResponse(CityEntity entity) {
        this.set(entity);
    }

    public CityResponse set(CityEntity entity) {
        this.id = entity.getId;
        this.name = entity.getName;
        this.parentId = entity.getParentId;

        return this;
    }
}

第三步 进行递归遍历，生成树形接口

1.测试代码：

public class CityTest {
@Test
public void cityTree() throws Exception{
System.out.println(recursiveTree(1));
}

2.递归方法

 public CityResponse getTreeModule(String id) throws Exception {
        CityResponse firstTreeResponse = new CityResponse();
        //根据id获取节点对象(SELECT * FROM City c WHERE c.id=?)
        CityEntity entity = cityService.findById(id);
        firstTreeResponse.set(entity);
        //查询id下的所有子节点(SELECT * FROM City c WHERE c.parent_id=?)
        List<CityEntity> entities = cityService.findByParentId(id);
        List<CityResponse> treeResponses = new ArrayList<CityResponse>();
        CityResponse secondTreeResponse = null;
        for (CityEntity cityEntity : entities) {
            secondTreeResponse = new CityResponse();
            treeResponses.add(secondTreeResponse.set(CityEntity));
        }
        CityResponse cityTreeResponse = null;
        for (CityResponse child : treeResponses) {
             cityTreeResponse = getTreeModule(child.getId());//开始递归
            firstTreeResponse.getChildren().add(cityTreeResponse);
        }
        return firstTreeResponse;
    }

3.输出(省略)