获取行政区划数据-2023年省、市、县、乡镇、村五级数据

最新推荐文章于 2025-03-23 15:51:35 发布

Grain322

最新推荐文章于 2025-03-23 15:51:35 发布

阅读量948

点赞数 3

分类专栏：工具文章标签：数据库数据分析政务

本文链接：https://blog.youkuaiyun.com/guyua/article/details/139172557

版权

工具专栏收录该内容

11 篇文章

订阅专栏

1、胡咧咧一下

2、数据库脚本

3、实体类Region.java

4、RegionController.java

5、IRegionService.java和RegionServiceImpl.java

6、RegionMapper.java和RegionMapper.xml

1、胡咧咧一下

获取国家行政区划，另外数据库表留有经纬度坐标字段，奈何找不到资源完善，有资源的朋友希望分享一下。废话不说了。

sql文件下载地址

https://download.youkuaiyun.com/download/guyua/87429040

工具类

<!-- HTML解析器,抓取统计局数据 -->
<dependency>
    <groupId>org.jsoup</groupId>
    <artifactId>jsoup</artifactId>
</dependency>

数据如图：

编辑

代码如下：

2、数据库脚本

CREATE TABLE `aha_region` (
  `code` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区划编号',
  `parent_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '父区划编号',
  `ancestors` varchar(28) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '祖区划编号列表',
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区划名称',
  `province_code` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省级区划编号',
  `province_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省级名称',
  `city_code` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地市州市一级区划编号',
  `city_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '市一级名称',
  `county_code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '县级区划编码',
  `county_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '县级名称',
  `town_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '乡镇区划编码',
  `town_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '乡镇名称',
  `village_code` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '村一级区划编码',
  `village_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '村一级名称',
  `level` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '行政级别',
  `type` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城乡分类代码',
  `longitude` decimal(12,6) DEFAULT NULL COMMENT '经度',
  `dimension` decimal(12,6) DEFAULT NULL COMMENT '维度',
  `sort` int(3) DEFAULT NULL COMMENT '排序',
  `remark` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
  `version` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '版本，使用最新版本年份加001'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

3、实体类Region.java

package com.toto.region.bean;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;

/**
 * Created with IntelliJ IDEA.
 *
 * @Author: TOTO-AGU
 * @Date: 2023/02/08/09:14
 * @Description:
 */
@Data
public class Region implements Serializable {

    private static final long serialVersionUID = -5617131662664380659L;

    /**
     * 区划编号
     */
    @TableId(value = "code", type = IdType.INPUT)
    @ApiModelProperty(value = "区划编号")
    private String code;
    /**
     * 父区划编号
     */
    @ApiModelProperty(value = "父区划编号")
    private String parentCode;
    /**
     * 祖区划编号
     */
    @ApiModelProperty(value = "祖区划编号列表")
    private String ancestors;
    /**
     * 区划名称
     */
    @ApiModelProperty(value = "区划名称")
    private String name;
    /**
     * 省级区划编号
     */
    @ApiModelProperty(value = "省级区划编号")
    private String provinceCode;
    /**
     * 省级名称
     */
    @ApiModelProperty(value = "省级名称")
    private String provinceName;
    /**
     * 市级区划编号
     */
    @ApiModelProperty(value = "地市州市一级区划编号")
    private String cityCode;
    /**
     * 市级名称
     */
    @ApiModelProperty(value = "市一级名称")
    private String cityName;
    /**
     * 区级区划编号
     */
    @ApiModelProperty(value = "县级区划编码")
    private String countyCode;
    /**
     * 区级名称
     */
    @ApiModelProperty(value = "县级名称")
    private String countyName;
    /**
     * 乡镇区划编码
     */
    @ApiModelProperty(value = "乡镇区划编码")
    private String townCode;
    /**
     * 镇级名称
     */
    @ApiModelProperty(value = "乡镇名称")
    private String townName;
    /**
     * 村一级区划编码
     */
    @ApiModelProperty(value = "村一级区划编码")
    private String villageCode;
    /**
     * 村一级名称
     */
    @ApiModelProperty(value = "村一级名称")
    private String villageName;
    /**
     * 层级
     */
    @ApiModelProperty(value = "层级")
    private String level;
    /**
     * 城乡分类代码
     */
    @ApiModelProperty(value = "城乡分类代码")
    private String type;
    /**
     * 经度
     */
    @ApiModelProperty(value = "经度")
    private BigDecimal longitude;
    /**
     * 维度
     */
    @ApiModelProperty(value = "维度")
    private BigDecimal dimension;
    /**
     * 排序
     */
    @ApiModelProperty(value = "排序")
    private Integer sort;
    /**
     * 备注
     */
    @ApiModelProperty(value = "备注")
    private String remark;
    /**
     * 版本
     */
    @ApiModelProperty(value = "版本")
    private String version;
}

4、RegionController.java

package com.toto.region.controller;

import com.alibaba.fastjson.JSONObject;
import com.toto.core.web.bean.AjaxResult;
import com.toto.core.web.controller.BaseController;
import com.toto.region.bean.Region;
import com.toto.region.service.IRegionService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@Api("行政区划")
@RestController
@RequestMapping("/region")
public class RegionController extends BaseController {

    @Autowired
    private IRegionService regionService;

    @PostMapping("/save")
    @ApiOperation("保存对象")
    public AjaxResult add(@RequestBody Region info) {
        return toAjax(regionService.addRegion(info));
    }

    @PutMapping("/edit")
    @ApiOperation("修改对象")
    public AjaxResult edit(@RequestBody Region info) {
        return toAjax(regionService.updateRegion(info));
    }

    @DeleteMapping("/delete/{id}")
    @ApiOperation("删除对象")
    public AjaxResult delete(@PathVariable("code") Long code) {
        return toAjax(regionService.deleteRegionById(code));
    }

    @GetMapping("/info/{id}")
    @ApiOperation("查看对象信息")
    @ApiImplicitParams({
            @ApiImplicitParam(name="id",value="主键",required = true)
    })
    public AjaxResult info(@PathVariable("code") Long code) {
        Region info = regionService.getRegionById(code);
        return AjaxResult.success(info);
    }

    @GetMapping("/list")
    @ApiOperation("查看列表")
    public AjaxResult list() {
        List<Region> list = regionService.getAllRegions();
        return AjaxResult.success(this.getDataTable(list));
    }

    @GetMapping("/getRegionsFromGov")
    @ApiOperation("爬取行政区划")
    public AjaxResult getRegionsFromGov() {
        JSONObject json = regionService.getRegionsFromGov();
        return AjaxResult.success(json);
    }

}

5、IRegionService.java和RegionServiceImpl.java

package com.toto.region.service;

import com.alibaba.fastjson.JSONObject;
import com.toto.region.bean.Region;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Author: TOTO-AGU
 * @Date: 2023/02/08/09:22
 * @Description:
 */
public interface IRegionService {
    /**
     * 新增对象
     * @param info
     * @return
     */
    public int addRegion(Region info);
    /**
     * 更新对象
     * @param info
     * @return
     */
    public int updateRegion(Region info);
    /**
     * 删除对象
     * @param id
     * @return
     */
    public int deleteRegionById(Long id);
    /**
     * 获取对象
     * @param id
     * @return
     */
    public Region getRegionById(Long id);

    /**
     * 获取行政区划
     * @return
     */
    public List<Region> getAllRegions();

    /**
     * 从统计局抓取数据
     * @return
     */
    public JSONObject getRegionsFromGov();

}

package com.toto.region.service.impl;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.toto.region.bean.Region;
import com.toto.region.mapper.RegionMapper;
import com.toto.region.service.IRegionService;
import org.jsoup.Connection;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Author: TOTO-AGU
 * @Date: 2023/02/08/09:24
 * @Description:
 */
@Service
public class RegionServiceImpl implements IRegionService {

    private final Logger logger =  LoggerFactory.getLogger(RegionServiceImpl.class);

    private final static String URL = "https://www.stats.gov.cn/sj/tjbz/tjyqhdmhcxhfdm/2023/index.html";
    
    

    @Autowired
    private RegionMapper regionMapper;

    /**
     * 建立连接
     */
    private static Document connect(String url) {
        if (url == null || url.isEmpty()) {
            throw new IllegalArgumentException("无效的url");
        }
        try {
            String path = url+"?timestamp="+ System.currentTimeMillis();
            Connection connect = Jsoup.connect(path);
            connect.response().cookies();
            connect.cookies(connect.response().cookies());
            connect.header("User-Agent", "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:49.0) Gecko/20100101 Firefox/49.0")
                    .header("Connection", "close")//如果是这种方式，这里务必带上
                    .timeout(10000);
            return connect.get();
        } catch (Exception e) {
            System.out.println(url+"请求异常");
            try {
                Thread.sleep(30000);
            } catch (InterruptedException ex) {

            }
            return connect(url);
        }
    }

    /**
     * version
     */
    private static String version() {
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
        String version = format.format(new Date());
        return version;
    }

    @Override
    public int addRegion(Region info) {
        return 0;
    }

    @Override
    public int updateRegion(Region info) {
        return 0;
    }

    @Override
    public int deleteRegionById(Long id) {
        return 0;
    }

    @Override
    public Region getRegionById(Long id) {
        return null;
    }

    @Override
    public List<Region> getAllRegions() {
        return null;
    }

    @Override
    public JSONObject getRegionsFromGov() {
        JSONObject json = new JSONObject();
        json.put("region",this.getProvinces(0, URL));
        return json;
    }

    /**
     * 获取所有的省份(省级)
     * @param url 请求地址：http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2022/index.html
     * @return
     */
    private Boolean getProvinces(int count,String url) {
        List<Region> listAreas = new ArrayList<>();
        Document connect = connect(url);
        if(ObjectUtil.isEmpty(connect)||"请开启JavaScript并刷新该页.".equals(connect.select("strong").text())||"Please enable JavaScript and refresh the page.".equals(connect.select("strong").text())){
            try {
                Thread.sleep(60000);
            } catch (InterruptedException e) {

            }
            if(ObjectUtil.isNotNull(connect)){
                System.err.println(connect.toString());
            }
            System.err.println(url+"重试第"+(++count)+"次");
            getProvinces(count,url);
        }
        Elements rowProvince = connect.select("tr.provincetr");
        int i = 1;
        for (Element provinceElement : rowProvince) {
            Elements select = provinceElement.select("a");
            for (Element province : select) {
                String code = province.select("a").attr("href");
                String name = province.text();
                //if("江苏省".equals(name)){
                //if(!"北京市天津市河北省山西省内蒙古自治区辽宁省吉林省黑龙江省上海市江苏省浙江省安徽省福建省江西省山东省河南省".contains(name)){
                    Region region = new Region();
                    region.setCode(code.replace(".html",""));
                    region.setName(name);
                    region.setParentCode("00");
                    region.setAncestors("00");
                    region.setProvinceCode(code.replace(".html",""));
                    region.setProvinceName(name);
                    region.setLevel("1");
                    region.setVersion(version());
                    region.setSort(i++);
                    listAreas.add(region);

                    String provinceUrl = url.replace("index.html",code);
                    // System.err.println("++++++++++++++++++++++++++开始获取"+ name +"下属市区行政区划信息++++++++++++++++++++++++");
                    List<Region> cityAreaCodeList = getCityAreaCode(0,provinceUrl,region.getAncestors()+ "," +region.getCode(),region.getProvinceCode(),region.getProvinceName());
                    listAreas.addAll(cityAreaCodeList);

                    // 省级循环完毕后，插入数据库，并清空listAreas
                    // logger.info(JSON.toJSONString(listAreas));
                    System.err.println("*********************************开始插入【"+ name +"】行政区划*********************************");
                    this.regionMapper.batchInsertRegions(listAreas);
                    listAreas = new ArrayList<>();
                    System.err.println("*********************************结束插入【"+ name +"】行政区划*********************************");
                //}
            }
        }
        return true;
    }

    /**
     * 获取市一级行政区划代码(市级)
     * @param provinceUrl
     * @param provinceCode
     * @param provinceName
     * @param ancestors
     * @return
     */
    private List<Region> getCityAreaCode(int count,String provinceUrl, String ancestors,
                                         String provinceCode, String provinceName) {
        List<Region> listAreas = new ArrayList<>();
        Document connect = connect(provinceUrl);

        if(ObjectUtil.isEmpty(connect)||"请开启JavaScript并刷新该页.".equals(connect.select("strong").text())||"Please enable JavaScript and refresh the page.".equals(connect.select("strong").text())){
            try {
                Thread.sleep(30000);
            } catch (InterruptedException e) {

            }
            if(ObjectUtil.isNotNull(connect)){
                System.err.println(connect.toString());
            }
            System.err.println(provinceUrl+"重试第"+(++count)+"次");
            getCityAreaCode(count,provinceUrl,ancestors,provinceCode,provinceName);
        }

        Elements rowCity = connect.select("tr.citytr");
        int i = 1;
        for (Element cityElement : rowCity) {
            String name = cityElement.select("td").text();
            String[] split = name.split(" ");
            Region region = new Region();
            region.setCode(split[0].substring(0,4));
            region.setName(split[1]);
            region.setParentCode(provinceCode);
            region.setAncestors(ancestors);
            region.setProvinceCode(provinceCode);
            region.setProvinceName(provinceName);
            region.setCityCode(split[0].substring(0,4));
            region.setCityName(split[1]);
            region.setLevel("2");
            region.setVersion(version());
            region.setSort(i++);

            listAreas.add(region);
            String cityUrl = provinceUrl.replace(".html","/"+split[0].substring(0, 4)+".html");
            System.err.println("-------------------开始获取"+split[1]+"下属区县行政区划信息-----------------------");
            // System.out.println("市级url："+cityUrl);
            List<Region> cityAreaCodeList = getCountyAreaCode(0,cityUrl,
                    region.getAncestors()+ "," +region.getCode(),
                    region.getProvinceCode(),
                    region.getProvinceName(),
                    region.getCityCode(),
                    region.getCityName());
            listAreas.addAll(cityAreaCodeList);
        }
        return listAreas;
    }

    /**
     * 获取区县一级行政区划代码(区县级)
     * @param cityUrl
     * @param ancestors
     * @param provinceCode
     * @param provinceName
     * @param cityCode
     * @param cityName
     * @return
     */
    private List<Region> getCountyAreaCode(int count,String cityUrl, String ancestors,
                                           String provinceCode, String provinceName,
                                           String cityCode, String cityName){
        List<Region> listAreas = new ArrayList<>();
        Document connect = connect(cityUrl);
        if(ObjectUtil.isEmpty(connect)||"请开启JavaScript并刷新该页.".equals(connect.select("strong").text())||"Please enable JavaScript and refresh the page.".equals(connect.select("strong").text())){
            try {
                Thread.sleep(30000);
            } catch (InterruptedException e) {

            }
            if(ObjectUtil.isNotNull(connect)){
                System.err.println(connect.toString());
            }
            System.err.println(cityUrl+"重试第"+(++count)+"次");
            getCountyAreaCode(count,cityUrl,ancestors,provinceCode,provinceName,cityCode,cityName);
        }
        Elements rowCounty = connect.select("tr.countytr");
        int i = 1;
        for (Element countyElement : rowCounty) {
            String codeHtml = countyElement.select("a").attr("href");
            String name = countyElement.select("td").text();
            String[] split = name.split(" ");
            if(!"市辖区".equals(split[1])){
                Region region = new Region();
                region.setCode(split[0].substring(0,6));
                region.setName(split[1]);
                region.setParentCode(cityCode);
                region.setAncestors(ancestors);
                region.setProvinceCode(provinceCode);
                region.setProvinceName(provinceName);
                region.setCityCode(cityCode);
                region.setCityName(cityName);
                region.setCountyCode(split[0].substring(0,6));
                region.setCountyName(split[1]);
                region.setLevel("3");
                region.setVersion(version());
                region.setSort(i++);

                listAreas.add(region);
                String countyUrl = cityUrl.replace(region.getCityCode()+".html",codeHtml);
                if(!"金门县".equals(region.getCountyName())){
                    System.err.println("====================开始获取"+split[1]+"下属区划信息");
                    // System.out.println("区县级url："+cityUrl);
                    List<Region> countryAreaList = getDownAreaCode(0,countyUrl,
                            region.getAncestors()+ "," +region.getCode(),
                            region.getProvinceCode(),
                            region.getProvinceName(),
                            region.getCityCode(),
                            region.getCityName(),
                            region.getCountyCode(),
                            region.getCountyName());
                    listAreas.addAll(countryAreaList);
                }
            }
        }
        return listAreas;
    }

    /**
     * 获取乡镇街道数据（乡镇一级）
     * @param countyUrl
     * @param ancestors
     * @param provinceCode
     * @param provinceName
     * @param cityCode
     * @param cityName
     * @param countryCode
     * @param countryName
     * @return
     */
    private List<Region> getDownAreaCode(int count,String countyUrl, String ancestors,
                                         String provinceCode, String provinceName,
                                         String cityCode, String cityName,
                                         String countryCode, String countryName){
        List<Region> listAreas = new ArrayList<>();
        Document connect = connect(countyUrl);
        if(ObjectUtil.isEmpty(connect)||"请开启JavaScript并刷新该页.".equals(connect.select("strong").text())||"Please enable JavaScript and refresh the page.".equals(connect.select("strong").text())){
            try {
                Thread.sleep(30000);
            } catch (InterruptedException e) {

            }
            if(ObjectUtil.isNotNull(connect)){
                System.err.println(connect.toString());
            }
            System.err.println(countyUrl+"重试第"+(++count)+"次");
            getDownAreaCode(count,countyUrl,ancestors,provinceCode,provinceName,cityCode,cityName,countryCode,countryName);
        }
        Elements rowDown = connect.select("tr.towntr");
        int i = 1;
        for (Element downElement : rowDown) {
            String codeHtml = downElement.select("a").attr("href");
            String name = downElement.select("td").text();
            String[] split = name.split(" ");
            Region region = new Region();
            region.setCode(split[0].substring(0,9));
            region.setName(split[1]);
            region.setParentCode(countryCode);
            region.setAncestors(ancestors);
            region.setProvinceCode(provinceCode);
            region.setProvinceName(provinceName);
            region.setCityCode(cityCode);
            region.setCityName(cityName);
            region.setCountyCode(countryCode);
            region.setCountyName(countryName);
            region.setTownCode(split[0].substring(0,9));
            region.setTownName(split[1]);
            region.setLevel("4");
            region.setVersion(version());
            region.setSort(i++);

            listAreas.add(region);
            String downyUrl = countyUrl.replace(region.getCountyCode()+".html",codeHtml);
            System.err.println("====================开始获取"+split[1]+"下属区划信息");
            //System.out.println("乡镇街道级URL："+downyUrl);
            List<Region> villageAreaCodeList = getVillageAreaCode(0,downyUrl,
                    region.getAncestors()+ "," +region.getCode(),
                    region.getProvinceCode(),
                    region.getProvinceName(),
                    region.getCityCode(),
                    region.getCityName(),
                    region.getCountyCode(),
                    region.getCountyName(),
                    region.getTownCode(),
                    region.getTownName());
            listAreas.addAll(villageAreaCodeList);
        }
        return listAreas;
    }

    private List<Region> getVillageAreaCode(int count,String downyUrl, String ancestors,
                                         String provinceCode, String provinceName,
                                         String cityCode, String cityName,
                                         String countryCode, String countryName,
                                         String townCode,String townName){
        List<Region> listAreas = new ArrayList<>();
        Document connect = connect(downyUrl);
        if(ObjectUtil.isEmpty(connect)||"请开启JavaScript并刷新该页.".equals(connect.select("strong").text())||"Please enable JavaScript and refresh the page.".equals(connect.select("strong").text())){
            try {
                Thread.sleep(30000);
            } catch (InterruptedException e) {

            }
            if(ObjectUtil.isNotNull(connect)){
                System.err.println(connect.toString());
            }
            System.err.println(downyUrl+"重试第"+(++count)+"次");
            getVillageAreaCode(count,downyUrl,ancestors,provinceCode,provinceName,cityCode,cityName,countryCode,countryName,townCode,townName);
        }
        Elements rowDown = connect.select("tr.villagetr");
        int i = 1;
        for (Element downElement : rowDown) {
            String name = downElement.select("td").text();
            String[] split = name.split(" ");

            Region region = new Region();
            region.setCode(split[0]);
            region.setName(split[2]);
            region.setParentCode(townCode);
            region.setAncestors(ancestors);
            region.setProvinceCode(provinceCode);
            region.setProvinceName(provinceName);
            region.setCityCode(cityCode);
            region.setCityName(cityName);
            region.setCountyCode(countryCode);
            region.setCountyName(countryName);
            region.setTownCode(townCode);
            region.setTownName(townName);
            region.setVillageCode(split[0]);
            region.setVillageName(split[2]);
            region.setType(split[1]);
            region.setLevel("5");
            region.setVersion(version());
            region.setSort(i++);

            listAreas.add(region);
        }
        return listAreas;
    }

}

6、RegionMapper.java和RegionMapper.xml

package com.toto.region.mapper;

import com.toto.region.bean.Region;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Author: TOTO-AGU
 * @Date: 2023/02/08/09:28
 * @Description:
 */
@Mapper
public interface RegionMapper {
    /**
     * 新增对象
     * @param info
     * @return
     */
    public int addRegion(Region info);
    /**
     * 更新对象
     * @param info
     * @return
     */
    public int updateRegion(Region info);
    /**
     * 删除对象
     * @param code
     * @return
     */
    public int deleteRegionByCode(Long code);
    /**
     * 获取对象
     * @param code
     * @return
     */
    public Region getRegionById(Long code);

    /**
     * 获取行政区划
     * @return
     */
    public List<Region> getAllRegions();

    /**
     * 批量插入行政区划
     * @param listAreas
     * @return
     */
    public boolean batchInsertRegions(List<Region> listAreas);
}

<?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.toto.region.mapper.RegionMapper">

    <resultMap id="BaseResultMap" type="com.toto.region.bean.Region">
        <id column="code" property="code"/>
        <result column="parent_code" property="parentCode"/>
        <result column="ancestors" property="ancestors"/>
        <result column="name" property="name"/>
        <result column="province_code" property="provinceCode"/>
        <result column="province_name" property="provinceName"/>
        <result column="city_code" property="cityCode"/>
        <result column="city_name" property="cityName"/>
        <result column="county_code" property="countyCode"/>
        <result column="county_name" property="countyName"/>
        <result column="town_code" property="townCode"/>
        <result column="town_name" property="townName"/>
        <result column="village_code" property="villageCode"/>
        <result column="village_name" property="villageName"/>
        <result column="level" property="level"/>
        <result column="sort" property="sort"/>
        <result column="remark" property="remark"/>
        <result column="type" property="type"/>
        <result column="longitude" property="longitude"/>
        <result column="dimension" property="dimension"/>
        <result column="version" property="version"/>
    </resultMap>

    <!-- 全部字段字段 -->
    <sql id="Base_Column_All">
        code,parent_code,ancestors,name,province_code,province_name,city_code,city_name,county_code,county_name,town_code,town_name,village_code,village_name,level,sort,remark,type,longitude,dimension,version
    </sql>

    <!-- 全部字段字段 -->
    <sql id="Value_Column_All">
        #{code},
        #{parent_code},
        #{ancestors},
        #{name},
        #{province_code},
        #{province_name},
        #{city_code},
        #{city_name},
        #{county_code},
        #{county_name},
        #{town_code},
        #{town_name},
        #{village_code},
        #{village_name},
        #{level},
        #{sort},
        #{remark},
        #{type},
        #{longitude},
        #{dimension},
        #{version}
    </sql>

    <!-- where字段 -->
    <sql id="Where_Column_All">
        <where>
            <if test="parentCode!=null and parentCode!=''">
                and parent_code = #{parentCode},
            </if>
        </where>
    </sql>

    <!-- 全部字段字段 -->
    <sql id="Update_Column_All">
        <trim prefix="SET" suffixOverrides=",">
            <if test="parentCode!=null and parentCode!=''">
                parent_code = #{parentCode},
            </if>
            <if test="ancestors!=null and ancestors!=''">
                ancestors = #{ancestors},
            </if>
            <if test="name!=null and name!=''">
                name = #{name},
            </if>
            <if test="provinceCode!=null and provinceCode!=''">
                province_code = #{provinceCode},
            </if>
            <if test="provinceName!=null and provinceName!=''">
                province_name = #{provinceName},
            </if>
            <if test="cityCode!=null and cityCode!=''">
                city_code = #{cityCode},
            </if>
            <if test="cityName!=null and cityName!=''">
                city_name = #{cityName},
            </if>
            <if test="countyCode!=null and countyCode!=''">
                county_code = #{countyCode},
            </if>
            <if test="countyName!=null and countyName!=''">
                county_name = #{countyName},
            </if>
            <if test="townCode!=null and townCode!=''">
                town_code = #{townCode},
            </if>
            <if test="townName!=null and townName!=''">
                town_name = #{townName},
            </if>
            <if test="villageCode!=null and villageCode!=''">
                village_code = #{villageCode},
            </if>
            <if test="villageName!=null and villageName!=''">
                village_name = #{villageName},
            </if>
            <if test="level!=null and level!=''">
                level = #{level},
            </if>
            <if test="sort!=null">
                sort = #{sort},
            </if>
            <if test="remark!=null and remark!=''">
                remark = #{remark},
            </if>
            <if test="type!=null and type!=''">
                type = #{type},
            </if>
            <if test="longitude!=null">
                longitude = #{longitude},
            </if>
            <if test="dimension!=null">
                dimension = #{dimension},
            </if>
            <if test="dimension!=null and dimension!=''">
                version = #{version},
            </if>
        </trim>
    </sql>

    <!-- 新增对象 -->
    <insert id="addRegion">
        INSERT INTO aha_region(<include refid="Base_Column_All"/>) VALUES (<include refid="Value_Column_All"/>)
    </insert>
    <!-- 批量插入行政区划 -->
    <insert id="batchInsertRegions" parameterType="java.util.List">
        insert into aha_region(<include refid="Base_Column_All"/>) VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.code},
            #{item.parentCode},
            #{item.ancestors},
            #{item.name},
            #{item.provinceCode},
            #{item.provinceName},
            #{item.cityCode},
            #{item.cityName},
            #{item.countyCode},
            #{item.countyName},
            #{item.townCode},
            #{item.townName},
            #{item.villageCode},
            #{item.villageName},
            #{item.level},
            #{item.sort},
            #{item.remark},
            #{item.type},
            #{item.longitude},
            #{item.dimension},
            #{item.version})
        </foreach>
    </insert>
    <!-- 删除对象 -->
    <delete id="deleteRegionByCode">
        DELETE FROM aha_region WHERE code=#{code}
    </delete>
    <!-- 更新对象 -->
    <update id="updateRegion">
        UPDATE aha_region <include refid="Update_Column_All"/> WHERE code=#{code}
    </update>
    <!-- 获取对象 -->
    <select id="getRegionById" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_All"/> FROM aha_region WHERE code=#{code}
    </select>
    <!-- 获取对象列表 -->
    <select id="getAllRegions" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_All"/> FROM aha_region
    </select>
</mapper>