6~7秒左右
package com.wang.test.insertmysql;
import java.sql.*;
/**
* @author Spider Man
* @date 2023-03-28 09:35
*/
public class Test02 {
public static void main(String[] args) throws SQLException {
long start = System.currentTimeMillis();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mytest?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true", "root", "root");
这里需要注意,SQL语句的格式必须是预处理的这种,就是values(?,?,...,?),否则批处理不起作用
String sql = "insert into blog(id,title,author,views) values(?,?,?,?)";
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
conn.setAutoCommit(false);//取消自动提交
for (int i = 1; i <= 1000000; i++) {
ps.setInt(1, i+1);
ps.setString(2, "标题"+i);
ps.setString(3, "某作者"+i);
//statement.setDate(4,new Date(System.currentTimeMillis()));
ps.setInt(4, i);
ps.addBatch();
if (i % 500 == 0) {
ps.executeBatch();
ps.clearBatch();
}
}
ps.executeBatch();
ps.clearBatch();
conn.commit();//所有语句都执行完毕后才手动提交sql语句
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭相关连接
ps.close();
conn.close();
}
System.out.println("百万条数据插入用时:" + (System.currentTimeMillis() - start)/1000+"秒");
}
}