完整版中国省市县数据 存储到数据库中

1. https://github.com/small-dream/China_Province_City // 这个网站已经把中国地区省市县的代码 转换成json 格式了。

2. 接下来我们只要把这个（2018年11月中华人民共和国县以上行政区划代码.json）json文件放到本地工程目录中然后读取就行了。

3. 我的省市县本地数据库中的字段

（1）

 1 SET FOREIGN_KEY_CHECKS=0;
 2 
 3 -- ----------------------------
 4 -- Table structure for base_province
 5 -- ----------------------------
 6 DROP TABLE IF EXISTS `base_province`;
 7 CREATE TABLE `base_province` (
 8   `prov_id` bigint(20) NOT NULL,
 9   `prov_name` varchar(40) DEFAULT NULL COMMENT '省名称',
10   `create_date` datetime DEFAULT NULL,
11   `region_code` varchar(20) DEFAULT NULL COMMENT '区域编码',
12   PRIMARY KEY (`prov_id`)
13 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='省';
省 province 表

（2）

 1 SET FOREIGN_KEY_CHECKS=0;
 2 
 3 -- ----------------------------
 4 -- Table structure for base_city
 5 -- ----------------------------
 6 DROP TABLE IF EXISTS `base_city`;
 7 CREATE TABLE `base_city` (
 8   `city_id` bigint(20) NOT NULL,
 9   `prov_id` bigint(20) DEFAULT NULL COMMENT '所属省',
10   `city_name` varchar(40) DEFAULT NULL COMMENT '城市名称',
11   `create_date` datetime DEFAULT NULL,
12   `region_code` varchar(20) DEFAULT NULL COMMENT '区域编码',
13   PRIMARY KEY (`city_id`),
14   KEY `FK_Reference_4` (`prov_id`)
15 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='市';
市 city 表

（3）

 1 SET FOREIGN_KEY_CHECKS=0;
 2 
 3 -- ----------------------------
 4 -- Table structure for base_county
 5 -- ----------------------------
 6 DROP TABLE IF EXISTS `base_county`;
 7 CREATE TABLE `base_county` (
 8   `county_id` bigint(20) NOT NULL,
 9   `city_id` bigint(20) DEFAULT NULL,
10   `county_name` varchar(40) DEFAULT NULL COMMENT '县/区名称',
11   `create_date` datetime DEFAULT NULL,
12   `region_code` varchar(20) DEFAULT NULL COMMENT '区域编码',
13   PRIMARY KEY (`county_id`),
14   KEY `FK_Reference_6` (`city_id`),
15   CONSTRAINT `FK_Reference_6` FOREIGN KEY (`city_id`) REFERENCES `base_city` (`city_id`)
16 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='县/区';
县 county 表

4. 工程 结构代码 （我使用的市springBoot）

（1）entity 层

 1 package com.text.springbootdemo.entity;
 2 
 3 import lombok.Data;
 4 
 5 import javax.persistence.ElementCollection;
 6 import javax.persistence.Entity;
 7 import javax.persistence.GeneratedValue;
 8 import javax.persistence.Id;
 9 import java.util.Date;
10 import java.util.List;
11 
12 @Entity
13 //@Proxy(lazy = false)
14 //@DynamicUpdate // 自动更新时间
15 @Data
16 public class Province {
17     @Id
18 //    @GeneratedValue(strategy = GenerationType.IDENTITY)
19     @GeneratedValue
20     private Integer prov_id;
21     private String prov_name;
22     private Date create_date;
23     private String region_code;
24 
25     @ElementCollection
26     private List<City> cityList;
27 
28     public Province(){}
29 
30     public Province(String prov_name, Date create_date, String region_code, List<City> cityList) {
31         this.prov_name = prov_name;
32         this.create_date = create_date;
33         this.region_code = region_code;
34         this.cityList = cityList;
35     }
36 
37 }
省 province entity
 1 package com.text.springbootdemo.entity;
 2 
 3 import lombok.Data;
 4 
 5 import javax.persistence.ElementCollection;
 6 import javax.persistence.Entity;
 7 import javax.persistence.GeneratedValue;
 8 import javax.persistence.Id;
 9 import java.util.Date;
10 import java.util.List;
11 
12 @Entity
13 //@Proxy(lazy = false)
14 //@DynamicUpdate // 自动更新时间
15 @Data
16 public class City {
17     @Id
18 //    @GeneratedValue(strategy = GenerationType.IDENTITY)
19     @GeneratedValue
20     private Integer city_id;
21     private Integer prov_id;
22     private String city_name;
23     private Date create_date;
24     private String region_code;
25 
26     @ElementCollection
27     private List<County> countyList;
28 
29     public City(){}
30 
31     public City(Integer prov_id, String city_name, Date create_date, String region_code, List<County> countyList) {
32         this.prov_id = prov_id;
33         this.city_name = city_name;
34         this.create_date = create_date;
35         this.region_code = region_code;
36         this.countyList = countyList;
37     }
38 }
市 city entity
 1 package com.text.springbootdemo.entity;
 2 
 3 import lombok.Data;
 4 
 5 import javax.persistence.Entity;
 6 import javax.persistence.GeneratedValue;
 7 import javax.persistence.Id;
 8 import java.util.Date;
 9 
10 @Entity
11 //@Proxy(lazy = false)
12 //@DynamicUpdate // 自动更新时间
13 @Data
14 public class County {
15     @Id
16 //    @GeneratedValue(strategy = GenerationType.IDENTITY)
17     @GeneratedValue
18     private Integer county_id;
19     private Integer city_id;
20     private String county_name;
21     private Date create_date;
22     private String region_code;
23 
24     public County(){}
25 
26     public County(Integer county_id, Integer city_id, String county_name, Date create_date, String region_code) {
27         this.county_id = county_id;
28         this.city_id = city_id;
29         this.county_name = county_name;
30         this.create_date = create_date;
31         this.region_code = region_code;
32     }
33 }
县 county entity

（2）dao 层

1 package com.text.springbootdemo.dao;
2 
3 import com.text.springbootdemo.entity.Province;
4 import org.springframework.stereotype.Repository;
5 
6 @Repository
7 public interface ProvinceDao {
8     void insertProvice(Province province);
9 }
省 provinceDao
1 package com.text.springbootdemo.dao;
2 
3 import com.text.springbootdemo.entity.City;
4 import org.springframework.stereotype.Repository;
5 
6 @Repository
7 public interface CityDao {
8     void insertCity(City city);
9 }
市 cityDao
1 package com.text.springbootdemo.dao;
2 
3 import com.text.springbootdemo.entity.County;
4 import org.springframework.stereotype.Repository;
5 
6 @Repository
7 public interface CountyDao {
8     void insertCounty(County county);
9 }
县 countyDao

（3）service 层

 1 package com.text.springbootdemo.service;
 2 
 3 import com.text.springbootdemo.dao.ProvinceDao;
 4 import com.text.springbootdemo.entity.Province;
 5 import lombok.extern.slf4j.Slf4j;
 6 import org.springframework.beans.factory.annotation.Autowired;
 7 import org.springframework.stereotype.Service;
 8 import org.springframework.transaction.annotation.Transactional;
 9 
10 @Service
11 @Transactional
12 @Slf4j
13 public class ProvinceService {
14 
15     @Autowired
16     private ProvinceDao provinceDao;
17 
18     public void insertProvice(Province province){
19         provinceDao.insertProvice(province);
20     }
21 }
省 provinceService
 1 package com.text.springbootdemo.service;
 2 
 3 import com.text.springbootdemo.dao.CityDao;
 4 import com.text.springbootdemo.entity.City;
 5 import lombok.extern.slf4j.Slf4j;
 6 import org.springframework.beans.factory.annotation.Autowired;
 7 import org.springframework.stereotype.Service;
 8 import org.springframework.transaction.annotation.Transactional;
 9 
10 @Service
11 @Transactional
12 @Slf4j
13 public class CityService {
14 
15     @Autowired
16     private CityDao cityDao;
17 
18     public  void insertCity(City city){
19         cityDao.insertCity(city);
20     }
21 }
cityService
 1 package com.text.springbootdemo.service;
 2 
 3 import com.text.springbootdemo.dao.CountyDao;
 4 import com.text.springbootdemo.entity.County;
 5 import lombok.extern.slf4j.Slf4j;
 6 import org.springframework.beans.factory.annotation.Autowired;
 7 import org.springframework.stereotype.Service;
 8 import org.springframework.transaction.annotation.Transactional;
 9 
10 @Service
11 @Transactional
12 @Slf4j
13 public class CountyService {
14 
15     @Autowired
16     private CountyDao countyDao;
17 
18     public void insertCounty(County county){
19         countyDao.insertCounty(county);
20     }
21 }
县 countyService

（4）读取 json文件工具类

 1 package com.text.springbootdemo.utils;
 2 
 3 import com.alibaba.fastjson.JSONArray;
 4 import com.alibaba.fastjson.JSONObject;
 5 import com.text.springbootdemo.entity.City;
 6 import com.text.springbootdemo.entity.County;
 7 import com.text.springbootdemo.entity.Province;
 8 import org.apache.commons.io.FileUtils;
 9 
10 import javax.servlet.http.HttpServletRequest;
11 import java.io.File;
12 import java.util.ArrayList;
13 import java.util.Date;
14 import java.util.List;
15 
16 public class Utils {
17     public static List<Province> getFile(HttpServletRequest request,String filePath){
18         JSONArray proJsonArray = null;
19         JSONArray cityJsonArray = null;
20         JSONArray countyJsonArray = null;
21 
22         JSONObject cityJson = null;
23         JSONObject countyJson = null;
24         Date date = new Date();
25 
26         List<Province> provinceList = null;
27         List<City> cityList = null;
28         List<County> countyList = null;
29 
30         String path = request.getSession().getServletContext().getRealPath("/");
31         path = path + filePath;
32         File file = new File(path);
33         try {
34             String input = FileUtils.readFileToString(file,"UTF-8");// 读取文件
35             //文件转换jsonObject格式
36             JSONObject proJson= JSONObject.parseObject(input);
37             if (proJson != null) {
38                 // 取出数据
39                 proJsonArray = proJson.getJSONArray("china");
40                 provinceList = new ArrayList<Province>();
41                 if (proJsonArray !=null ) {
42                     for (int i = 0; i < proJsonArray.size(); i++){
43                         Province province = new Province();
44                         JSONObject temp = proJsonArray.getJSONObject(i);
45                         province.setProv_name(temp.getString("name"));
46                         province.setCreate_date(date);
47                         province.setRegion_code(temp.getString("code"));
48 
49                         cityJsonArray = JSONArray.parseArray(temp.getString("cityList"));
50                         cityList = new ArrayList<City>();
51                         if (cityJsonArray != null) {
52                             for (int j = 0; j < cityJsonArray.size(); j++){
53                                 City city = new City();
54                                 cityJson = cityJsonArray.getJSONObject(j);
55                                 city.setCity_name(cityJson.getString("name"));
56                                 city.setRegion_code(cityJson.getString("code"));
57                                 city.setCreate_date(date);
58                                 countyJsonArray = JSONArray.parseArray(cityJson.getString("areaList"));
59                                 countyList = new ArrayList<County>();
60                                 if (countyJsonArray != null) {
61                                     for (int k = 0; k < countyJsonArray.size(); k++){
62                                         County county = new County();
63                                         countyJson = countyJsonArray.getJSONObject(k);
64                                         county.setCounty_name(countyJson.getString("name"));
65                                         county.setRegion_code(countyJson.getString("code"));
66                                         county.setCreate_date(date);
67                                         countyList.add(county);
68                                     }
69                                     city.setCountyList(countyList);
70                                     cityList.add(city);
71                                 }
72                             }
73                             province.setCityList(cityList);
74                             provinceList.add(province);
75                         }
76                     }
77                 }
78             }
79         }catch (Exception e){
80             e.getStackTrace();
81         }
82         return provinceList;
83     }
84 
85 // 下面的 main 方法没有用到
86     public static void main(String[] args) {
87 //        test();
88     }
89 }
读取json文件工具类

（5）controller 层

 1 @RequestMapping("/file")
 2     @ResponseBody
 3     private  String getFile(HttpServletRequest request){
 4         String filePath = "config/2018年11月中华人民共和国县以上行政区划代码.json";
 5         List<Province> provinceList = Utils.getFile(request,filePath);
 6         if (provinceList != null && provinceList.size()>0){
 7             for (Province province: provinceList) {
 8                 provinceService.insertProvice(province);
 9                 List<City> cityList = province.getCityList();
10                 if (cityList != null && cityList.size()>0){
11                     for (City city: cityList) {
12                         city.setProv_id(province.getProv_id());
13                         cityService.insertCity(city);
14                         List<County> countyList = city.getCountyList();
15                         if (countyList != null && countyList.size()>0){
16                             for (County county: countyList) {
17                                 county.setCity_id(city.getCity_id());
18                                 countyService.insertCounty(county);
19                             }
20                         }
21                     }
22                 }
23 
24             }
25         }
26        return null;
27     }
View Code

 

https://blog.csdn.net/jiangxuqaz/article/details/80264085 // 这个网址是操作 

获取最新最完整的省市县数据 转换 json 文件的

posted on 2019-01-23 09:57  蒙古码农  阅读(7680)  评论(0)  编辑  收藏  举报

Powered by: 博客园 Copyright © 2024 蒙古码农
Powered by .NET 9.0 on Kubernetes