JAVA——关于省市区三级树状返回

一、数据模型的构建语句

CREATE TABLE `obase_region` (
  `reg_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `reg_code` int(20) unsigned NOT NULL,
  `reg_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '名称',
  `p_id` int(11) DEFAULT '0' COMMENT '上级id',
  `p_code` int(20) DEFAULT NULL,
  `reg_type` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '地区类型 RGT001：省，RGT002：市，RGT003：区',
  `lastchanged` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `p_reg_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '上级名称',
  PRIMARY KEY (`reg_id`) USING BTREE,
  KEY `reg_code` (`reg_code`) USING BTREE COMMENT '行政划区编码',
  KEY `reg_type` (`reg_type`) USING BTREE COMMENT '层级类型',
  KEY `p_code` (`p_code`) USING BTREE COMMENT '上级行政划区编码'
) ENGINE=InnoDB AUTO_INCREMENT=43678 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='区域表';

二、数据源的导入语句，仅供参考

INSERT INTO `obase_region` VALUES (4, 130000, '河北省', 1, 100000, 'RGT001', '2022-04-26 15:20:38', '中华人民共和国');
INSERT INTO `obase_region` VALUES (39, 130100, '石家庄市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (40, 130200, '唐山市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (41, 130300, '秦皇岛市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (42, 130400, '邯郸市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (43, 130500, '邢台市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (44, 130600, '保定市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (45, 130700, '张家口市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (46, 130800, '承德市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (47, 130900, '沧州市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (48, 131000, '廊坊市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (49, 131100, '衡水市', 4, 130000, 'RGT002', '2022-04-26 15:20:38', '河北省');
INSERT INTO `obase_region` VALUES (460, 130102, '长安区', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (461, 130104, '桥西区', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (462, 130105, '新华区', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (463, 130107, '井陉矿区', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (464, 130108, '裕华区', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (465, 130109, '藁城区', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (466, 130110, '鹿泉区', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (467, 130111, '栾城区', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (468, 130121, '井陉县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (469, 130123, '正定县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (470, 130125, '行唐县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (471, 130126, '灵寿县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (472, 130127, '高邑县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (473, 130128, '深泽县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (474, 130129, '赞皇县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (475, 130130, '无极县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (476, 130131, '平山县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (477, 130132, '元氏县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (478, 130133, '赵县', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (479, 130181, '辛集市', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (480, 130183, '晋州市', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (481, 130184, '新乐市', 39, 130100, 'RGT003', '2022-04-26 15:20:38', '石家庄市');
INSERT INTO `obase_region` VALUES (482, 130202, '路南区', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (483, 130203, '路北区', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (484, 130204, '古冶区', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (485, 130205, '开平区', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (486, 130207, '丰南区', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (487, 130208, '丰润区', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (488, 130209, '曹妃甸区', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (489, 130224, '滦南县', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (490, 130225, '乐亭县', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (491, 130227, '迁西县', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (492, 130229, '玉田县', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (493, 130281, '遵化市', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (494, 130283, '迁安市', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (495, 130284, '滦州市', 40, 130200, 'RGT003', '2022-04-26 15:20:38', '唐山市');
INSERT INTO `obase_region` VALUES (496, 130302, '海港区', 41, 130300, 'RGT003', '2022-04-26 15:20:38', '秦皇岛市');
INSERT INTO `obase_region` VALUES (497, 130303, '山海关区', 41, 130300, 'RGT003', '2022-04-26 15:20:38', '秦皇岛市');
INSERT INTO `obase_region` VALUES (498, 130304, '北戴河区', 41, 130300, 'RGT003', '2022-04-26 15:20:38', '秦皇岛市');
INSERT INTO `obase_region` VALUES (499, 130306, '抚宁区', 41, 130300, 'RGT003', '2022-04-26 15:20:38', '秦皇岛市');
INSERT INTO `obase_region` VALUES (500, 130321, '青龙满族自治县', 41, 130300, 'RGT003', '2022-04-26 15:20:38', '秦皇岛市');
INSERT INTO `obase_region` VALUES (501, 130322, '昌黎县', 41, 130300, 'RGT003', '2022-04-26 15:20:38', '秦皇岛市');
INSERT INTO `obase_region` VALUES (502, 130324, '卢龙县', 41, 130300, 'RGT003', '2022-04-26 15:20:38', '秦皇岛市');
INSERT INTO `obase_region` VALUES (503, 130402, '邯山区', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (504, 130403, '丛台区', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (505, 130404, '复兴区', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (506, 130406, '峰峰矿区', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (507, 130407, '肥乡区', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (508, 130408, '永年区', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (509, 130423, '临漳县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (510, 130424, '成安县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (511, 130425, '大名县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (512, 130426, '涉县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (513, 130427, '磁县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (514, 130430, '邱县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (515, 130431, '鸡泽县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (516, 130432, '广平县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (517, 130433, '馆陶县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (518, 130434, '魏县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (519, 130435, '曲周县', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (520, 130481, '武安市', 42, 130400, 'RGT003', '2022-04-26 15:20:38', '邯郸市');
INSERT INTO `obase_region` VALUES (521, 130502, '襄都区', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (522, 130503, '信都区', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (523, 130505, '任泽区', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (524, 130506, '南和区', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (525, 130522, '临城县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (526, 130523, '内丘县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (527, 130524, '柏乡县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (528, 130525, '隆尧县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (529, 130528, '宁晋县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (530, 130529, '巨鹿县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (531, 130530, '新河县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (532, 130531, '广宗县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (533, 130532, '平乡县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (534, 130533, '威县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (535, 130534, '清河县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (536, 130535, '临西县', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (537, 130581, '南宫市', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (538, 130582, '沙河市', 43, 130500, 'RGT003', '2022-04-26 15:20:38', '邢台市');
INSERT INTO `obase_region` VALUES (539, 130602, '竞秀区', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (540, 130606, '莲池区', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (541, 130607, '满城区', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (542, 130608, '清苑区', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (543, 130609, '徐水区', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (544, 130623, '涞水县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (545, 130624, '阜平县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (546, 130626, '定兴县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (547, 130627, '唐县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (548, 130628, '高阳县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (549, 130629, '容城县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (550, 130630, '涞源县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (551, 130631, '望都县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (552, 130632, '安新县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (553, 130633, '易县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (554, 130634, '曲阳县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (555, 130635, '蠡县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (556, 130636, '顺平县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (557, 130637, '博野县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (558, 130638, '雄县', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (559, 130681, '涿州市', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (560, 130682, '定州市', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (561, 130683, '安国市', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (562, 130684, '高碑店市', 44, 130600, 'RGT003', '2022-04-26 15:20:38', '保定市');
INSERT INTO `obase_region` VALUES (563, 130702, '桥东区', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (564, 130703, '桥西区', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (565, 130705, '宣化区', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (566, 130706, '下花园区', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (567, 130708, '万全区', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (568, 130709, '崇礼区', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (569, 130722, '张北县', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (570, 130723, '康保县', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (571, 130724, '沽源县', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (572, 130725, '尚义县', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (573, 130726, '蔚县', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (574, 130727, '阳原县', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (575, 130728, '怀安县', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (576, 130730, '怀来县', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (577, 130731, '涿鹿县', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (578, 130732, '赤城县', 45, 130700, 'RGT003', '2022-04-26 15:20:38', '张家口市');
INSERT INTO `obase_region` VALUES (579, 130802, '双桥区', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (580, 130803, '双滦区', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (581, 130804, '鹰手营子矿区', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (582, 130821, '承德县', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (583, 130822, '兴隆县', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (584, 130824, '滦平县', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (585, 130825, '隆化县', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (586, 130826, '丰宁满族自治县', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (587, 130827, '宽城满族自治县', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (588, 130828, '围场满族蒙古族自治县', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (589, 130881, '平泉市', 46, 130800, 'RGT003', '2022-04-26 15:20:38', '承德市');
INSERT INTO `obase_region` VALUES (590, 130902, '新华区', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (591, 130903, '运河区', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (592, 130921, '沧县', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (593, 130922, '青县', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (594, 130923, '东光县', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (595, 130924, '海兴县', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (596, 130925, '盐山县', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (597, 130926, '肃宁县', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (598, 130927, '南皮县', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (599, 130928, '吴桥县', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (600, 130929, '献县', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (601, 130930, '孟村回族自治县', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (602, 130981, '泊头市', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (603, 130982, '任丘市', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (604, 130983, '黄骅市', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (605, 130984, '河间市', 47, 130900, 'RGT003', '2022-04-26 15:20:38', '沧州市');
INSERT INTO `obase_region` VALUES (606, 131002, '安次区', 48, 131000, 'RGT003', '2022-04-26 15:20:38', '廊坊市');
INSERT INTO `obase_region` VALUES (607, 131003, '广阳区', 48, 131000, 'RGT003', '2022-04-26 15:20:38', '廊坊市');
INSERT INTO `obase_region` VALUES (608, 131022, '固安县', 48, 131000, 'RGT003', '2022-04-26 15:20:38', '廊坊市');
INSERT INTO `obase_region` VALUES (609, 131023, '永清县', 48, 131000, 'RGT003', '2022-04-26 15:20:38', '廊坊市');
INSERT INTO `obase_region` VALUES (610, 131024, '香河县', 48, 131000, 'RGT003', '2022-04-26 15:20:38', '廊坊市');
INSERT INTO `obase_region` VALUES (611, 131025, '大城县', 48, 131000, 'RGT003', '2022-04-26 15:20:38', '廊坊市');
INSERT INTO `obase_region` VALUES (612, 131026, '文安县', 48, 131000, 'RGT003', '2022-04-26 15:20:38', '廊坊市');
INSERT INTO `obase_region` VALUES (613, 131028, '大厂回族自治县', 48, 131000, 'RGT003', '2022-04-26 15:20:38', '廊坊市');
INSERT INTO `obase_region` VALUES (614, 131081, '霸州市', 48, 131000, 'RGT003', '2022-04-26 15:20:38', '廊坊市');
INSERT INTO `obase_region` VALUES (615, 131082, '三河市', 48, 131000, 'RGT003', '2022-04-26 15:20:38', '廊坊市');
INSERT INTO `obase_region` VALUES (616, 131102, '桃城区', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');
INSERT INTO `obase_region` VALUES (617, 131103, '冀州区', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');
INSERT INTO `obase_region` VALUES (618, 131121, '枣强县', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');
INSERT INTO `obase_region` VALUES (619, 131122, '武邑县', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');
INSERT INTO `obase_region` VALUES (620, 131123, '武强县', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');
INSERT INTO `obase_region` VALUES (621, 131124, '饶阳县', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');
INSERT INTO `obase_region` VALUES (622, 131125, '安平县', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');
INSERT INTO `obase_region` VALUES (623, 131126, '故城县', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');
INSERT INTO `obase_region` VALUES (624, 131127, '景县', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');
INSERT INTO `obase_region` VALUES (625, 131128, '阜城县', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');
INSERT INTO `obase_region` VALUES (626, 131182, '深州市', 49, 131100, 'RGT003', '2022-04-26 15:20:38', '衡水市');

三，实体类与Vo，Vo返回序列化处理

@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonSerialize(using = RegionVoSerializer.class)
public class RegionVo {

    private Integer provinceId;//省Id
    private String provinceName;//省名称
    private Integer cityId;//城市Id
    private String cityName;//城市名称
    private Integer districtId;//区Id
    private String districtName;//区名称
    private Integer pId; // 父级 ID
    private String regType; // 区域类型
    private String pRegName; // 上级名称
    private List<RegionVo> children; // 子节点
}
import com.alibaba.acm.shaded.org.codehaus.jackson.JsonGenerator;
import com.alibaba.acm.shaded.org.codehaus.jackson.map.JsonSerializer;
import com.alibaba.acm.shaded.org.codehaus.jackson.map.SerializerProvider;

import java.io.IOException;

public class RegionVoSerializer extends JsonSerializer<RegionVo> {


    @Override
    public void serialize(RegionVo region, JsonGenerator gen, SerializerProvider serializers) throws IOException {
        gen.writeStartObject();

        // 根据 regType 来决定序列化哪些字段
        switch (region.getRegType()) {
            case "RGT001": // 省级
                gen.writeNumberField("provinceId", region.getProvinceId());
                gen.writeStringField("provinceName", region.getProvinceName());
                break;
            case "RGT002": // 市级
                gen.writeNumberField("cityId", region.getCityId());
                gen.writeStringField("cityName", region.getCityName());
                break;
            case "RGT003": // 区级
                gen.writeNumberField("districtId", region.getDistrictId());
                gen.writeStringField("districtName", region.getDistrictName());
                break;
            default:
                break;
        }

        // 写入 regType 字段
        gen.writeStringField("regType", region.getRegType());

        // 序列化子节点
        if (region.getChildren() != null && !region.getChildren().isEmpty()) {
            gen.writeFieldName("children");
            gen.writeObject(region.getChildren());
        }

        gen.writeEndObject();
    }
}

四、select查询

<?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.xxxxx.domain.mapper.member.ObaseRegionMapper">
    

    <select id="getAllRegions" resultType="com.xxxxx.controller.vo.RegionVo">
        SELECT
            CASE
                WHEN reg_type = 'RGT001' THEN reg_id  -- 如果是省，则映射为 provinceId
                END AS provinceId,
            CASE
                WHEN reg_type = 'RGT001' THEN reg_name  -- 如果是省，则映射为 provinceName
                END AS provinceName,
            CASE
                WHEN reg_type = 'RGT002' THEN reg_id  -- 如果是市，则映射为 cityId
                END AS cityId,
            CASE
                WHEN reg_type = 'RGT002' THEN reg_name  -- 如果是市，则映射为 cityName
                END AS cityName,
            CASE
                WHEN reg_type = 'RGT003' THEN reg_id  -- 如果是区，则映射为 districtId
                END AS districtId,
            CASE
                WHEN reg_type = 'RGT003' THEN reg_name  -- 如果是区，则映射为 districtName
                END AS districtName,
            p_id AS pId,  -- 父级 ID
            reg_type AS regType,
            p_reg_name AS pRegName  -- 父级名称
        FROM
            obase_region
        WHERE
            reg_type != 'RGT004' and reg_type != 'RGT000'
        ORDER BY
            reg_type, p_id;
    </select>
</mapper>

五，对应的业务逻辑，考虑到频繁访问数据库，做本地缓存

@Service
public class xxxxServiceImpl implements xxxxxxService {

private final Map<String, List<RegionVo>> regionCache = new ConcurrentHashMap<>();

    private static final String CACHE_KEY = "ALL_REGIONS";

    public  xxxxServiceImpl (ObaseRegionMapper regionMapper) {
        this.regionMapper = regionMapper;
    }public ResponseMsg getAllCity() {
        // 尝试从缓存中获取数据
        List<RegionVo> regionList = regionCache.get(CACHE_KEY);

        // 如果缓存为空，则从数据库查询
        if (regionList == null) {
            synchronized (this) {
                // 再次检查缓存，以防其他线程已经加载数据
                regionList = regionCache.get(CACHE_KEY);
                if (regionList == null) {
                    regionList = regionMapper.getAllRegions();
                    if (regionList != null && !regionList.isEmpty()) {
                        // 将数据放入缓存
                        regionCache.put(CACHE_KEY, regionList);
                    }
                }
            }
        }

        // 按 p_id 分组，将相同父 ID 的 RegionVo 对象分在一起
        Map<Integer, List<RegionVo>> regionMap = regionList.stream()
                .collect(Collectors.groupingBy(RegionVo::getPId));

        // 获取所有的省作为根节点（p_id 为 1 的节点）
        List<RegionVo> rootList = regionMap.get(1);
        if (rootList == null || rootList.isEmpty()) {
            return ResponseMsg.buildFailResponse(-1, "数据为空");
        }

        // 构建树形结构，并处理字段映射
        for (RegionVo root : rootList) {
            mapFields(root);
            buildTree(root, regionMap);
        }

        // 返回构建好的树形结构
        return ResponseMsg.buildResponse(rootList);
    }

    private void buildTree(RegionVo parent, Map<Integer, List<RegionVo>> regionMap) {
        // 根据当前节点类型选择对应的ID字段来获取子节点列表
        List<RegionVo> children = null;
        switch (parent.getRegType()) {
            case "RGT001": // 省
                children = regionMap.get(parent.getProvinceId());
                break;
            case "RGT002": // 市
                children = regionMap.get(parent.getCityId());
                break;
            case "RGT003": // 区
                children = regionMap.get(parent.getDistrictId());
                break;
        }

        if (children != null) {
            for (RegionVo child : children) {
                // 映射字段
                mapFields(child);
                // 递归构建子树
                buildTree(child, regionMap);
            }
            // 将子节点设置到父节点的 children 中
            parent.setChildren(children);
        }
    }


    private void mapFields(RegionVo region) {
        switch (region.getRegType()) {
            case "RGT001": // 省
                region.setProvinceId(region.getProvinceId()); // 这里应该映射为从数据库中获得的字段
                region.setProvinceName(region.getProvinceName());
                break;
            case "RGT002": // 市
                region.setCityId(region.getCityId()); // 这里应该映射为从数据库中获得的字段
                region.setCityName(region.getCityName());
                break;
            case "RGT003": // 区
                region.setDistrictId(region.getDistrictId()); // 这里应该映射为从数据库中获得的字段
                region.setDistrictName(region.getDistrictName());
                break;

        }
    }

六、手动清除缓存，例如在数据更新后调用

   // 手动清除缓存，例如在数据更新后调用
    public void clearCache() {
        regionCache.remove(CACHE_KEY);
    }

七、运行结果

{
            "provinceId": 4,
            "provinceName": "河北省",
            "regType": "RGT001",
            "children": [
                {
                    "cityId": 39,
                    "cityName": "石家庄市",
                    "regType": "RGT002",
                    "children": [
                        {
                            "districtId": 460,
                            "districtName": "长安区",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 461,
                            "districtName": "桥西区",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 462,
                            "districtName": "新华区",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 463,
                            "districtName": "井陉矿区",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 464,
                            "districtName": "裕华区",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 465,
                            "districtName": "藁城区",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 466,
                            "districtName": "鹿泉区",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 467,
                            "districtName": "栾城区",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 468,
                            "districtName": "井陉县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 469,
                            "districtName": "正定县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 470,
                            "districtName": "行唐县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 471,
                            "districtName": "灵寿县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 472,
                            "districtName": "高邑县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 473,
                            "districtName": "深泽县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 474,
                            "districtName": "赞皇县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 475,
                            "districtName": "无极县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 476,
                            "districtName": "平山县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 477,
                            "districtName": "元氏县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 478,
                            "districtName": "赵县",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 479,
                            "districtName": "辛集市",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 480,
                            "districtName": "晋州市",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        },
                        {
                            "districtId": 481,
                            "districtName": "新乐市",
                            "regType": "RGT003",
                            "pid": 39,
                            "pregName": "石家庄市"
                        }
                    ],
                    "pid": 4,
                    "pregName": "河北省"
                }................

 

八、总结

关键点：

  1. 线程安全：使用 ConcurrentHashMap 确保缓存的线程安全性，使用 synchronized 块确保缓存的加载是原子的。
  2. 缓存策略：在数据更新或其他必要的情况下，可以手动清除缓存，确保数据的及时性。

其他考虑：

  • 缓存的持久化：如果需要在应用程序重启后保留缓存，可以考虑使用文件或数据库来持久化缓存数据。
  • 高级缓存框架：如果需要更复杂的缓存策略，可以基于时间的缓存失效、LRU（Least Recently Used）等，考虑使用 Guava Cache、Caffeine、Ehcache 等框架。

 

posted @ 2024-08-21 15:17  我的博客我的人生  阅读(144)  评论(0)  编辑  收藏  举报