数据库中省区市组装成json_完整版中国省市县数据 存储到数据库中

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 forbase_province5 -- ----------------------------

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 forbase_city5 -- ----------------------------

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 forbase_county5 -- ----------------------------

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 packagecom.text.springbootdemo.entity;2

3 importlombok.Data;4

5 importjavax.persistence.ElementCollection;6 importjavax.persistence.Entity;7 importjavax.persistence.GeneratedValue;8 importjavax.persistence.Id;9 importjava.util.Date;10 importjava.util.List;11

12 @Entity13 //@Proxy(lazy = false)14 //@DynamicUpdate//自动更新时间

15 @Data16 public classProvince {17 @Id18 //@GeneratedValue(strategy = GenerationType.IDENTITY)

19 @GeneratedValue20 privateInteger prov_id;21 privateString prov_name;22 privateDate create_date;23 privateString region_code;24

25 @ElementCollection26 private ListcityList;27

28 publicProvince(){}29

30 public Province(String prov_name, Date create_date, String region_code, ListcityList) {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 packagecom.text.springbootdemo.entity;2

3 importlombok.Data;4

5 importjavax.persistence.ElementCollection;6 importjavax.persistence.Entity;7 importjavax.persistence.GeneratedValue;8 importjavax.persistence.Id;9 importjava.util.Date;10 importjava.util.List;11

12 @Entity13 //@Proxy(lazy = false)14 //@DynamicUpdate//自动更新时间

15 @Data16 public classCity {17 @Id18 //@GeneratedValue(strategy = GenerationType.IDENTITY)

19 @GeneratedValue20 privateInteger city_id;21 privateInteger prov_id;22 privateString city_name;23 privateDate create_date;24 privateString region_code;25

26 @ElementCollection27 private ListcountyList;28

29 publicCity(){}30

31 public City(Integer prov_id, String city_name, Date create_date, String region_code, ListcountyList) {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 packagecom.text.springbootdemo.entity;2

3 importlombok.Data;4

5 importjavax.persistence.Entity;6 importjavax.persistence.GeneratedValue;7 importjavax.persistence.Id;8 importjava.util.Date;9

10 @Entity11 //@Proxy(lazy = false)12 //@DynamicUpdate//自动更新时间

13 @Data14 public classCounty {15 @Id16 //@GeneratedValue(strategy = GenerationType.IDENTITY)

17 @GeneratedValue18 privateInteger county_id;19 privateInteger city_id;20 privateString county_name;21 privateDate create_date;22 privateString region_code;23

24 publicCounty(){}25

26 publicCounty(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 packagecom.text.springbootdemo.dao;2

3 importcom.text.springbootdemo.entity.Province;4 importorg.springframework.stereotype.Repository;5

6 @Repository7 public interfaceProvinceDao {8 voidinsertProvice(Province province);9 }

省 provinceDao

1 packagecom.text.springbootdemo.dao;2

3 importcom.text.springbootdemo.entity.City;4 importorg.springframework.stereotype.Repository;5

6 @Repository7 public interfaceCityDao {8 voidinsertCity(City city);9 }

市 cityDao

1 packagecom.text.springbootdemo.dao;2

3 importcom.text.springbootdemo.entity.County;4 importorg.springframework.stereotype.Repository;5

6 @Repository7 public interfaceCountyDao {8 voidinsertCounty(County county);9 }

县 countyDao

(3)service 层

1 packagecom.text.springbootdemo.service;2

3 importcom.text.springbootdemo.dao.ProvinceDao;4 importcom.text.springbootdemo.entity.Province;5 importlombok.extern.slf4j.Slf4j;6 importorg.springframework.beans.factory.annotation.Autowired;7 importorg.springframework.stereotype.Service;8 importorg.springframework.transaction.annotation.Transactional;9

10 @Service11 @Transactional12 @Slf4j13 public classProvinceService {14

15 @Autowired16 privateProvinceDao provinceDao;17

18 public voidinsertProvice(Province province){19 provinceDao.insertProvice(province);20 }21 }

省 provinceService

1 packagecom.text.springbootdemo.service;2

3 importcom.text.springbootdemo.dao.CityDao;4 importcom.text.springbootdemo.entity.City;5 importlombok.extern.slf4j.Slf4j;6 importorg.springframework.beans.factory.annotation.Autowired;7 importorg.springframework.stereotype.Service;8 importorg.springframework.transaction.annotation.Transactional;9

10 @Service11 @Transactional12 @Slf4j13 public classCityService {14

15 @Autowired16 privateCityDao cityDao;17

18 public voidinsertCity(City city){19 cityDao.insertCity(city);20 }21 }

cityService

1 packagecom.text.springbootdemo.service;2

3 importcom.text.springbootdemo.dao.CountyDao;4 importcom.text.springbootdemo.entity.County;5 importlombok.extern.slf4j.Slf4j;6 importorg.springframework.beans.factory.annotation.Autowired;7 importorg.springframework.stereotype.Service;8 importorg.springframework.transaction.annotation.Transactional;9

10 @Service11 @Transactional12 @Slf4j13 public classCountyService {14

15 @Autowired16 privateCountyDao countyDao;17

18 public voidinsertCounty(County county){19 countyDao.insertCounty(county);20 }21 }

县 countyService

(4)读取 json文件工具类

1 packagecom.text.springbootdemo.utils;2

3 importcom.alibaba.fastjson.JSONArray;4 importcom.alibaba.fastjson.JSONObject;5 importcom.text.springbootdemo.entity.City;6 importcom.text.springbootdemo.entity.County;7 importcom.text.springbootdemo.entity.Province;8 importorg.apache.commons.io.FileUtils;9

10 importjavax.servlet.http.HttpServletRequest;11 importjava.io.File;12 importjava.util.ArrayList;13 importjava.util.Date;14 importjava.util.List;15

16 public classUtils {17 public static ListgetFile(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 = newDate();25

26 List provinceList = null;27 List cityList = null;28 List countyList = null;29

30 String path = request.getSession().getServletContext().getRealPath("/");31 path = path +filePath;32 File file = newFile(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();41 if (proJsonArray !=null) {42 for (int i = 0; i < proJsonArray.size(); i++){43 Province province = newProvince();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();51 if (cityJsonArray != null) {52 for (int j = 0; j < cityJsonArray.size(); j++){53 City city = newCity();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();60 if (countyJsonArray != null) {61 for (int k = 0; k < countyJsonArray.size(); k++){62 County county = newCounty();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 returnprovinceList;83 }84

85 //下面的 main 方法没有用到

86 public static voidmain(String[] args) {87 //test();

88 }89 }

读取json文件工具类

(5)controller 层

1 @RequestMapping("/file")2 @ResponseBody3 privateString getFile(HttpServletRequest request){4 String filePath = "config/2018年11月中华人民共和国县以上行政区划代码.json";5 List provinceList =Utils.getFile(request,filePath);6 if (provinceList != null && provinceList.size()>0){7 for(Province province: provinceList) {8 provinceService.insertProvice(province);9 List 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 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 文件的