Java一次返回中國所有省市區三級樹形級聯+前端vue展示【200ms內】
2022-10-13 12:08:13
一、前言
中國省市區還是不少的，省有34個，市有391個，區有1101個，這是以小編的庫裡的，可能不是最新的，但是個數也差不了多少。
當一次返回所有的資料，並且還要組裝成一個三級樹，一般的for，會迴圈34*391*1101次。這樣就是千萬級的，加上與資料庫互動，你跑半天也跑不完。
最後的處理是組長提供的思路，果然很快。
二、思路
首先：小編的省市區是三張表
第一：我們通過三次IO從資料庫中查詢出所有省市區的資料，下面在進行for迴圈組裝樹形效率就很快了！
第二：為了減少IO互動，我們把剛剛取出來的市和區構建為map<provinceCode,List<City>>，map<districtCode,List<District>>，這樣效率就上來了！
三、資料庫表設計
1. 省
CREATE TABLE `address_province` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`short_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`new_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`is_latest` tinyint(3) NULL DEFAULT 1 COMMENT '是否是最新地址，0否1是',
`remarks` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`created_at` datetime(0) NULL DEFAULT NULL COMMENT '建立時間',
`created_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`updated_at` datetime(0) NULL DEFAULT NULL COMMENT '更新時間',
`updated_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`is_deleted` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '刪除標記，0：正常；1：刪除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `uk_code`(`code`) USING BTREE,
INDEX `idx_address_province_name`(`name`) USING BTREE,
INDEX `idx_address_province_short_name`(`short_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址省' ROW_FORMAT = Dynamic;
2. 市：
CREATE TABLE `address_city` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`new_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`province_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`is_latest` tinyint(3) NULL DEFAULT 1 COMMENT '是否是最新地址，0否1是',
`remarks` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`created_at` datetime(0) NULL DEFAULT NULL COMMENT '建立時間',
`created_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`updated_at` datetime(0) NULL DEFAULT NULL COMMENT '更新時間',
`updated_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`is_deleted` tinyint(3) NULL DEFAULT 0 COMMENT '刪除標記，0正常1刪除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `uk_code`(`code`) USING BTREE,
INDEX `idx_address_city_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 392 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址市' ROW_FORMAT = Dynamic;
3. 區
CREATE TABLE `address_district` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`new_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`city_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`province_code` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`is_latest` tinyint(3) NULL DEFAULT 1 COMMENT '是否是最新地址，0否1是',
`remarks` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`created_at` datetime(0) NULL DEFAULT NULL COMMENT '建立時間',
`created_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`updated_at` datetime(0) NULL DEFAULT NULL COMMENT '更新時間',
`updated_by` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`is_deleted` tinyint(3) NULL DEFAULT 0 COMMENT '刪除標記，0正常1刪除',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `uk_code`(`code`) USING BTREE,
INDEX `idx_address_district_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3110 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地址縣/區' ROW_FORMAT = Dynamic;
資料量太大了，就不給大家了！
四、具體實現
省市區的實體類就不展示了，大家根據自己公司的設計稍微改動即可使用！
1. 樹形VO
/**
* 地址業務父子
*
* @author wangzhenjun
* @date 2022/9/2 16:26
*/
@Data
public class AddressVO implements Serializable {
/**
* ID
*/
@ApiModelProperty(value = "ID")
private Integer id;
/**
* 編碼
*/
@ApiModelProperty(value = "")
private String code;
/**
* 名稱
*/
@ApiModelProperty(value = "")
private String name;
/**
* 父編碼
*/
private String parentCode;
/**
* 市區
*/
private List<AddressVO> children;
}
2. 具體實現
@Override
public Result address() {
long l = System.currentTimeMillis();
// 獲取省市區
List<AddressProvince> provinceList = addressProvinceMapper.selectList(Wrappers.<AddressProvince>lambdaQuery().eq(AddressProvince::getIsDeleted, 0).eq(AddressProvince::getIsLatest, 1));
List<AddressCity> cityList = addressCityMapper.selectList(Wrappers.<AddressCity>lambdaQuery().eq(AddressCity::getIsDeleted, 0).eq(AddressCity::getIsLatest, 1));
List<AddressDistrict> districtList = addressDistrictMapper.selectList(Wrappers.<AddressDistrict>lambdaQuery().eq(AddressDistrict::getIsDeleted, 0).eq(AddressDistrict::getIsLatest, 1));
// 按照省code進行分組
Map<String, List<AddressCity>> cityMap = cityList.stream().collect(Collectors.groupingBy(AddressCity::getProvinceCode));
// 按照市code進行分組
Map<String, List<AddressDistrict>> districtMap = districtList.stream().collect(Collectors.groupingBy(AddressDistrict::getCityCode));
List<AddressVO> result = new ArrayList<>();
for (AddressProvince province : provinceList) {
// 獲取某個省下的所有市
List<AddressCity> addressCityList = cityMap.get(province.getCode());
// 給樹形物件賦省的資料
AddressVO addressProvinceVO = new AddressVO();
addressProvinceVO.setId(province.getId());
addressProvinceVO.setCode(province.getCode());
addressProvinceVO.setName(province.getName());
List<AddressVO> cityResult = new ArrayList<>();
for (AddressCity addressCity : addressCityList) {
// 獲取某個市下的所有區
List<AddressDistrict> addressDistrictList = districtMap.get(addressCity.getCode());
// 給樹形物件賦市的資料
AddressVO addressCityVO = new AddressVO();
addressCityVO.setId(addressCity.getId());
addressCityVO.setCode(addressCity.getCode());
addressCityVO.setName(addressCity.getName());
addressCityVO.setParentCode(province.getCode());
List<AddressVO> districtResult = new ArrayList<>();
// 便利每個市下面的所有區
for (AddressDistrict addressDistrict : addressDistrictList) {
// 給樹形物件賦區的資料
AddressVO addressDistrictVO = new AddressVO();
addressDistrictVO.setId(addressDistrict.getId());
addressDistrictVO.setCode(addressDistrict.getCode());
addressDistrictVO.setName(addressDistrict.getName());
addressDistrictVO.setParentCode(addressCity.getCode());
districtResult.add(addressDistrictVO);
}
addressCityVO.setChildren(districtResult);
cityResult.add(addressCityVO);
}
addressProvinceVO.setChildren(cityResult);
result.add(addressProvinceVO);
}
System.out.println(System.currentTimeMillis() - l);
return Result.success(result);
}
4. 計算時長
我們可以看到，一共297毫秒，小編的機器比較卡，支援cpu在60%情況下，200ms應該問題不大，可以放到redis快取起來，這樣減少IO互動，減少資料庫的壓力！！
五、前端vue
此時address為陣列，按序放了選擇的value，
props:可以指定顯示和選擇的名稱，預設是value和label
<el-cascader
v-model="addReceiverForm.address"
placeholder="請選擇收貨人地址"
:options="options"
:props="{checkStrictly: true,value:'code',label:'name'}"></el-cascader>
========data：============
addReceiverForm:{
address:[]
}
options: [],
========method：============
// 獲取省市區
initAddress() {
listAddressAndChild().then(data => {
this.options = data.data
}).catch(() => {
});
},
六、總結
如果大家有比小編更加快的方式，歡迎留言交流哈！
如果覺得有用，還請動動大家的發財小手點點關注哈！！謝謝大家了！！
有緣人才可以看得到的哦！！！ 歡迎大家關注小編的微信公眾號，謝謝大家！
