完整版中国省市县数据 存储到数据库中
生活随笔
收集整理的這篇文章主要介紹了
完整版中国省市县数据 存储到数据库中
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
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 文件的
總結
以上是生活随笔為你收集整理的完整版中国省市县数据 存储到数据库中的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 用分布式路由器（Mesh）进行家庭组网
- 下一篇： 位、字节、字、双字