继【简单的java采集程序】,这里将完成对整个网站的号码段的采集任务。
【使用预编译+批处理采集网页内容至数据库表中】
在之前我们用statement类来创建sql语句的执行对象,以实现插入字段到数据库的操作,但由于插入的数据量较大,如果继续用statement操作话,会很耗时间,我们用其子类PreparedStatement来进行操作。
PreparedStatement 可以实现sql语句的预编译,我们只需要通过其setString()方法传参即可,这样不仅效率提高了,而且也会更加安全,可防止SQL注入。推荐相关文章
另外我们还可以调用其addBatch()方法 和 executeBatch()实现批量插入处理。
代码如下,喜欢把数据库链接作为一个单独的类 import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
public class database {
public static String driver ="com.mysql.jdbc.Driver";
public static String url="jdbc:mysql://127.0.0.1:3306/tele_dat?autoReconnect=true&characterEncoding=UTF-8";
public static String user ="root";
public static String password = "123456";
public static java.sql.Connection conn = null;
//返回一个数据库连接对象
public static Connection ConnectToDataBase(){
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
System.out.println("加载驱动失败");
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(url, user, password);
System.out.println("连接成功");
} catch (SQLException e) {
System.out.println("连接出问题了");
e.printStackTrace();
}
return (Connection) conn;
}
//测试连接数据库
public static void main(String args[])
{
database.ConnectToDataBase();
}
}
主体程序
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"; //网站首页
static String pattern_pro_city = "
static String pattern_number = ">(13\\d{5}|15\\d{5}|18\\d{5}|147\\d{4})
static String pattern_pro ="\\w{3}\\.\\w{7}\\.\\w{3}\\/\\w{4}\\/\\w+"; //省份URL
static String pattern_city_hz="
"; //城市URL的后缀static PreparedStatement pst = null;
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();
System.out.println("------第"+num_pro+"个省-----");
String city_url_qz = "http://"+mat_home.group()+"/";
int len = city_url_qz.length();
StringBuffer city_ur = new StringBuffer();
Matcher mat_city_hz = get(city_url_qz,pattern_city_hz);
while(mat_city_hz.find()) //通过拼接获得 城市的完整url
System.out.println("第"+num_city+"个市");
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);
long end = System.currentTimeMillis();
long time = (end-start)/(1000*60);
System.out.println("查询到的电话号码段总数量:"+all_num_tele);
System.out.println("花费的时间是:"+time);
public static void One_City_Tele_to_DB(String url) throws Exception {
Matcher mat_pro_city = get(url,pattern_pro_city); //获取省名字 市名字
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("省份:"+pro+" "+"城市:"+city+" 正在插入号码段进数据库");
Matcher mat_number = get(url,pattern_number); //获取号码段
pst.setString(1,mat_number.group(1));
pst.executeBatch(); //每次批量插入一个城市的号码段
System.out.println("该市插入的号码段的数量是:"+ this_city_num);
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);
public static String get_Html(String str_url) throws IOException{
StringBuffer page = new StringBuffer();
BufferedReader in = new BufferedReader(new InputStreamReader(url
while((content = in.readLine()) != null){
显然,6分钟的速度还是太慢了,后面试了几次都是在6~8分钟之内(不过相对于不用批处理而直接用statement已经快很多了)。因此还得想办法优化。
【使用智能批量处理+StringBuilder的append()方法】
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.PreparedStatement;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
private static Connection conn = database.ConnectToDataBase();
static StringBuilder PreStat = new StringBuilder();
static String Qz = "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 = "
static String pattern_number = ">(13\\d{5}|15\\d{5}|18\\d{5}|147\\d{4})
static String pattern_pro ="\\w{3}\\.\\w{7}\\.\\w{3}\\/\\w{4}\\/\\w+"; //省份URL
static String pattern_city_hz="