Java一次回傳中國所有省市區三級樹形級聯+前端vue展示【200ms內】-有解無憂

一、前言

中國省市區還是不少的，省有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(valuehttps://www.cnblogs.com/wang1221/archive/2022/10/13/= "https://www.cnblogs.com/wang1221/archive/2022/10/13/ID")
    private Integer id;
    /**
     * 編碼
     */
    @ApiModelProperty(valuehttps://www.cnblogs.com/wang1221/archive/2022/10/13/= "")
    private String code;
    /**
     * 名稱
     */
    @ApiModelProperty(valuehttps://www.cnblogs.com/wang1221/archive/2022/10/13/= "")
    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 =https://www.cnblogs.com/wang1221/archive/2022/10/13/> {
    this.options = data.data
  }).catch(() => {

  });
},

六、總結

如果大家有比小編更加快的方式，歡迎留言交流哈！

如果覺得有用，還請動動大家的發財小手點點關注哈！！謝謝大家了！！

有緣人才可以看得到的哦！！！歡迎大家關注小編的微信公眾號，謝謝大家！

點擊訪問！小編自己的網站，里面也是有很多好的文章哦！

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/514141.html

標籤：其他

上一篇：面試官：Java 多執行緒怎么做事務控制？一半人答不上來。。

下一篇：jdk8中optional的orElse和orElseGet、orElseThrow方法具有什么功能呢？