最近遇到一个需求、数据库中存储父、子节点作为数据节点、使用递归算法结合数据库解析成java树形结构网上找了好几种方式、经笔者总结后整理了Demo 也为了加深自己印象、同时也给看到的你一些帮助、
数据库表结构:
create table TB_TREE
(
CID NUMBER not null,
CNAME VARCHAR2(50),
PID NUMBER //父节点
)
数据语句:
insert into tb_tree (CID, CNAME, PID) values (1, '中国', 0);
insert into tb_tree (CID, CNAME, PID) values (2, '北京市', 1);
insert into tb_tree (CID, CNAME, PID) values (3, '广东省', 1);
insert into tb_tree (CID, CNAME, PID) values (4, '上海市', 1);
insert into tb_tree (CID, CNAME, PID) values (5, '广州市', 3);
insert into tb_tree (CID, CNAME, PID) values (6, '深圳市', 3);
insert into tb_tree (CID, CNAME, PID) values (7, '海珠区', 5);
insert into tb_tree (CID, CNAME, PID) values (8, '天河区', 5);
insert into tb_tree (CID, CNAME, PID) values (9, '福田区', 6);
insert into tb_tree (CID, CNAME, PID) values (10, '南山区', 6);
insert into tb_tree (CID, CNAME, PID) values (11, '密云县', 2);
insert into tb_tree (CID, CNAME, PID) values (12, '浦东', 4);
如图:
数据实体TreeNode:
public class TreeNode {
private Integer cid;
private String cname;
private Integer pid;
private List<TreeNode> nodes = new ArrayList();
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public List getNodes() {
return nodes;
}
public void setNodes(List nodes) {
this.nodes = nodes;
}
@Override
public String toString() {
return "TreeNode{" +
"cid=" + cid +
", cname='" + cname + '\'' +
", pid=" + pid +
", nodes=" + nodes +
'}';
}
}
接口CityProvienceService:
package com.miaoshaproj.seckill.service;
import com.miaoshaproj.seckill.service.model.TreeNode;
import java.util.List;
public interface CityProvienceService {
public TreeNode getMasterNode(Integer cid);
public List<TreeNode> getSlaveNodes(Integer cid);
}
实现类CityProvienceServiceImpl :
@Service
public class CityProvienceServiceImpl implements CityProvienceService {
@Autowired
private CityProvienceDaoExt cityProvienceDaoExt;
@Override
public TreeNode getMasterNode(Integer cid) {
return cityProvienceDaoExt.getMasterNode(cid);
}
@Override
public List<TreeNode> getSlaveNodes(Integer cid) {
return cityProvienceDaoExt.getSlaveNodes(cid);
}
}
mapping dao层方法 CityProvienceDaoExt :
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.miaoshaproj.seckill.dao.CityProvienceDaoExt">
<!--根据cid获取节点对象(SELECT * FROM tb_tree t WHERE t.cid=?)-->
<select id="getMasterNode" resultType="com.miaoshaproj.seckill.service.model.TreeNode">
SELECT * FROM tb_tree t WHERE t.cid=#{cid,jdbcType=INTEGER}
</select>
<!--查询cid下的所有子节点pid(SELECT * FROM tb_tree t WHERE t.pid=?)-->
<select id="getSlaveNodes" resultType="com.miaoshaproj.seckill.service.model.TreeNode">
SELECT * FROM tb_tree t WHERE t.pid=#{cid,jdbcType=INTEGER}
</select>
</mapper>
控制器CityProvienceController :
@ResponseBody
@Controller
@RequestMapping("/city")
public class CityProvienceController {
@Autowired
private CityProvienceService cityProvienceService;
@RequestMapping("/getcity")
@ResponseBody
public TreeNode recursiveTreeData(@RequestParam(name = "cid") int cid) {
//根据cid获取节点对象(SELECT * FROM tb_tree t WHERE t.cid=?)
TreeNode masterTreeNode = cityProvienceService.getMasterNode(cid);
//查询cid下的所有子节点(SELECT * FROM tb_tree t WHERE t.pid=?)
List<TreeNode> slaveTreeNodes = cityProvienceService.getSlaveNodes(cid);
//遍历子节点
for (TreeNode slave : slaveTreeNodes) {
TreeNode n = recursiveTreeData(slave.getCid()); //递归
masterTreeNode.getNodes().add(n);
}
return masterTreeNode;
}
}
Postman测试后返回json 实例:http://localhost:10000/city/getcity?cid=1
效果如下:
{
"cid": 1,
"cname": "中国",
"pid": 0,
"nodes": [
{
"cid": 2,
"cname": "北京市",
"pid": 1,
"nodes": [
{
"cid": 11,
"cname": "密云县",
"pid": 2,
"nodes": []
}
]
},
{
"cid": 3,
"cname": "广东省",
"pid": 1,
"nodes": [
{
"cid": 5,
"cname": "广州市",
"pid": 3,
"nodes": [
{
"cid": 7,
"cname": "海珠区",
"pid": 5,
"nodes": []
},
{
"cid": 8,
"cname": "天河区",
"pid": 5,
"nodes": []
}
]
},
{
"cid": 6,
"cname": "深圳市",
"pid": 3,
"nodes": [
{
"cid": 9,
"cname": "福田区",
"pid": 6,
"nodes": []
},
{
"cid": 10,
"cname": "南山区",
"pid": 6,
"nodes": []
}
]
}
]
},
{
"cid": 4,
"cname": "上海市",
"pid": 1,
"nodes": [
{
"cid": 12,
"cname": "浦东区",
"pid": 4,
"nodes": []
}
]
}
]
}