获得全国地区表

爱被打了一巴掌 2023-02-14 10:42 19阅读 0赞

表

CREATE TABLE \`b\_area\` (  
  \`id\` bigint(18) NOT NULL COMMENT 'id',  
  \`name\` varchar(50) NOT NULL COMMENT '名称',  
  \`full\_name\` varchar(255) DEFAULT NULL COMMENT '全称',  
  \`address\` varchar(255) DEFAULT NULL COMMENT '详细地址',  
  \`pinyin\` varchar(50) DEFAULT NULL COMMENT '拼音',  
  \`level\` tinyint(1) NOT NULL COMMENT '1-省;2-市;3-县,4-乡镇;5-村/社区',  
  \`code\` varchar(12) NOT NULL COMMENT '编码',  
  \`parent\_id\` bigint(18) NOT NULL DEFAULT '0' COMMENT '父级ID,0表示没有父级',  
  \`create\_date\` datetime DEFAULT NULL COMMENT '创建日期',  
  \`modify\_date\` datetime DEFAULT NULL COMMENT '修改日期',  
  \`visible\` bit(1) NOT NULL DEFAULT b'1' COMMENT '状态，默认为1',  
  PRIMARY KEY (\`id\`)  
) ENGINE=MyISAM AUTO\_INCREMENT=3664 DEFAULT CHARSET=utf8 COMMENT='地区表';

国家统计局：

[http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2019/index.html][http_www.stats.gov.cn_tjsj_tjbz_tjyqhdmhcxhfdm_2019_index.html]

链接：https://pan.baidu.com/s/1zbZI9LyE3JP1cL9ErxW6AQ   
提取码：g3rm

导入设计成我上面的字段，code最好根据层级缩进，方便模糊查询

UPDATE \`province\_city\_county\` SET \`code\`= left(\`code\`,2) WHERE \`level\`=1;  
UPDATE \`province\_city\_county\` SET \`code\`= left(\`code\`,4) WHERE \`level\`=2;  
UPDATE \`province\_city\_county\` SET \`code\`= left(\`code\`,6) WHERE \`level\`=3;  
UPDATE \`province\_city\_county\` SET \`code\`= left(\`code\`,10) WHERE \`level\`=4;

SELECT \* FROM province\_city\_county WHERE \`level\`=1;  
SELECT \* FROM province\_city\_county WHERE \`level\`=2;  
SELECT \* FROM province\_city\_county WHERE \`level\`=3;  
SELECT \* FROM province\_city\_county WHERE \`level\`=4;  
SELECT \* FROM province\_city\_county WHERE \`level\`=5;

对fullName和pinyin做处理

<dependency>
        <groupId>com.belerweb</groupId>
        <artifactId>pinyin4j</artifactId>
        <version>2.5.0</version>
    </dependency>

import com.cdls.carp.common.util.PinyinUtil;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import java.util.List;
    
    @SpringBootTest
    @RunWith(SpringRunner.class)
    public class CarpApplicationTests {
    
    
        @Autowired
        private AreaService areaService;
    
        @Test
        public void contextLoads() {
            List<AreaEntity> areaEntityList = areaService.selectByLevel(1L);
            String fullName="";
            boolean a=false;
            boolean b=false;
            boolean c=false;
            boolean d=false;
            for (AreaEntity a1:areaEntityList
                 ) {
                fullName+=a1.getName();
                a1.setFullName(fullName);
                a1.setPinyin(PinyinUtil.getPinYin(a1.getName()));
                if(!a){
                    a=true;
                    System.out.println("a:"+fullName);
                }
                //成都市等
                IPage<AreaEntity> areaEntityIPage = areaService.selectPage(1L, 10000L, a1.getId());
                List<AreaEntity> areaEntityList1=areaEntityIPage.getRecords();
                String fullName1="";
                for (AreaEntity a2:areaEntityList1
                ) {
                    fullName1=fullName+a2.getName();
                    a2.setFullName(fullName1);
                    a2.setPinyin(PinyinUtil.getPinYin(a2.getName()));
                    if(!b){
                        b=true;
                        System.out.println("b:"+fullName1);
                    }
                    //区县等
                    IPage<AreaEntity> areaEntityIPage1 = areaService.selectPage(1L, 10000L, a2.getId());
                    List<AreaEntity> areaEntityList2=areaEntityIPage1.getRecords();
                    String fullName2="";
                    for (AreaEntity a3:areaEntityList2
                    ) {
                        fullName2=fullName1+a3.getName();
                        a3.setFullName(fullName2);
                        a3.setPinyin(PinyinUtil.getPinYin(a3.getName()));
                        if(!c){
                            c=true;
                            System.out.println("c:"+fullName2);
                        }
                        //街道等
                        IPage<AreaEntity> areaEntityIPage2 = areaService.selectPage(1L, 10000L, a3.getId());
                        List<AreaEntity> areaEntityList3=areaEntityIPage2.getRecords();
                        String fullName3="";
                        for (AreaEntity a4:areaEntityList3
                        ) {
                            fullName3=fullName2+a4.getName();
                            a4.setFullName(fullName3);
                            a4.setPinyin(PinyinUtil.getPinYin(a4.getName()));
                            if(!d){
                                d=true;
                                System.out.println("d:"+fullName3);
                            }
                            areaService.saveOrUpdate(a4);
                        }
                        areaService.saveOrUpdate(a3);
                    }
                    areaService.saveOrUpdate(a2);
                }
                areaService.saveOrUpdate(a1);
            }
            System.out.println(areaEntityList.size()+"------"+PinyinUtil.getPinYin(areaEntityList.get(0).getName()));
        }
    
    
    
    
    
    }

这样就可以了

博主强烈推荐：https://blog.csdn.net/persistencegoing/article/details/84376427

希望大家关注我一波,防止以后迷路,有需要的可以加群讨论互相学习java ,学习路线探讨,经验分享与java求职

群号:721 515 304

[http_www.stats.gov.cn_tjsj_tjbz_tjyqhdmhcxhfdm_2019_index.html]: http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2019/index.html