项目中我们可能经常有这样的需求,需要返回二级或三级的菜单,返回一个树形结构,面试中也可能经常被问到。
最近的项目中就用到了,这里整理分享一下。
应用场景:
地区树,国家,省,市,区县共4级。
表结构:
建表语句:
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `base_area`
-- ----------------------------
DROP TABLE IF EXISTS `base_area`;
CREATE TABLE `base_area` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`AREA_NAME` varchar(255) DEFAULT NULL COMMENT '地区名称',
`AREA_CODE` varchar(255) DEFAULT NULL COMMENT '地区编码',
`PARENT_ID` bigint(20) DEFAULT NULL,
`PLAT_MARK` bigint(20) DEFAULT NULL COMMENT '区域标识,也就是平台标识',
`LEVEL` tinyint(4) DEFAULT '1' COMMENT '层',
`STATUS` tinyint(4) DEFAULT '1' COMMENT '是否可用、是否显示',
`EXPAND` tinyint(4) DEFAULT '0' COMMENT '是否展开子节点,非0为展开。',
PRIMARY KEY (`ID`),
KEY `index2` (`PLAT_MARK`)
) ENGINE=InnoDB AUTO_INCREMENT=3514 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS = 1;
返回JSON结果示例:
数据比较多,这里只展示一部分,有需要完整表数据的小伙伴,稍后提供下载地址。
{"result":[{"createTimeString":"","updateTimeString":"","level":3,"platMark":100001001000000,"parentId":1,"areaCode":"110000","expand":0,"areaName":"北京市","id":2,"childrenList":[{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001001000,"parentId":2,"areaCode":"110101","expand":0,"areaName":"东城区","id":4,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001002000,"parentId":2,"areaCode":"110102","expand":0,"areaName":"西城区","id":5,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001003000,"parentId":2,"areaCode":"110105","expand":0,"areaName":"朝阳区","id":6,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001004000,"parentId":2,"areaCode":"110106","expand":0,"areaName":"丰台区","id":7,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001005000,"parentId":2,"areaCode":"110107","expand":0,"areaName":"石景山区","id":8,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001006000,"parentId":2,"areaCode":"110108","expand":0,"areaName":"海淀区","id":9,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001007000,"parentId":2,"areaCode":"110109","expand":0,"areaName":"门头沟区","id":10,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001008000,"parentId":2,"areaCode":"110111","expand":0,"areaName":"房山区","id":11,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001009000,"parentId":2,"areaCode":"110112","expand":0,"areaName":"通州区","id":12,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001010000,"parentId":2,"areaCode":"110113","expand":0,"areaName":"顺义区","id":13,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001011000,"parentId":2,"areaCode":"110114","expand":0,"areaName":"昌平区","id":14,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001012000,"parentId":2,"areaCode":"110115","expand":0,"areaName":"大兴区","id":15,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001013000,"parentId":2,"areaCode":"110116","expand":0,"areaName":"怀柔区","id":16,"childrenList":[]},{"createTimeString":"","updateTimeString":"","level":4,"platMark":100001001014000,"p