自定义层级查询,自定义参数,还可以将id和parentId的Long类型换成String类型,这样可用自定义的编码来实现树形;
要查询的数据示例(只留了部分区域层级作为演示)下面放有此数据的sql语句
基础版查询
结果
{
"code": 0,
"data": [
{
"id": 1,
"parentId": 0,
"name": "四川省",
"children": [
{
"id": 2,
"parentId": 1,
"name": "成都市",
"children": [
{
"id": 3,
"parentId": 2,
"name": "锦江区"
},
{
"id": 4,
"parentId": 2,
"name": "青羊区"
},
{
"id": 5,
"parentId": 2,
"name": "金牛区"
},
{
"id": 6,
"parentId": 2,
"name": "武侯区"
},
{
"id": 7,
"parentId": 2,
"name": "成华区"
},
{
"id": 8,
"parentId": 2,
"name": "龙泉驿区"
}
]
},
{
"id": 23,
"parentId": 1,
"name": "自贡市",
"children": [
{
"id": 24,
"parentId": 23,
"name": "自流井区"
},
{
"id": 25,
"parentId": 23,
"name": "贡井区"
},
{
"id": 26,
"parentId": 23,
"name": "大安区"
},
{
"id": 27,
"parentId": 23,
"name": "沿滩区"
},
{
"id": 28,
"parentId": 23,
"name": "荣县"
},
{
"id": 29,
"parentId": 23,
"name": "富顺县"
}
]
},
{
"id": 30,
"parentId": 1,
"name": "攀枝花市",
"children": [
{
"id": 31,
"parentId": 30,
"name": "东区"
},
{
"id": 32,
"parentId": 30,
"name": "西区"
},
{
"id": 33,
"parentId": 30,
"name": "仁和区"
},
{
"id": 34,
"parentId": 30,
"name": "米易县"
},
{
"id": 35,
"parentId": 30,
"name": "盐边县"
}
]
},
{
"id": 36,
"parentId": 1,
"name": "泸州市",
"children": [
{
"id": 37,
"parentId": 36,
"name": "江阳区"
},
{
"id": 38,
"parentId": 36,
"name": "纳溪区"
},
{
"id": 39,
"parentId": 36,
"name": "龙马潭区"
},
{
"id": 40,
"parentId": 36,
"name": "泸县"
}
]
}
]
}
],
"msg": ""
}
自定义版的层级、参数查询>>
结果>>
{
"code": 0,
"data": [
{
"id": 30,
"parentId": 1,
"name": "攀枝花市",
"children": [
{
"id": 31,
"parentId": 30,
"name": "东区",
"status": "1",
"code": "56694ddb42a3bc4dd88fc936f47572368db5dbde",
"type": "区县级"
},
{
"id": 32,
"parentId": 30,
"name": "西区",
"status": "1",
"code": "061080b8f721aa338de128f6bc4f44e5aa2b4fec",
"type": "区县级"
},
{
"id": 33,
"parentId": 30,
"name": "仁和区",
"status": "1",
"code": "57725c6d66342d17e5a911163d9a2be4f531bcb8",
"type": "区县级"
},
{
"id": 34,
"parentId": 30,
"name": "米易县",
"status": "1",
"code": "fc9ee41186fb2eedf95660c6056db0b57f797e0c",
"type": "区县级"
},
{
"id": 35,
"parentId": 30,
"name": "盐边县",
"status": "1",
"code": "2ad3f46d6d89b4f4859432cf1dd2ea12e84738a9",
"type": "区县级"
}
],
"status": "1",
"code": "c6fab5a156c731c058bab5a414ab5b14afa0af58",
"type": "市州级"
}
],
"msg": ""
}
工具类
/**
* @Author: tan
* @Date: 2023/05/30 16:25
* @Description: 通用树形结构工具类:一条SQL语句、两次循环搞定
*/
public class TreeUtils {
/**
* 固定的根id
*/
private static final Long ROOT = 0L;
/**
* 树形结构入口
* 方法所用的参数为id和parentId,只要满足这两个条件就可以构建树结构了
*/
public static <T> List<T> getTree(List<T> dataList, Long id) {
// 把数据装进MAP中
HashMap<Long, T> hashMap = new HashMap<>(16);
dataList.forEach(d -> hashMap.put(getId(d), d));
// 返回对象:无限极
List<T> list = new ArrayList<>();
// 循环
dataList.forEach(d -> {
// 获取当前对象的父id
Long parentId = getParentId(d);
if (parentId != null) {
// 判断是否是一级父类
if (parentId.equals(id)) {
list.add(d);
} else {
// 如果不是顶级父类,那么此id就是此对象的父id,通过id查询出此对象的父类
T parent = hashMap.get(parentId);
// 把当前对象存入父类对象中
if (parent != null) {
addChild(parent, d);
}
}
}
});
//指定层级处理,判断不是根
if (!id.equals(ROOT)) {
//指定根
T root = hashMap.get(id);
if (root != null) {
((TreeVO) root).setChildren(list);
List<T> rootList = new ArrayList<>();
rootList.add(root);
return rootList;
}
}
return list;
}
private static Long getId(Object obj) {
// 返回对象的id属性值
return ((TreeVO) obj).getId();
}
private static Long getParentId(Object obj) {
// 返回对象的parentId属性值
return ((TreeVO) obj).getParentId();
}
private static void addChild(Object parent, Object child) {
// 将子对象添加到父对象的children列表中
((TreeVO) parent).getChildren().add(child);
}
}
通用的树形结构对象
/**
* @Author: tan
* @Date: 2023/3/10 10:25
* @Description: 通用的树型结构对象
*/
@Data
public class TreeVO<T> {
@ApiModelProperty(value = "id")
private Long id;
@ApiModelProperty(value = "父级id")
private Long parentId;
@ApiModelProperty(value = "名称")
private String name;
@ApiModelProperty(value = "子级")
@JsonInclude(value = JsonInclude.Include.NON_EMPTY)
private List<T> children = new ArrayList<>();
}
自定义树形对象:示例
/**
* @Author: tan
* @Date: 2023/3/10 10:25
* @Description: 自定义树形对象
*/
@Data
@EqualsAndHashCode(callSuper = true)
public class TreeAreaVO extends TreeVO<TreeAreaVO> {
@ApiModelProperty(value = "状态 1选中 0无")
private String status;
@ApiModelProperty(value = "区域code")
private String code;
@ApiModelProperty(value = "区域类型")
private String type;
}
查询数据
DROP TABLE IF EXISTS `t_area`;
CREATE TABLE `t_area` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '行政区名称',
`code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '唯一编码',
`type` tinyint(1) NULL DEFAULT NULL COMMENT '类型 1省 2市 3区县',
`parent_id` bigint NULL DEFAULT NULL COMMENT '父id',
`admin_code` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '行政区划码',
`remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 234 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of t_area
-- ----------------------------
INSERT INTO `t_area` VALUES (1, '四川省', '38ba1071cfe250106c0ddd427e17b3a02073dda3', 1, 0, '510000', NULL);
INSERT INTO `t_area` VALUES (2, '成都市', 'aee4dc7f1f8071f258ce9779837327418b4654bb', 2, 1, '510100', NULL);
INSERT INTO `t_area` VALUES (3, '锦江区', 'ad31ac416216cc2e0a94d2f71bfbdc38c9ac7e21', 3, 2, '510104', NULL);
INSERT INTO `t_area` VALUES (4, '青羊区', '231ec7127da2abe3ad3056fa4059d2aa6ef3398f', 3, 2, '510105', NULL);
INSERT INTO `t_area` VALUES (5, '金牛区', '314c4accaed3509fa483f42a1de01d4d57ed9cab', 3, 2, '510106', NULL);
INSERT INTO `t_area` VALUES (6, '武侯区', 'b9a57b4b9b9daf2aaa2f721dd4ecd968c8c01d35', 3, 2, '510107', NULL);
INSERT INTO `t_area` VALUES (7, '成华区', 'b74ace80c8cb21a39f9b676e20af2c8cb6cad843', 3, 2, '510108', NULL);
INSERT INTO `t_area` VALUES (8, '龙泉驿区', '944289c9660203609d5b9b68845da26fb4564f37', 3, 2, '510112', NULL);
INSERT INTO `t_area` VALUES (23, '自贡市', '86330a12b8a4b0bff1fc073c960b9de23ecaa452', 2, 1, '510300', NULL);
INSERT INTO `t_area` VALUES (24, '自流井区', 'a4b1bdde43860206bfdb9cd6523d92c4b11d4871', 3, 23, '510302', NULL);
INSERT INTO `t_area` VALUES (25, '贡井区', 'f047de0ea40373dff1ec5c5a936fbac606555249', 3, 23, '510303', NULL);
INSERT INTO `t_area` VALUES (26, '大安区', '348ef5122b5c33093debf0f42a1bcff8f0595aa7', 3, 23, '510304', NULL);
INSERT INTO `t_area` VALUES (27, '沿滩区', '5d40c7ced2172a6faf5b000a65889bfa8d3e7d02', 3, 23, '510311', NULL);
INSERT INTO `t_area` VALUES (28, '荣县', 'e1beab3e9bf58c2aaf891e9ebc0672f4f4ddc888', 3, 23, '510321', NULL);
INSERT INTO `t_area` VALUES (29, '富顺县', '10d96e08649c5b5b0bdcc3957891b81b9f6a2eb0', 3, 23, '510322', NULL);
INSERT INTO `t_area` VALUES (30, '攀枝花市', 'c6fab5a156c731c058bab5a414ab5b14afa0af58', 2, 1, '510400', NULL);
INSERT INTO `t_area` VALUES (31, '东区', '56694ddb42a3bc4dd88fc936f47572368db5dbde', 3, 30, '510402', NULL);
INSERT INTO `t_area` VALUES (32, '西区', '061080b8f721aa338de128f6bc4f44e5aa2b4fec', 3, 30, '510403', NULL);
INSERT INTO `t_area` VALUES (33, '仁和区', '57725c6d66342d17e5a911163d9a2be4f531bcb8', 3, 30, '510411', NULL);
INSERT INTO `t_area` VALUES (34, '米易县', 'fc9ee41186fb2eedf95660c6056db0b57f797e0c', 3, 30, '510421', NULL);
INSERT INTO `t_area` VALUES (35, '盐边县', '2ad3f46d6d89b4f4859432cf1dd2ea12e84738a9', 3, 30, '510422', NULL);
INSERT INTO `t_area` VALUES (36, '泸州市', 'cbdb2cdf577e2a0b8b175119aab43db44d0ff7af', 2, 1, '510500', NULL);
INSERT INTO `t_area` VALUES (37, '江阳区', '86786e16566d2602fd0fee684078a3fc260c00c0', 3, 36, '510502', NULL);
INSERT INTO `t_area` VALUES (38, '纳溪区', '843d7fec1ef9113c5fa9e7d229fdcaf8f7eedb8a', 3, 36, '510503', NULL);
INSERT INTO `t_area` VALUES (39, '龙马潭区', '0df01e131670c89d1c902d9ca0c114436cf95c44', 3, 36, '510504', NULL);
INSERT INTO `t_area` VALUES (40, '泸县', 'fc48aea4b65857b7100959e8c800c7df23314dbf', 3, 36, '510521', NULL);