今天,对mysql数据库的插入数据的速度做了个简单的测试。
JdbcUtil.java
packageutils;
importjava.sql.*;
publicclassJdbcUtil {
privatefinalstaticString DB_DRIVER ="com.mysql.jdbc.Driver";
privatefinalstaticString DB_CONNECTION ="jdbc:mysql://localhost:3306/";
privatefinalstaticString DB_NAME ="root";
privatefinalstaticString DB_PWd ="root";
static{
try{
Class.forName(DB_DRIVER);
} catch(ClassNotFoundException e) {
e.printStackTrace();
}
}
publicstaticConnection getConnection() {
Connection conn = null;
try{
conn = DriverManager
.getConnection(DB_CONNECTION,
DB_NAME, DB_PWd);
} catch(SQLException e) {
e.printStackTrace();
}
returnconn;
}
publicstaticStatement getStatement(Connection conn) {
Statement stmt = null;
try{
stmt = conn.createStatement();
} catch(SQLException e) {
e.printStackTrace();
}
returnstmt;
}
publicstaticPreparedStatement getPreparedStatement(Connection conn, String sql) {
PreparedStatement pstmt = null;
try{
pstmt = conn.prepareStatement(sql);
} catch(SQLException e) {
e.printStackTrace();
}
returnpstmt;
}
publicstaticResultSet getRs(Statement stmt, String sql) {
ResultSet rs = null;
try{
rs = stmt.executeQuery(sql);
} catch(SQLException e) {
e.printStackTrace();
}
returnrs;
}
publicstaticvoidclose(Statement stmt) {
if(stmt !=null) {
try{
stmt.close();
} catch(SQLException e) {
e.printStackTrace();
}
}
}
publicstaticvoidclose(ResultSet rs) {
if(rs !=null) {
try{
rs.close();
} catch(SQLException e) {
e.printStackTrace();
}
}
}
publicstaticvoidclose(Connection conn) {
if(conn !=null) {
try{
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
}
}
}
SqlTest.java
packagetest;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.SQLException;
importjava.sql.Statement;
importutils.JdbcUtil;
publicclassSqlTest {
privatefinalString DBNAME ="test";
privatefinalString TABLENAME ="mytest";
publicvoidcreateTable(){
//String sql_select_db = "use "+ DBNAME;
String sql_drop = "drop table if exists "+ DBNAME +"."+ TABLENAME;
String sql_create = "create table "+ DBNAME +"."+ TABLENAME+"(id varchar(10) primary key,name varchar(10))";
Connection conn = JdbcUtil.getConnection();
Statement stmt = JdbcUtil.getStatement(conn);
try{
//System.out.println(stmt.execute(sql_select_db));
stmt.execute(sql_drop);
stmt.execute(sql_create);
} catch(SQLException e) {
e.printStackTrace();
}finally{
JdbcUtil.close(conn);
JdbcUtil.close(stmt);
}
}
publicvoidadd(){
String sql_add = "insert into "+ DBNAME +"."+ TABLENAME +" values(?,?)";
Connection conn = JdbcUtil.getConnection();
PreparedStatement pstmt = JdbcUtil.getPreparedStatement(conn, sql_add);
try{
pstmt.setString(1,"1006");
pstmt.setString(2,"q1006");
pstmt.executeUpdate();
} catch(SQLException e) {
e.printStackTrace();
}finally{
JdbcUtil.close(conn);
JdbcUtil.close(pstmt);
}
}
publicvoidaddBatchTest(){
String sql_add = "insert into "+ DBNAME +"."+TABLENAME +" values(?,?)";
Connection conn = JdbcUtil.getConnection();
PreparedStatement pstmt = JdbcUtil.getPreparedStatement(conn, sql_add);
longstartTime = System.currentTimeMillis();
try{
intcount =0;
for(inti =0; i <100000; i++){
pstmt.setString(1, i+"");
pstmt.setString(2,"q"+ i);
conn.setAutoCommit(false);//若改为true 插入中遇到主键冲突还会继续插入,具体看需求——???是这样吗?
pstmt.addBatch();
count ++;
if(count >=10000){
count = 0;
pstmt.executeBatch();
conn.commit();
}
}
pstmt.executeBatch();
conn.commit();
System.out.println( (System.currentTimeMillis() - startTime) + "ms");
} catch(SQLException e) {
e.printStackTrace();
}finally{
JdbcUtil.close(conn);
JdbcUtil.close(pstmt);
}
}
/**
* @param args
*/
publicstaticvoidmain(String[] args) {
SqlTest sqlTest = newSqlTest();
sqlTest.createTable();
//sqlTest.add();
sqlTest.addBatchTest();
}
}
背景:
1、mysql数据库
2、表结构为
id varchar(10)
name varchar(10)
3、采用非自动提交的PrepareStatement批处理
测试结果:
数据量(条) 插入所需时间(ms)
1 0
10 15
100 62
1,000 422
10,000 2,922
100,000 26,922
1000,000 272,219
测试过程学习到的:
1、批处理要conn.setAutoCommit(false)(默认会自动提交,不能达到批处理的目的,速度极慢!)。
2、pstmt.executeBatch();
conn.commit();
要提交,数据库才会有数据。
3、避免内存溢出,应每x(如:万)条提交一次数据。
4、可用“数据库名.表名”的方法来访问数据库表
这样,则在写JdbcUtil的DB_CONNECTION = "jdbc:mysql://localhost:3306/"时,
可不具体到数据库,方便跨数据库的数据操作。
5、Statement和PrepareStatement不仅可以操作DDL,添加删除表和数据库的sql都可以操作,用execute(String sql)方法。