JDBC 一次批量插入几十万条模拟数据

最近一个项目需要对大量的数据进行汇总,期间还涉及到定时对冗余记录的去重。因为现阶段不能直接操作生厂环境不可能有这如此大量的真实数据供测试期间使用。因为就需要自己在数据库中插入50万+ 条记录。在程序中采用 while 循环 INSERT 插入1000 条 要执行近 50 秒,如插入50万条 不敢想向耗时。 这里就有必要采用更高效的办法 。

public static void main(String[] args) {

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    System.out.println( sdf.format( new Date() ));
    long begin = System.currentTimeMillis();
    //testMInsert();
    testBatch();
    long end = System.currentTimeMillis();
    System.out.println( sdf.format( new Date() ));
    System.out.println( "程序运行时间为:"+ ( end - begin ) );

}
public static int testBatch(){

    DataBase dataBase =  DataBase.getInstance();
    int row = 0;
    PreparedStatement  pStmt = null;
    Connection conn = dataBase.conn;

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    //Date now = new Date();
    Calendar nowTime = Calendar.getInstance();

    try{

        conn.setAutoCommit(false); // 设置手动提交
        String sql = "INSERT IGNORE test ( ADDDATE,MAC,DataVar,UP ) VALUE ( ?,?,?,? )";
        pStmt = conn.prepareStatement(sql ,ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY );
        ///Random random = new Random();
        int count = 0;
        final int batchSize = 5000;
        for( int i = 0; i < 500000; i++){

            nowTime.add( Calendar.MINUTE, 5 );
            String ADDDATE = sdf.format( nowTime.getTime()  );
            String MAC = "00:00:00:00:00:00";
            int  DataVar = 10;
            int  UP = 0;

            pStmt.setString( 1 , ADDDATE );
            pStmt.setString( 2 , MAC );
            pStmt.setInt( 3, DataVar );
            pStmt.setInt( 4, UP  );

            pStmt.addBatch();

            if( ++count % batchSize == 0 ) {
                pStmt.executeBatch();
                conn.commit();
            }
        }
        pStmt.executeBatch();
        //row = rows.length;
        conn.commit();  // 提交
    }catch(Exception e){

        e.printStackTrace();

    }finally{
        try{
            if( pStmt != null)
                pStmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        try{
            if( conn != null)
                conn.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    return 0;
}


在我测试机上,执行50万条的插入约 < 50秒 ; 插入1000 条约为 600 毫秒

批量任务执行的效率非常明显:


关键提示自己再次注意:

conn.setAutoCommit(false); // 设置手动提交

。。。。

。。

conn.commit(); 


JDBC 连接对象的属性设置,一定不要生用自动提交,而使用手动提交;

另外,为了防止内存不足,可以设定一个峰值如 2000 条,提交一次;因为把50万条记录都压一个队列中是非常占用内存的。


// 循环外部设置峰值
int count = 0;
final int batchSize = 5000;

// 循环最后做一次判断
//{
   if( ++count % batchSize == 0 ) {
        pStmt.executeBatch();
        conn.commit();
   }
//}


创建 JDBC 连接时的优化设置,关键:

useServerPrepStmts = false

rewriteBatchedStatements = true

Class.forName("org.gjt.mm.mysql.Driver").newInstance();
String url ="jdbc:mysql://"+ this.HostName +":" + this.port  +"/"+ this.DBName + 
"?user="+
this.User +
"&password="+
this.Password +
"&useUnicode=true&characterEncoding=UTF-8";
url += "&useServerPrepStmts=false&rewriteBatchedStatements=true";