Jsoup access to national data (town and County Township) (continued) Pure dry goods sharing

A few days ago to share a bit, how to get from the National Bureau of Statistics on the official website to obtain the county town of Jsoup data. Please click here for missed friends.
How do we convert to what we want to format after capturing the data? Haha, the analytic way may be very simple, but one thing I know, a lot of people are reaching the party, then I will take my process to share with you, feel good, please point a praise.

The first step: Convert the obtained TXT file into a database file:

Here need to note, the following all the resources compressed files, decompression password are my blog park nickname. Why add a password to explain: The previous blog post was crawled by many other sites, but there is no original link or reprint instructions, do not respect the copyright of the original blog post. Please understand the inconvenience caused to us.

Text data after Last Post processing: Click to download

Needless to say, directly on the code to convert the crawled text file into database data:

1 ImportJava.io.BufferedReader; 2 ImportJava.io.File; 3 ImportJava.io.FileNotFoundException; 4 ImportJava.io.FileReader; 5 ImportJava.io.IOException; 6 ImportJava.sql.Connection; 7 ImportJava.sql.DriverManager; 8 ImportJava.sql.SQLException; 9 ImportJAVA.SQL.STATEMENT;10 public classResolveData112{private static Connection Connection = nullN. public static voidMain (string[] args) 16{17Initdb (); BufferedReader BufferedReader = null; Try21{BufferedReader = new BufferedReader (new FileReader (New File ("F:\\cityinfo.txt"))); String line = null; "(line = Bufferedreader.readline ()) = null) 25{26Inser2db (line), Getcitylevel (line), Getcitycode, System.out.println ("in process ....")); 28}29} catch(FileNotFoundException e) 30{31E.printstacktrace ();(IOException e) 33{34E.printstacktrace (); 35}36}37-private static voidInitdb (39){try41{Class.forName ("Com.mysql.jdbc.Driver"); connection = Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/51houseservice", "Database Account", 44 "Database Password"); catch(SQLException e) 46{47E.printstacktrace ();(ClassNotFoundException e) 49{50E.printstacktrace (); 51}52}53-Private staticString Getcityname (string line) 55{line.substring return (0, Line.indexof ("{")); Getcitycode }58 private static  string (string line)  {line.substring (line.ind Exof ("[") + 1, line.indexof ("]" )), "}63" ("+"), "Getcitylevel" ("+"), "G" (String line),   Eturn Integer.parseint (line.substring (Line.indexof ("{") + 1, Line.indexof ("}" ))); }68 private static void Inser2db (string cityname, int  citylevel, string citycode)- {try72- {$ Statement createstat Ement =  connection.createstatement ();  createStatement76. executeupdate ("INSERT INTO _51houseservice_ Citys_copy (City_name_zh,city_level,city_code) VALUES (' "+ CityName +" ', "+ Citylevel +", ' "+ Citycode +" ') "); 78 } catch  (SQLException e)  {e.printstacktrace  (); Bayi }82 }83}                     

After executing the above procedure, the data has been duly put into the database. Data stored in the database, I believe you yards are master, these data have become your chopping board and fish it.

Step two: Add each row of data in the database to its parent city

Careful friend must have found that each of the above city data contains only a detailed information of its own, but the provincial city and city-level cities do not have any association. Tree-based data how the database should be stored I won't say much. This is directly affixed to the code of the cities associated with each subordinate:

The following is the code in the process:

1 packageCom.wyhousesevice.test; 2 ImportJava.sql.Connection; 3 ImportJava.sql.DriverManager; 4 ImportJava.sql.ResultSet; 5 ImportJava.sql.SQLException; 6 ImportJava.sql.Statement; 7 ImportJava.util.ArrayList; 8 ImportJava.util.List; 9 public classRESOLVEDATA3 11{The private staticConnection Connection; public static voidMain (string[] args) 15{16Initdb (); + Try 18{19//Get a row of data in the source table ResultSet rs =Getallcitys (); 21stRs.next (); While(Rs.next ()) 23{24//If the item has a parent ID, skip the Set if (Rs.getint ("parent_id") = = 0) 26{list<string> parentcodes = getparentcodes (rs.getstring ("City_code")); 28//Get the data ID of the target database ParentID = Getparentid (Parentcodes, Rs.getint ("City_level")-1); DoUpdate (Rs.getint ("id"), ParentID); System.out.println ("Handling:" + rs.getint ("id"))); 32} 33} 34} The Catch(SQLException e) 36{37E.printstacktrace (); 38} 39 40Closedb (); 41} The private static void doupdate (int id, intParentID) 44{Try 46{Statement Statement =Connection.createstatement (); Statement.executeupdate ("UPDATE _51houseservice_citys_copy SET parent_id =" + ParentID + "WHERE id =" 49 +ID); 50} A Catch(SQLException e) 52{53E.printstacktrace (); 54} 55} getparentid private static int (list<string> parentcodes, int level) throwsSQLException 58{Statement Statement =Connection.createstatement (); For(String string:parentcodes) 61{ResultSet ExecuteQuery =Statement. ExecuteQuery ("select * from _51houseservice_citys_copy where city_code= '" +String + "' and city_level=" +level); If(Executequery.next ()) 66{Executequery.getint return ("id")); 68} 69} return-1; 71} list<string> private staticGetparentcodes (String Citycode) 74{list<string> dataList = new arraylist<string>(); if (Citycode.endswith ("0"))) 78{The String code =Rmvlastzero (Citycode); for (int i = 1; i < Code.length ()-1; i++) 81{substring String = code.substring (0, Code.length ()-i); StringBuilder sb = newStringBuilder (substring); + for (int j = Substring.length (); J <) 85{Sb.append ("0")); 87} 88Datalist.add (Sb.tostring ()); 89} 90} 92{i++ for (int i = 1; i < Citycode.length ()-1;) 94{substring String = citycode.substring (0, Citycode.length ()-i); StringBuilder sb = newStringBuilder (substring); $ for (int j = 1; J <= I; j + +) 98{Sb.append ("0")); 100}101Datalist.add (Sb.tostring ()); 102}103}104 returndatalist;105}106 107 private staticString Rmvlastzero (String citycode) 108{109 while (Citycode.endswith ("0")) 110{111 Citycode = citycode.substring (0, Citycode.length ()-1); 112}113 returncitycode;114}115, private staticResultSet Getallcitys () 117{118 try119{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 $ private static void  closedb () 131  {(connection! = NULL ) 133  {134 try135  {136  connection.close (); 137 }138 catch  (SQLException e) 139  {
                                                                                                                           
                                                                                                                             E.printstacktrace (); 141 
                                                                                                                            }142 }143 }144 145 private static void  Initdb () 146  {147 TRY14 8  {149 Class.forName ("Com.mysql.jdbc.Driver" ), connection =  DriverManager151. getconnection (" Jdbc:mysql://localhost:3306/51houseservice "," Database Account "," Database Password "), }153 catch  (SQLException e) 154 span> {155  e.printstacktrace () 156 }157 catch  (ClassNotFoundException e) 158  {159  E.print StackTrace (); }161 }162}           
                                                                                                                            

Then it takes time to deal with it, and to deal with it slowly ..... The results of the resulting SQL dump file are as follows: Click to download

If you think this blog is helpful to you, please remember to click on " recommend " at the bottom right! , what's The DA ...

Reprint Please specify source: http://www.cnblogs.com/liushaofeng89/p/4937714.html

Jsoup access to national data (town and County Township) (continued) Pure dry goods sharing