将数据库中的树类型表，递归形成json 格式

最新推荐文章于 2025-08-03 16:17:19 发布

原创最新推荐文章于 2025-08-03 16:17:19 发布 · 5.2k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#递归 #数据库 #java #json

JAVA 专栏收录该内容

170 篇文章

订阅专栏

首先我们已城市City为分组为例以下是表结构：

以下是创建表的SQL语句：

DROP TABLE IF EXISTS `tb_groups`;
CREATE TABLE `tb_groups` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`parent_id` bigint(20) DEFAULT NULL,
`parent_path` varchar(255) DEFAULT NULL,
`deleted` bit(1) DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

以下是模拟数据：

INSERT INTO `tb_groups` VALUES ('1', '广东省体彩中心', null, null, '\0', '2016-09-19 10:09:22', '2016-09-19 10:09:28');
INSERT INTO `tb_groups` VALUES ('2', '珠海市体彩中心', '1', '1', '\0', '2016-09-19 10:11:09', '2016-09-19 10:11:09');
INSERT INTO `tb_groups` VALUES ('3', '广州市体彩中心', '1', '1', '\0', '2016-09-19 10:20:20', '2016-09-19 10:20:20');
INSERT INTO `tb_groups` VALUES ('4', '深证市体彩中心', '1', '1', '\0', '2016-09-19 10:23:24', '2016-09-19 10:23:27');
INSERT INTO `tb_groups` VALUES ('5', '越秀区体彩中心', '3', '1,3', '\0', '2016-09-19 10:24:51', '2016-09-19 10:24:54');
INSERT INTO `tb_groups` VALUES ('6', '荔湾区体彩中心', '3', '1,3', '\0', '2016-09-19 10:25:49', '2016-09-19 10:25:49');
INSERT INTO `tb_groups` VALUES ('7', '海珠区体彩中心', '3', '1,3', '\0', '2016-09-19 10:26:28', '2016-09-19 10:26:28');
INSERT INTO `tb_groups` VALUES ('8', '天河区体彩中心', '3', '1,3', '\0', '2016-09-19 10:26:50', '2016-09-19 10:26:50');
INSERT INTO `tb_groups` VALUES ('9', '白云区体彩中心', '3', '1,3', '\0', '2016-09-19 10:27:20', '2016-09-19 10:27:20');
INSERT INTO `tb_groups` VALUES ('10', '福田区体彩中心', '4', '1,4', '\0', '2016-09-19 10:29:04', '2016-09-19 10:29:04');
INSERT INTO `tb_groups` VALUES ('11', '罗湖区体彩中心', '4', '1,4', '\0', '2016-09-19 10:29:46', '2016-09-19 10:29:46');
INSERT INTO `tb_groups` VALUES ('12', '南山区体彩中心', '4', '1,4', '\0', '2016-09-19 10:30:10', '2016-09-19 10:30:10');
INSERT INTO `tb_groups` VALUES ('13', '盐田区体彩中心', '4', '1,4', '\0', '2016-09-19 10:30:41', '2016-09-19 10:30:41');
INSERT INTO `tb_groups` VALUES ('14', '宝安区体彩中心', '4', '1,4', '\0', '2016-09-19 10:31:03', '2016-09-19 10:31:03');
INSERT INTO `tb_groups` VALUES ('15', '龙岗区体彩中心', '4', '1,4', '\0', '2016-09-19 10:32:20', '2016-09-19 10:32:20');
INSERT INTO `tb_groups` VALUES ('16', '汕头市体彩中心', '1', '1', '\0', '2016-09-19 10:33:06', '2016-09-19 10:33:06');
INSERT INTO `tb_groups` VALUES ('17', '佛山市体彩中心', '1', '1', '\0', '2016-09-19 10:33:36', '2016-09-19 10:33:36');
INSERT INTO `tb_groups` VALUES ('18', '韶关市体彩中心', '1', '1', '\0', '2016-09-19 10:33:55', '2016-09-19 10:33:55');
INSERT INTO `tb_groups` VALUES ('19', '河源市体彩中心', '1', '1', '\0', '2016-09-19 10:34:26', '2016-09-19 10:34:26');
INSERT INTO `tb_groups` VALUES ('20', '梅州市体彩中心', '1', '1', '\0', '2016-09-19 11:52:22', '2016-09-19 10:34:47');
INSERT INTO `tb_groups` VALUES ('21', '惠州市体彩中心', '1', '1', '\0', '2016-09-23 11:15:56', '2016-09-19 10:35:09');
INSERT INTO `tb_groups` VALUES ('22', '惠城区体彩中心', '21', '1,21', '\0', '2016-09-23 11:15:38', '2016-09-19 10:38:37');
INSERT INTO `tb_groups` VALUES ('23', '东莞市体彩中心', '1', '1', '\0', '2016-09-19 10:48:50', '2016-09-19 10:41:07');
INSERT INTO `tb_groups` VALUES ('24', '汕尾市体彩中心', '1', '1', '\0', '2016-09-19 11:58:12', '2016-09-19 11:58:12');
INSERT INTO `tb_groups` VALUES ('25', '惠阳区体彩中心', '21', '1,21', '\0', '2016-09-23 11:30:00', '2016-09-23 11:22:15');
INSERT INTO `tb_groups` VALUES ('26', '惠城区体彩中心子分组', '22', '1,21,22', '\0', '2016-09-23 14:10:45', '2016-09-23 11:40:03');
INSERT INTO `tb_groups` VALUES ('27', '惠城区体彩中心子分组的子分组', '26', '1,21,22,26', '\0', '2016-09-23 14:10:41', '2016-09-23 13:44:36');
INSERT INTO `tb_groups` VALUES ('28', '龙岗区体彩中心子分组', '15', '1,4,15', '\0', '2016-09-23 16:49:45', '2016-09-23 16:40:37');
INSERT INTO `tb_groups` VALUES ('29', '龙岗区体彩中心子分组222', '15', '1,4,15', '\0', '2016-09-23 16:57:17', '2016-09-23 16:51:28');

