Simple Java Capture Program two

Following the "Simple Java Capture Program", this will complete the collection task for the entire site's number segment.

"Using precompilation + batching to capture Web page content into a database table"

Before we used the statement class to create the execution object of the SQL statement to implement the operation of inserting the field into the database, but because of the large amount of data inserted, it would be time-consuming to continue using statement to manipulate the session, and we would use its subclass PreparedStatement to operate.

PreparedStatement can implement the pre-compilation of SQL statements, we only need to pass the parameters through its setstring () method, which is not only more efficient, but also more secure, to prevent SQL injection. Recommend related articles

In addition, we can call its Addbatch () method and ExecuteBatch () to implement BULK insert processing.

The code below, like the database link as a separate class

Import Java.sql.drivermanager;import Java.sql.sqlexception;import Com.mysql.jdbc.connection;public class Database { public static string driver = "Com.mysql.jdbc.Driver";p ublic static string Url= "Jdbc:mysql://127.0.0.1:3306/tele_dat? Autoreconnect=true&characterencoding=utf-8 ";p ublic static string user =" root ";p ublic static string password =" 1234 ";p ublic static Java.sql.Connection conn = null;//Returns a database connection object public static Connection Connecttodatabase () {try {Class. forname (driver);} catch (ClassNotFoundException e) {System.out.println ("load driver failed"); E.printstacktrace ();} try {conn = drivermanager.getconnection (URL, user, password); SYSTEM.OUT.PRINTLN ("Connection succeeded");} catch (SQLException e) {System.out.println ("connection problem"); E.printstacktrace ();} Return (CONNECTION) conn;} Test connection database public static void main (String args[]) {database. Connecttodatabase ();}}

Main program

Import Java.io.bufferedreader;import java.io.ioexception;import java.io.inputstreamreader;import Java.net.URL; Import Java.sql.connection;import Java.sql.preparedstatement;import Java.util.regex.matcher;import Java.util.regex.pattern;public class Crawl {private static Connection conn = database.      Connecttodatabase ();   static String Home_url = "http://www.hiphop8.com"; Website Home static String pattern_pro_city = "<div class=title><span> (. *?) - (.*?)   <\\/SPAN><\\/DIV> ";  Match province name, city name static String Pattern_number = "> (13\\d{5}|15\\d{5}|18\\d{5}|147\\d{4}) <";   Match number segment static String Pattern_pro = "\\w{3}\\.\\w{7}\\.\\w{3}\\/\\w{4}\\/\\w+"; Province urlstatic String pattern_city_hz= "<li><a href=\" (. *?)    \ "Target=_blank>"; City URL suffix//compile preprocessing related options static String Insertsql = "Insert ignore into number_segment (segment,province,city) VALUES (?,?,?)    "; static PreparedStatement PST = NULL;    static int num_pro = 0;static int num_city=0; static int All_nuM_tele = 0;public static void Main (string[] args) throws Exception {String Prestat = "Insert Ignore into number_segment ( segment,province,city) VALUES (?,?,?)  "; PST = Conn.preparestatement (prestat.tostring ()); Matcher mat_home = Get (Home_url,pattern_pro), long start = System.currenttimemillis (); while (Mat_home.find ()) {num_pro+ +; SYSTEM.OUT.PRINTLN ("------" +num_pro+ "province-----"); String CITY_URL_QZ = "http:/" +mat_home.group () + "/"; int len = City_url_qz.length ();// This is replaced with StringBuffer to add the most string to the stringbuffer City_ur = new StringBuffer (); City_ur.append (CITY_URL_QZ); Matcher mat_city_hz = Get (City_url_qz,pattern_city_hz), while (Mat_city_hz.find ())//by stitching to get the city's complete url{num_ci ty++; System.out.println ("+num_city+", "the City of"); String Last_city_url=city_ur.append (Mat_city_hz.group (1)). ToString ();//string Last_city_url = City_url_qz + mat_city _hz.group (1); int len2 = Last_city_url.length (); one_city_tele_to_db (Last_city_url); City_ur.delete (LEN,LEN2);}} Long end = System.currenttimemillis (); Long time = (End-start)/(1000*60); Conn.close (); SYSTEM.OUT.PRINTLN ("Total number of phone number segments queried:" +all_num_tele); System.out.println ("The time Spent is:" +time);}    public static void one_city_tele_to_db (String url) throws Exception {int this_city_num=0;    String pro = null;     String city = null;  Matcher mat_pro_city = Get (url,pattern_pro_city);     Gets the province name City name while (Mat_pro_city.find ()) {String Long_pro = Mat_pro_city.group (1); Pro = long_pro.substring (0, Long_pro.length ()-1);     String long_city = Mat_pro_city.group (2); City = long_city.substring (0, Long_city.length ()-10);  System.out.println ("Province:" +pro+ "" + "City:" +city+ "inserting the number segment into the database");      } Matcher Mat_number = Get (Url,pattern_number); Get the number segment while (Mat_number.find ()) {pst.setstring (1,mat_number.group (1)); Pst.setstring (2, pro); Pst.setstring (3, City ); Pst.addbatch (); this_city_num++; all_num_tele++;  } pst.executebatch (); Each batch inserts a city's number segment Pst.clearbatch (); System.out.println ("Number of number segments inserted in the city is:" + this_city_num);} Regular matches public static Matcher get (String Str_url, String pattern) throws Exception {string urlsource = get_html (Str_url); Pattern P = pattern.compile (pattern); Matcher m = P.matcher (Urlsource); return m;}    Gets the Web content public static string get_html (String str_url) throws ioexception{url url = new URL (str_url); String content= "";                    StringBuffer page = new StringBuffer (); try {bufferedreader in = new BufferedReader (new InputStreamReader (URL . OpenStream ()); while (content = In.readline ()) = null) {page.append (content);}}        catch (IOException e) {e.printstacktrace ();}    return page.tostring (); }}

The actual operation of the program, found that there are more than 500 duplicate number segment, because Xiangfan changed to Xiangyang City, the two city of the number of the same segment, and the database table is segment (number) as the primary key, so to set, when inserting the same primary key SQL statement, automatically ignore the skip, the method is in the insert Add ignore to the back.

Also set the ID to auto_increment, but if the data table data is emptied, the ID will not start again from 1, then as long as the MySQL command line input TRUNCATE TABLE TABLE_NAME can be implemented ID starting from 1.

Run results

Obviously, 6 minutes of speed is too slow, after the interview a few times in 6-8 minutes (but relative to the use of statement without batching is much faster). So we have to find a way to optimize.

"Append () method of using Intelligent batch processing +stringbuilder"

When inserting a MySQL database table, you can insert ignore into number_segment (segment,province,city) VALUES (?,?,?), Vaulues (?), Values (?,?,?) ... In the form of bulk INSERT, the above using the SetString () method to pass the parameter or too slow, directly with the StringBuilder append () method, we all know that the Appedn () method for string addition processing efficiency is very high, So each time we add a "vaulues (???") to the INSERT statement. ，？ ), "In this form of string, and then batch insert, here through the variable all_tele_num for batch processing control, we require each batch to execute 2000 pieces of data segment insertion."

       just look at the code.

