我们可以看到 Statement 和 PreparedStatement 为我们提供的批次执行 sql 操作
JDBC 引入上述 batch 功能的主要目的，是加快对客户端SQL的执行和响应速度，并进而提高数据库整体并发度，而 jdbc batch 能够提高对客户端SQL的执行和响应速度,其主要原理有：
减少了JDBC客户端和数据库服务器之间网络传输的开销：使用 batch 功能前，每提交一个SQL，都需要一次网络IO开销，且提交后需要等待服务端返回结果后，才能提交下一个SQL；而使用 batch 功能后，客户端的多个SQL是一起提交给服务器的，只涉及到一次网络IO开销（single database round trip），其示意图如下：
当batch底层使用的是静态SQL并参数化执行时(JAVA中一般是使用类java.sql.PreparedStatement 来参数化执行静态SQL)，数据库服务器可以只做一次解析：利用对参数化机制的支持，数据库服务器仅需要对 PreparedStatement 做一次解析（sql parse），即可传入不同参数执行该 batch 中所有的 SQL;
网上有个帖子，详细对比了不同场景下，不同数据库的插入和更新性能的差异，可以看出，ORACLE/PG/MYSQL 使用 batch 功能后，性能都有了3-5被的提高：
现在大家用的都是 PreparedStatement 了
对于 select update insert delete 来说、select 是用不上这个批次方法的
我们看到 executeBatch 返回的是 int[] 类型
不同 db 可能返回的值或者逻辑是不一样的
关于 rewriteBatchedStatements 参数
该参数是 mysql 专属的
https://stackoverflow.com/questions/2993251/jdbc-batch-insert-performance
里面有说到关于如何提高 mysql 批量 insert 的效率
useServerPrepStmts 是作用于 mysql 服务端的
rewriteBatchedStatements 是作用于mysql 客户端的、也就是我们的应用程序
那么它发挥了什么的作用？
该参数的意思就是重写用户的 sql 已提高批量 update 的速率。
可以发现是将批量执⾏的⼀组 sql Insert 语句,改写为一条 batched 语句 insert into tableA (colA,colB) values (colA-value1,colB-value1),(colA-value2,colB-value2),(colA-value3,colB-value3), 并通过一次请求发送给数据库服务器的，也就是说此时 mysql 使用了批量插入功能；
对于批量
对于增删改
- batchInsert(10 records) 会被改写为 "insert into t (…) values (…), (…), (…)” 并一次性提交；
如果不能被改写为 “multi-values”, 则会改写为多个;分割的sql语句并一次性提交：语句 “INSERT INTO TABLE(col1) VALUES (?) ON DUPLICATE KEY UPDATE col2=?” 与变量 [1,2] 和 [2,3]，会被改写为 “INSERT INTO TABLE(col1) VALUES (1) ON DUPLICATE KEY UPDATE col2=2;INSERT INTO TABLE(col1) VALUES (3) ON DUPLICATE KEY UPDATE col2=4” 并一次性提交 - batchDelete(10 records) 会被改写为 "delete from t where id = 1; delete from t where id = 2; delete from t where id = 3;…."并一次性提交；
- batchUpdate(10 records) 会被改写为 “update t set… where id = 1; update t set… where id = 2; update t set… where id = 3…” 并一次性提交；
对于其他数据库比如 oracle 来说、默认是开启了这种批量优化的。比如现在 insert (1,1)(2,2)(3,3)… 到数据
源码
@Override
protected long[] executeBatchInternal() throws SQLException {
synchronized (checkClosed().getConnectionMutex()) {
if (this.connection.isReadOnly()) {
throw new SQLException(Messages.getString("PreparedStatement.25") + Messages.getString("PreparedStatement.26"),
MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT);
}
if (this.query.getBatchedArgs() == null || this.query.getBatchedArgs().size() == 0) {
return new long[0];
}
// we timeout the entire batch, not individual statements
int batchTimeout = getTimeoutInMillis();
setTimeoutInMillis(0);
resetCancelledState();
try {
statementBegins();
clearWarnings();
if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) {
if (getQueryInfo().isRewritableWithMultiValuesClause()) {
return executeBatchWithMultiValuesClause(batchTimeout);
}
if (!this.batchHasPlainStatements && this.query.getBatchedArgs() != null
&& this.query.getBatchedArgs().size() > 3 /* cost of option setting rt-wise */) {
return executePreparedBatchAsMultiStatement(batchTimeout);
}
}
return executeBatchSerially(batchTimeout);
} finally {
this.query.getStatementExecuting().set(false);
clearBatch();
}
}
}
看到 executeBatchSerially 方法
for (batchCommandIndex = 0; batchCommandIndex < nbrCommands; batchCommandIndex++) {
((PreparedQuery<?>) this.query).setBatchCommandIndex(batchCommandIndex);
Object arg = this.query.getBatchedArgs().get(batchCommandIndex);
try {
if (arg instanceof String) {
updateCounts[batchCommandIndex] = executeUpdateInternal((String) arg, true, this.retrieveGeneratedKeys);
// limit one generated key per OnDuplicateKey statement
getBatchedGeneratedKeys(this.results.getFirstCharOfQuery() == 'I' && containsOnDuplicateKeyInString((String) arg) ? 1 : 0);
} else {
QueryBindings<?> queryBindings = (QueryBindings<?>) arg;
updateCounts[batchCommandIndex] = executeUpdateInternal(queryBindings, true);
// limit one generated key per OnDuplicateKey statement
getBatchedGeneratedKeys(containsOnDuplicateKeyUpdateInSQL() ? 1 : 0);
}
} catch (SQLException ex) {
updateCounts[batchCommandIndex] = EXECUTE_FAILED;
if (this.continueBatchOnError && !(ex instanceof MySQLTimeoutException) && !(ex instanceof MySQLStatementCancelledException)
&& !hasDeadlockOrTimeoutRolledBackTx(ex)) {
sqlEx = ex;
} else {
long[] newUpdateCounts = new long[batchCommandIndex];
System.arraycopy(updateCounts, 0, newUpdateCounts, 0, batchCommandIndex);
throw SQLError.createBatchUpdateException(ex, newUpdateCounts, this.exceptionInterceptor);
}
}
}
确实是一条条插入到数据库中的
但是我们认真看看、调用这个函数之前、还有其他出口
if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) {
if (getQueryInfo().isRewritableWithMultiValuesClause()) {
return executeBatchWithMultiValuesClause(batchTimeout);
}
if (!this.batchHasPlainStatements && this.query.getBatchedArgs() != null
&& this.query.getBatchedArgs().size() > 3 /* cost of option setting rt-wise */) {
return executePreparedBatchAsMultiStatement(batchTimeout);
}
}
this.rewriteBatchedStatements.getValue() 这个我们说过的参数
batchHasPlainStatements 默认就是 false 不用管
executeBatchWithMultiValuesClause 做的就是 insert 的时候多 values
executePreparedBatchAsMultiStatement 做的就是 ; 分割的多个 sql 语句、比如 delete 、update 或者是 insert 无法多个 valuse 的
Mysql 这里还涉及到另外的一个参数 max_allowed_packet 代表一次的网络包最大是多少
看到在 mysql 8.0 这边的大小是 32M 左右
Spring JdbcTemplate
直接 JDBC
//获取要设置的Arp基准的List后,插入Arp基准表中
public boolean insertArpStandardList(List<ArpTable> list) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
//MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。
//优化插入性能，用JDBC的addBatch方法，但是注意在连接字符串加上面写的参数。
//例如： String connectionUrl="jdbc:mysql://192.168.1.100:3306/test?rewriteBatchedStatements=true" ;
String sql = "insert into arp_standard(guid, devicebrand, devicename, deviceip, ipaddress, " +
"macaddress, createtime) values(?,?,?,?,?,?,?)";
try{
conn = DBConnection.getConnection();
ps = conn.prepareStatement(sql);
//优化插入第一步设置手动提交
conn.setAutoCommit(false);
int len = list.size();
for(int i=0; i<len; i++) {
ps.setString(1, list.get(i).getGuid());
ps.setString(2, list.get(i).getDeviceBrand());
ps.setString(3, list.get(i).getDeviceName());
ps.setString(4, list.get(i).getDeviceIp());
ps.setString(5, list.get(i).getIpAddress());
ps.setString(6, list.get(i).getMacAddress());
ps.setString(7, list.get(i).getCreateTime());
//if(ps.executeUpdate() != 1) r = false; 优化后，不用传统的插入方法了。
//优化插入第二步插入代码打包，等一定量后再一起插入。
ps.addBatch();
//if(ps.executeUpdate() != 1)result = false;
//每200次提交一次
if((i!=0 && i%200==0) || i==len-1){//可以设置不同的大小；如50，100，200，500，1000等等
ps.executeBatch();
//优化插入第三步提交，批量插入数据库中。
conn.commit();
ps.clearBatch();//提交后，Batch清空。
}
}
} catch (Exception e) {
System.out.println("MibTaskPack->getArpInfoList() error:" + e.getMessage());
return false; //出错才报false
} finally {
DBConnection.closeConection(conn, ps, rs);
}
return true;
}
https://mdnice.com/writing/fc6d8a16525d447bbcae4c5be34215a0
https://stackoverflow.com/questions/26307760/mysql-and-jdbc-with-rewritebatchedstatements-true
https://letcoding.com/2024/01/03/%E5%A5%BD%E5%A5%BD%E7%A0%81%E4%BB%A3%E7%A0%81%E5%90%96/JAVA/MyBatis/JDBC%E8%BF%9E%E6%8E%A5%E5%8F%82%E6%95%B0%E3%80%90rewriteBatchedStatements%E3%80%91%E8%AF%A6%E8%A7%A3/
https://www.cnblogs.com/lizm166/p/7890168.html