java – 如何将百万数据插入数据库

这基本上为您提供了sqlloader的速度,它可以进行批量插入.并且只应使用1个线程.

我在这里做的是将插入放入一个循环中,表明你必须每隔几千条记录清除批次.

您将删除无限循环并让它插入数据而不是硬编码的mkyong数据

String insertTableSQL = "INSERT INTO DBUSER"
        + "(USER_ID,USERNAME,CREATED_BY,CREATED_DATE) VALUES"
        + "(?,?,?)";

PreparedStatement preparedStatement = dbConnection.prepareStatement(insertTableSQL);
try {
    dbConnection.setAutoCommit(false);

    int batchTotal=0;
    for  (;;) { // infinate loop? change this to get your data here
        preparedStatement.setInt(1,101);
        preparedStatement.setString(2,"mkyong101");
        preparedStatement.setString(3,"system");
        preparedStatement.setTimestamp(4,getCurrentTimeStamp());
        preparedStatement.addBatch();
        if (batchTotal++ == 4096) {
            int[] result = preparedStatement.executeBatch();
            preparedStatement.clearBatch();
            batchTotal=0;                    
        }
    }
    if (batchTotal > 0) {
        int[] result = preparedStatement.executeBatch();
    }

    dbConnection.commit();
}  finally {
    preparedStatement.close();
}