Jsoup獲取全國(guó)地區(qū)數(shù)據(jù)（省市縣鎮(zhèn)村）(續(xù))純干貨分享

2019-11-14 15:10:51

字體：大中小

供稿：網(wǎng)友

前幾天給大家分享了一下，怎么樣通過(guò)jsoup來(lái)從國(guó)家統(tǒng)計(jì)局官網(wǎng)獲取全國(guó)省市縣鎮(zhèn)村的數(shù)據(jù)。錯(cuò)過(guò)的朋友請(qǐng)點(diǎn)擊這里。
上文說(shuō)到抓取到數(shù)據(jù)以后，我們?cè)趺崔D(zhuǎn)換成我們想要格式呢？哈哈，解析方式可能很簡(jiǎn)單，但是有一點(diǎn)我是知道的，很多人是伸手黨，那么我就把我的處理過(guò)程給大家分享出來(lái)，覺(jué)得不錯(cuò)的，請(qǐng)點(diǎn)個(gè)贊。

第一步：將獲取到的txt文件轉(zhuǎn)換成數(shù)據(jù)庫(kù)文件：

這里需要備注一下，下文所有的資源壓縮文件，解壓密碼都是我的博客園昵稱(chēng)。為什么要加密碼給大家解釋一下：前期發(fā)出的博文被其他很多站點(diǎn)爬取了，但是都沒(méi)有原文鏈接或者轉(zhuǎn)載說(shuō)明，一點(diǎn)都不尊重原博文的版權(quán)。給大家?guī)?lái)的不便，敬請(qǐng)諒解。

上次博文處理后的文本數(shù)據(jù)下載地址：點(diǎn)擊下載

廢話(huà)不多說(shuō)，直接上代碼將抓取到的文本文件轉(zhuǎn)換成數(shù)據(jù)庫(kù)數(shù)據(jù)：

 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("處理中&hell);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", "數(shù)據(jù)庫(kù)賬戶(hù)",44                 "數(shù)據(jù)庫(kù)密碼");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 }

執(zhí)行完以上程序以后，那么數(shù)據(jù)就已經(jīng)妥妥的放入數(shù)據(jù)庫(kù)了。存入數(shù)據(jù)庫(kù)的數(shù)據(jù)，相信各位碼農(nóng)都是高手，這些數(shù)據(jù)都成了你們砧板上的與魚(yú)肉了吧。

第二步：將數(shù)據(jù)庫(kù)的每一行數(shù)據(jù)添加上其父城市

細(xì)心的朋友一定發(fā)現(xiàn)了，上面的每一個(gè)城市數(shù)據(jù)都只是包含一自己本身的詳細(xì)信息，但是省級(jí)城市與市級(jí)城市之間沒(méi)有任何關(guān)聯(lián)。基于樹(shù)形結(jié)構(gòu)的數(shù)據(jù)在數(shù)據(jù)庫(kù)應(yīng)該怎樣存儲(chǔ)我就不多說(shuō)了。這里就直接貼上關(guān)聯(lián)各上下級(jí)關(guān)聯(lián)的城市的代碼：

接下來(lái)的是處理過(guò)程中的代碼：

  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             // 獲取源表中一行數(shù)據(jù) 20             ResultSet rs = getAllCitys(); 21             rs.next(); 22             while (rs.next()) 23             { 24                 // 如果該項(xiàng)存在父ID，則跳過(guò)設(shè)置 25                 if (rs.getInt("parent_id") == 0) 26                 { 27                     List<String> parentCodes = getParentCodes(rs.getString("city_code")); 28                     // 獲取目標(biāo)數(shù)據(jù)庫(kù)的數(shù)據(jù)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", "數(shù)據(jù)庫(kù)賬戶(hù)", "數(shù)據(jù)庫(kù)密碼");152         }153         catch (SQLException e)154         {155             e.printStackTrace();156         }157         catch (ClassNotFoundException e)158         {159             e.printStackTrace();160         }161     }162 }