我是使用JPA进行SQL查询的，要先实现这两个方法。执行SQL语句。

下面是实体：

下面是前端用于展现的MODEL：

下面是通过两个方法获取到树：

上面的方法效用下面的方法：

以下两个方法是工具方法，在以上的两个方法中会用到：

因为我是用的Spring 所有通过注解就可以将用于展现的Model转换成JSON格式：

GroupCtlTest

示例1：

request:

groupId=1

resposne:

{"success":true,"data":[{"key":1,"value":1,"label":"广东省体彩中心","children":[{"key":2,"value":2,"label":"珠海市体彩中心","children":[]},{"key":3,"value":3,"label":"广州市体彩中心","children":[{"key":5,"value":5,"label":"越秀区体彩中心","children":[]},{"key":6,"value":6,"label":"荔湾区体彩中心","children":[]},{"key":7,"value":7,"label":"海珠区体彩中心","children":[]},{"key":8,"value":8,"label":"天河区体彩中心","children":[]},{"key":9,"value":9,"label":"白云区体彩中心","children":[]}]},{"key":4,"value":4,"label":"深证市体彩中心","children":[{"key":10,"value":10,"label":"福田区体彩中心","children":[]},{"key":11,"value":11,"label":"罗湖区体彩中心","children":[]},{"key":12,"value":12,"label":"南山区体彩中心","children":[]},{"key":13,"value":13,"label":"盐田区体彩中心","children":[]},{"key":14,"value":14,"label":"宝安区体彩中心","children":[]},{"key":15,"value":15,"label":"龙岗区体彩中心","children":[]}]},{"key":16,"value":16,"label":"汕头市体彩中心","children":[]},{"key":17,"value":17,"label":"佛山市体彩中心","children":[]},{"key":18,"value":18,"label":"韶关市体彩中心","children":[]},{"key":19,"value":19,"label":"河源市体彩中心","children":[]},{"key":20,"value":20,"label":"梅州市体彩中心","children":[]},{"key":21,"value":21,"label":"惠州市体彩中心","children":[]}]}],"message":"","status":200,"errors":null,"fieldErrors":null}

示例2：

request:

groupId=3

resposne:

{"success":true,"data":[{"key":3,"value":3,"label":"广州市体彩中心","children":[{"key":5,"value":5,"label":"越秀区体彩中心","children":[]},{"key":6,"value":6,"label":"荔湾区体彩中心","children":[]},{"key":7,"value":7,"label":"海珠区体彩中心","children":[]},{"key":8,"value":8,"label":"天河区体彩中心","children":[]},{"key":9,"value":9,"label":"白云区体彩中心","children":[]}]}],"message":"","status":200,"errors":null,"fieldErrors":null}