你的位置:首页 > Java教程

[Java教程]Jsoup获取全国地区数据(省市县镇村)(续) 纯干货分享


前几天给大家分享了一下,怎么样通过jsoup来从国家统计局官网获取全国省市县镇村的数据。错过的朋友请点击这里
上文说到抓取到数据以后,我们怎么转换成我们想要格式呢?哈哈,解析方式可能很简单,但是有一点我是知道的,很多人是伸手党,那么我就把我的处理过程给大家分享出来,觉得不错的,请点个赞。

第一步:将获取到的txt文件转换成数据库文件:

这里需要备注一下,下文所有的资源压缩文件,解压密码都是我的博客园昵称。为什么要加密码给大家解释一下:前期发出的博文被其他很多站点爬取了,但是都没有原文链接或者转载说明,一点都不尊重原博文的版权。给大家带来的不便,敬请谅解。

上次博文处理后的文本数据下载地址:点击下载

废话不多说,直接上代码将抓取到的文本文件转换成数据库数据:

 1 import java.io.BufferedReader; 2 import java.io.File; 3 import java.io.FileNotFoundException; 4 import java.io.FileReader; 5 import java.io.IOException; 6 import java.sql.Connection; 7 import java.sql.DriverManager; 8 import java.sql.SQLException; 9 import java.sql.Statement;10 11 public class ResolveData112 {13   private static Connection connection = null;14 15   public static void main(String[] args)16   {17     initDB();18 19     BufferedReader bufferedReader = null;20     try21     {22       bufferedReader = new BufferedReader(new FileReader(new File("f:\\CityInfo.txt")));23       String line = null;24       while ((line = bufferedReader.readLine()) != null)25       {26         inser2DB(getCityName(line), getCityLevel(line), getCityCode(line));27         System.out.println("处理中……");28       }29     } catch (FileNotFoundException e)30     {31       e.printStackTrace();32     } catch (IOException e)33     {34       e.printStackTrace();35     }36   }37 38   private static void initDB()39   {40     try41     {42       Class.forName("com.mysql.jdbc.Driver");43       connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/51houseservice", "数据库账户",44         "数据库密码");45     } catch (SQLException e)46     {47       e.printStackTrace();48     } catch (ClassNotFoundException e)49     {50       e.printStackTrace();51     }52   }53 54   private static String getCityName(String line)55   {56     return line.substring(0, line.indexOf("{"));57   }58 59   private static String getCityCode(String line)60   {61     return line.substring(line.indexOf("[") + 1, line.indexOf("]"));62   }63 64   private static int getCityLevel(String line)65   {66     return Integer.parseInt(line.substring(line.indexOf("{") + 1, line.indexOf("}")));67   }68 69   private static void inser2DB(String cityName, int cityLevel, String cityCode)70   {71     try72     {73 74       Statement createStatement = connection.createStatement();75       createStatement76         .executeUpdate("insert into _51houseservice_citys_copy(city_name_zh,city_level,city_code) values('"77           + cityName + "'," + cityLevel + ",'" + cityCode + "')");78     } catch (SQLException e)79     {80       e.printStackTrace();81     }82   }83 }

 执行完以上程序以后,那么数据就已经妥妥的放入数据库了。存入数据库的数据,相信各位码农都是高手,这些数据都成了你们砧板上的与鱼肉了吧。

第二步:将数据库的每一行数据添加上其父城市

细心的朋友一定发现了,上面的每一个城市数据都只是包含一自己本身的详细信息,但是省级城市与市级城市之间没有任何关联。基于树形结构的数据在数据库应该怎样存储我就不多说了。这里就直接贴上关联各上下级关联的城市的代码:

接下来的是处理过程中的代码:

 1 package com.wyhousesevice.test; 2 import java.sql.Connection; 3 import java.sql.DriverManager; 4 import java.sql.ResultSet; 5 import java.sql.SQLException; 6 import java.sql.Statement; 7 import java.util.ArrayList; 8 import java.util.List; 9  10 public class ResolveData3 11 { 12   private static Connection connection; 13  14   public static void main(String[] args) 15   { 16     initDB(); 17     try 18     { 19       // 获取源表中一行数据 20       ResultSet rs = getAllCitys(); 21       rs.next(); 22       while (rs.next()) 23       { 24         // 如果该项存在父ID,则跳过设置 25         if (rs.getInt("parent_id") == 0) 26         { 27           List<String> parentCodes = getParentCodes(rs.getString("city_code")); 28           // 获取目标数据库的数据ID 29           int parentId = getParentId(parentCodes, rs.getInt("city_level") - 1); 30           doUpdate(rs.getInt("id"), parentId); 31           System.out.println("handling:" + rs.getInt("id")); 32         } 33       } 34     } 35     catch (SQLException e) 36     { 37       e.printStackTrace(); 38     } 39  40     closeDB(); 41   } 42  43   private static void doUpdate(int id, int parentId) 44   { 45     try 46     { 47       Statement statement = connection.createStatement(); 48       statement.executeUpdate("UPDATE _51houseservice_citys_copy SET parent_id = " + parentId + " WHERE id = " 49         + id); 50     } 51     catch (SQLException e) 52     { 53       e.printStackTrace(); 54     } 55   } 56  57   private static int getParentId(List<String> parentCodes, int level) throws SQLException 58   { 59     Statement statement = connection.createStatement(); 60     for (String string : parentCodes) 61     { 62       ResultSet executeQuery = statement 63         .executeQuery("select * from _51houseservice_citys_copy where city_code='" + string 64           + "' and city_level=" + level); 65       if (executeQuery.next()) 66       { 67         return executeQuery.getInt("id"); 68       } 69     } 70     return -1; 71   } 72  73   private static List<String> getParentCodes(String cityCode) 74   { 75     List<String> dataList = new ArrayList<String>(); 76  77     if (cityCode.endsWith("0")) 78     { 79       String code = rmvLastZero(cityCode); 80       for (int i = 1; i < code.length() - 1; i++) 81       { 82         String substring = code.substring(0, code.length() - i); 83         StringBuilder sb = new StringBuilder(substring); 84         for (int j = substring.length(); j < 12; j++) 85         { 86           sb.append("0"); 87         } 88         dataList.add(sb.toString()); 89       } 90     } 91     else 92     { 93       for (int i = 1; i < cityCode.length() - 1; i++) 94       { 95         String substring = cityCode.substring(0, cityCode.length() - i); 96         StringBuilder sb = new StringBuilder(substring); 97         for (int j = 1; j <= i; j++) 98         { 99           sb.append("0");100         }101         dataList.add(sb.toString());102       }103     }104     return dataList;105   }106 107   private static String rmvLastZero(String cityCode)108   {109     while (cityCode.endsWith("0"))110     {111       cityCode = cityCode.substring(0, cityCode.length() - 1);112     }113     return cityCode;114   }115 116   private static ResultSet getAllCitys()117   {118     try119     {120       Statement createStatement = connection.createStatement();121       return createStatement.executeQuery("select * from _51houseservice_citys_copy");122     }123     catch (SQLException e)124     {125       e.printStackTrace();126       return null;127     }128   }129 130   private static void closeDB()131   {132     if (connection != null)133     {134       try135       {136         connection.close();137       }138       catch (SQLException e)139       {140         e.printStackTrace();141       }142     }143   }144 145   private static void initDB()146   {147     try148     {149       Class.forName("com.mysql.jdbc.Driver");150       connection = DriverManager151         .getConnection("jdbc:mysql://localhost:3306/51houseservice", "数据库账户", "数据库密码");152     }153     catch (SQLException e)154     {155       e.printStackTrace();156     }157     catch (ClassNotFoundException e)158     {159       e.printStackTrace();160     }161   }162 }

 接下来就需要时间处理了,慢慢的处理.....最终得到的sql转储文件结果如下:点击下载

  

如果你觉得本博文对你有所帮助,请记得点击右下方的"推荐"哦,么么哒... 

 转载请注明出处:http://www.cnblogs.com/liushaofeng89/p/4922310.html