• Java一次返回中国所有省市区三级树形级联+前端vue展示【200ms内】


    一、前言

    中国省市区还是不少的,省有34个,市有391个,区有1101个,这是以小编的库里的,可能不是最新的,但是个数也差不了多少。

    当一次返回所有的数据,并且还要组装成一个三级树,一般的for,会循环34*391*1101次。这样就是千万级的,加上与数据库交互,你跑半天也跑不完。

    最后的处理是组长提供的思路,果然很快。

    二、思路

    首先:小编的省市区是三张表

    第一:我们通过三次IO从数据库中查询出所有省市区的数据,下面在进行for循环组装树形效率就很快了!

    第二:为了减少IO交互,我们把刚刚取出来的市和区构建为map>map>,这样效率就上来了!

    三、数据库表设计

    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;
    
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    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;
    
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    数据量太大了,就不给大家了!

    四、具体实现

    省市区的实体类就不展示了,大家根据自己公司的设计稍微改动即可使用!

    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;
    }
    
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35

    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);
    
    }
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52

    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(() => {
    
      });
    },
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    六、总结

    如果大家有比小编更加快的方式,欢迎留言交流哈!

    如果觉得有用,还请动动大家的发财小手点点关注哈!!谢谢大家了!!


    有缘人才可以看得到的哦!!!

    点击访问!小编自己的网站,里面也是有很多好的文章哦!

  • 相关阅读:
    磨金石教育摄影技能干货分享|上海随手拍——叶落满街,秋意未尽
    云原生平台,让边缘应用玩出花!
    MyBatis框架——Mybatis操作数据库之简单的insert操作的实现
    麻了,别再为难软件测试员了
    撕开AEB的「遮羞布」
    ATTransUNet:一种增强型混合Transformer结构用于超声图像分割
    代碼隨想錄算法訓練營|第三十九天|738.单调递增的数字、968.监控二叉树、第八章 贪心算法總結。刷题心得(c++)
    淘宝退货退款测试用例
    Nginx 限流模块:限制高并发和IP访问频率
    复盘:手推SVM支持向量机二分类超平面,求解目标函数原问题,kkt条件,对偶问题求q,核函数、hinge loss损失函数,硬间隔,软间隔,最后得到w和b
  • 原文地址:https://blog.csdn.net/qq_52423918/article/details/126764458