package com.listings.web.controller;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.util.EntityUtils;
public class TestListingsController {
private static String IP = "localhost";
private static String PORT = "8080";
public static final String DBDRIVER = "com.mysql.jdbc.Driver";
// 连接地址是由各个数据库生产商单独提供的,所以需要单独记住
public static final String DBURL = "jdbc:mysql://192.168.2.23:3306/mfwang";
// 连接数据库的用户名
public static final String DBUSER = "root";
// 连接数据库的密码
public static final String DBPASS = "1q2w3e4r5t";
@Test
public void testImport() throws ClassNotFoundException, SQLException{
Connection con = null; //表示数据库的连接对象
Class.forName(DBDRIVER); //1、使用CLASS 类加载驱动程序
con = DriverManager.getConnection(DBURL,DBUSER,DBPASS); //2、连接数据库
ResultSet rs = null;
PreparedStatement st = null;
String sql = "insert into user(username,password,email) values(?,?,?)";
st = con.prepareStatement(sql);//预处理sql语句;
for(int i=0;i<50000;i++){
st.setString(1, "aaa" + i);
st.setString(2, "123" + i);
st.setString(3, "aaa" + i + "@sina.com");
st.addBatch();//将一组参数添加到此 PreparedStatement 对象的批处理命令中。
if(i%1000==0){
st.executeBatch();
st.clearBatch();
}
}
//循环输出结果
con.close(); // 3、关闭数据库
}
}
批处理速度和插入数据的字段数有关,例子中user为3字段,大约3分钟插入5W条数据,实际中处理过一条数据50个字段的,大约处理速度为1分钟2000条