1 新增菜单表
CREATE TABLE `menu` (
`id` int(11) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT INTO `menu` VALUES ('1', '1');
INSERT INTO `menu` VALUES ('2', '2');
INSERT INTO `menu` VALUES ('3', '1.1');
INSERT INTO `menu` VALUES ('4', '2.1');
INSERT INTO `menu` VALUES ('5', '1.1.1');
INSERT INTO `menu` VALUES ('6', '1.1.2');
INSERT INTO `menu` VALUES ('7', '2.1.1');
INSERT INTO `menu` VALUES ('8', '2.1.2');
2 新增菜单组织表
parent_id 父节点
root_id 根节点
depth 距离根节点长度
is_leaf 是否叶子节点
node_id 节点
CREATE TABLE `menu_relation` (
`parent_id` int(11) DEFAULT NULL,
`root_id` int(11) DEFAULT NULL,
`depth` int(11) DEFAULT NULL,
`is_leaf` tinyint(1) DEFAULT NULL,
`node_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into menu_relation values(null,1,0,0,1);
insert into menu_relation values(1,1,1,0,3);
insert into menu_relation values(3,1,2,1,5);
insert into menu_relation values(3,1,2,1,6);
insert into menu_relation values(null,2,0,0,1);
insert into menu_relation values(2,2,1,0,4);
insert into menu_relation values(4,2,2,1,7);
insert into menu_relation values(4,2,2,1,8);
insert into menu_relation values(1,3,0,0,3);
insert into menu_relation values(3,3,1,1,5);
insert into menu_relation values(3,3,1,1,6);
insert into menu_relation values(2,4,0,0,4);
insert into menu_relation values(4,4,1,1,7);
insert into menu_relation values(4,4,1,1,8);
insert into menu_relation values(3,5,0,1,5);
insert into menu_relation values(3,6,0,1,6);
insert into menu_relation values(4,7,0,1,7);
insert into menu_relation values(4,8,0,1,8);
3 获取菜单的组织结构
1 首先获取所有菜单
select * from menu_relation;
2 List转成树形结构
public List<TreeNode> ListToTree(List<TreeNode> all){
List<TreeNode> result = new ArrayList<TreeNode>();
all.forEach(node->{
boolean flag = false;
for(TreeNode node2:all){
if(node.getParentId()!=null && node.getParentId().equals(node2.getId())){
flag = true;
if(node2.getChild()==null){
node2.setChild(new ArrayList<TreeNode>());
}
node2.getChild().add(node);
break;
}
}
if(!flag){
result.add(node);
}
});
return result;
}
public class TreeNode {
private String name;
private String id;
private String parentId;
private List<TreeNode> child;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getParentId() {
return parentId;
}
public void setParentId(String parentId) {
this.parentId = parentId;
}
public List<TreeNode> getChild() {
return child;
}
public void setChild(List<TreeNode> child) {
this.child = child;
}
}
4 获取某个节点下的所有菜单
select * from menu_relation where root_id = '';