Import Java.io.bufferedreader;import java.io.ioexception;import java.io.inputstreamreader;import Java.net.URL; Import Java.sql.connection;import Java.sql.preparedstatement;import Java.util.regex.matcher;import Java.util.regex.pattern;public class Secondcrawl {private static Connection conn = database. Connecttodatabase ();//pre-compile + stringbuilderstatic StringBuilder Prestat = new StringBuilder (); static String Qz = "Insert ignore into number_segment (segment,province,city) values";    static String Insertsql = "Insert ignore into number_segment (segment,province,city) VALUES (?,?,?)";    static int len1 = Qz.length ();    static PreparedStatement PST = NULL;        static String Home_url = "http://www.hiphop8.com"; static String pattern_pro_city = "<div class=title><span> (. *?) - (.*?)   <\\/SPAN><\\/DIV> ";  Match province name, city name static String Pattern_number = "> (13\\d{5}|15\\d{5}|18\\d{5}|147\\d{4}) <"; Match number segment static String Pattern_pro = "\\w{3}\\.\\w{7}\\.\\w{3}\\/\\w{4}\\/\\w+"; Province urlstatic String pattern_city_hz= "<li><a href=\" (. *?)    \ "Target=_blank>"; Suffix of city url static int num_pro = 0;static int num_city=0;static int all_num_tele=0;public static void main (string[] args)  Throws Exception {Matcher mat_home = get (Home_url,pattern_pro); Conn.setautocommit (true);  Prestat.append (QZ);   PST = Conn.preparestatement (insertsql); Precompiled Long start = System.currenttimemillis (); while (Mat_home.find ()) {num_pro++; SYSTEM.OUT.PRINTLN ("------" +num_pro+ "province-----"); String CITY_URL_QZ = "http:/" +mat_home.group () + "/"; int len = City_url_qz.length (); StringBuffer City_ur = new StringBuffer (); City_ur.append (CITY_URL_QZ); Matcher mat_city_hz = Get (City_url_qz,pattern_city_hz), while (Mat_city_hz.find ())//Get the city's url{num_city++; System.out.println ("+num_city+", "the City of"); String City_url=city_ur.append (Mat_city_hz.group (1)). toString (); int len2 = City_url.length (); one_city_tele_to_db ( City_url); City_ur.delete (LEN,LEN2);}} Long end = System.currenttimemillis (); Long time = (End-start)/(1000*60);p st.executebatch (); Batch execution The remaining portion of the last surface conn.close (); SYSTEM.OUT.PRINTLN ("Total number of phone number segments queried:" +all_num_tele); System.out.println ("The time Spent is:" +time+ "minutes" + "+" in microseconds: "+ (End-start) +" microseconds ");} A city cell phone number segment processing function public static void one_city_tele_to_db (string url) throws Exception {string city=null;    String Pro =null; int this_city_num = 0;  Matcher mat_pro_city = Get (url,pattern_pro_city); while (Mat_pro_city.find ()) {String Long_pro = Mat_pro_city.group (1); pro = long_pro.substring (0, Long_pro.length ()-1); String long_city = Mat_pro_city.group (2);  City = long_city.substring (0, Long_city.length ()-10);  System.out.println ("Province:" +pro+ "" + "City:" +city+ "inserting number segment into database ..."); } String temp = ", '" +pro+ "', '" +city+ "'),"; Matcher Mat_number = Get (Url,pattern_number); while (Mat_number.find ()) {Prestat.append ("(" +mat_number.group (1)). Append (temp); this_city_num++; all_num_tele++;   if (all_num_tele<=208000 && all_num_tele% 2000==0) {Prestat.deletecharat (Prestat.length ()-1); Remove SThe comma Pst.addbatch (prestat.tostring ()) after the QL statement; Pst.executebatch (); Pst.clearbatch ();   Prestat.delete (Len1, Prestat.length ()); The condition SQL statement is followed by a free space} if (all_num_tele>208000)//After less than 2000 parts of the city join the batch without first executing {prestat.deletecharat (Prestat.len Gth ()-1); Pst.addbatch (Prestat.tostring ()); Prestat.delete (Len1, Prestat.length ()); } System.out.println ("Number of number segments inserted in the city is:" + this_city_num);} Regular matches public static Matcher get (string str_url, String pattern) throws Exception {string urlsource = get_html (str_ URL); Pattern P = pattern.compile (pattern); Matcher m = P.matcher (Urlsource); return m;}   Gets the Web content public static string get_html (String str_url) throws ioexception{url url = new URL (str_url); String content= "";                   StringBuffer page = new StringBuffer (); try {bufferedreader in = new BufferedReader (new InputStreamReader (URL . OpenStream ()); while (content = In.readline ()) = null) {page.append (content);}}       catch (IOException e) {e.printstacktrace ();}  return page.tostring (); }} 

Run

Test several times, the running time is about 2 minutes, and speed up a lot, but there is still a lot of room for improvement, because in the test, if the program is only inserted more than 200,000 of the SQL statement, can be completed in a few seconds.

Said again optimization, own idea is the website URL collection and inserts the database uses the multi-threading to carry on the concurrency operation, is now learning the Java Multi-threading, also attempts to use the multi-threading method to write the collection procedure, if everybody still has the better method, may also give me the message, is willing and everybody exchanges the progress.

Simple Java Capture Program two