jdbcTemplate是Spring框架中的一个数据库操作工具类，类位置位于org.springframework.jdbc.core。其中具体的对数据库的链接实现，会依据application.properties或者其他配置文件中，具体配置的数据库连接类型，自动判定选用哪个jdbc的驱动包来实现相关操作。而这些驱动的jar包，也是依据java.sql包中定的相关接口规范进行实现开发的。jdbcTemplate不过是调用了java.sql包各类接口的方法，从而有个面向开发人员的统一API而已。
jdbcTemplate插入数据库的方式有很多种，比如单条执行的jdbcTemplate.excute()、jdbcTemplate.excute()，批量执行的jdbcTemplate.batchUpdate()、jdbcTemplate.excuteBatch()等。具体所有的API说明可参考Spring的API文档：
最近在项目中，发现有块查询很慢，检查并测试后发现，主要是如下代码：
jdbcTemplate.batchUpdate(listsql.toArray(new String[listsql.size()]));这段代码的意思是，将包含SQL语句的的List转为Array后，进行批量插入。
在本地oracle下测试的结果却非常不好，结果如下：
INFO 2019-08-19 14:56:12 (EmpiPmiSerivceImpl.java:2171) - 数据总数：1720,耗时:19795
INFO 2019-08-19 14:56:38 (EmpiPmiSerivceImpl.java:2171) - 数据总数：1725,耗时:17961
INFO 2019-08-19 14:57:04 (EmpiPmiSerivceImpl.java:2171) - 数据总数：1730,耗时:17925
耗时单位是ms，基本上每条数据插入都在10~12ms的耗时左右。
总所周知，for循环单条插入sql的方式，在大数据量下的性能也是非常查的。本地oracle下测试结果如下：
INFO 2019-08-19 15:06:12 (EmpiPmiSerivceImpl.java:2173) - 数据总数:344，循环插入耗时:5126
INFO 2019-08-19 15:07:38 (EmpiPmiSerivceImpl.java:2173) - 数据总数:345，循环插入耗时:3844
INFO 2019-08-19 15:08:04 (EmpiPmiSerivceImpl.java:2173) - 数据总数:346,循环插入耗时:4837基本上每条数据插入都在15~18ms的耗时左右。
for循环单条插入sql的方式慢，主要是因为每次for对链接进行打开和关闭的时间消耗。jdbcTemplate的batchUpdate方法属于批量执行SQL，所以减少了这方面的损耗，效率还是有略微的提升的，但是10ms每条的效率还是不尽人意，上千条语句的执行将消耗10秒以上的等待时间。
在网上查询了一些关于该效率慢的说法，比如这篇：
https://blog.csdn.net/zhangyadick18/article/details/50294265
但是笔者在拜读了该篇博客后发现，rewriteBatchedStatements=true这条属性只对于mysql生效，其中的getRewriteBatchedStatements()方法也只在mysql的jdbc驱动包的PreparedStatement.java中能找到。
这篇博客有写到jdbcTemplate的另一种批量插入方式：
https://blog.csdn.net/qq_33269520/article/details/79727961
借鉴了该博客，笔者将上述批量插入方法改用如下模式实现：
jdbcTemplate.batchUpdate(sqlModel,new BatchPreparedStatementSetter() {
@Override
public int getBatchSize() {
return listDetail.size();
}
@Override
public void setValues(PreparedStatement ps, int i)throws SQLException {
ps.setLong (1, listDetail.get(i).getSuspectId ());
ps.setLong (2, listDetail.get(i).getFieldId ());
ps.setBigDecimal (3, listDetail.get(i).getScore ());
}
});测试后发现，其效率确实大大提高了接近100倍：
INFO 2019-08-19 15:47:22 (EmpiPmiSerivceImpl.java:2409) - 数据总数：1165,明细耗时:103
INFO 2019-08-19 15:47:42 (EmpiPmiSerivceImpl.java:2409) - 数据总数：1175,明细耗时:332
INFO 2019-08-19 15:47:45 (EmpiPmiSerivceImpl.java:2409) - 数据总数：1175,明细耗时:276平均每条数据的插入耗时只在0.1~0.2ms之间，确实快了不少。
但是两个同样都是jdbcTemplate.batchUpdate()方法，为何差距会如此大呢？
查看源码后，发现这两个jdbcTemplate.batchUpdate()方法在jdbcTemplate里面实际上是不同的方法实现。
第一个：
jdbcTemplate.batchUpdate(listsql.toArray(new String[listsql.size()]));其源码实现是：
@Override
public int[] batchUpdate(final String... sql) throws DataAccessException {
Assert.notEmpty(sql, "SQL array must not be empty");
if (logger.isDebugEnabled()) {
logger.debug("Executing SQL batch update of " + sql.length + " statements");
}
class BatchUpdateStatementCallback implements StatementCallback<int[]>, SqlProvider {
private String currSql;
@Override
public int[] doInStatement(Statement stmt) throws SQLException, DataAccessException {
int[] rowsAffected = new int[sql.length];
if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) {
for (String sqlStmt : sql) {
this.currSql = appendSql(this.currSql, sqlStmt);
stmt.addBatch(sqlStmt);
}
try {
rowsAffected = stmt.executeBatch();
}
catch (BatchUpdateException ex) {
String batchExceptionSql = null;
for (int i = 0; i < ex.getUpdateCounts().length; i++) {
if (ex.getUpdateCounts()[i] == Statement.EXECUTE_FAILED) {
batchExceptionSql = appendSql(batchExceptionSql, sql[i]);
}
}
if (StringUtils.hasLength(batchExceptionSql)) {
this.currSql = batchExceptionSql;
}
throw ex;
}
}
else {
for (int i = 0; i < sql.length; i++) {
this.currSql = sql[i];
if (!stmt.execute(sql[i])) {
rowsAffected[i] = stmt.getUpdateCount();
}
else {
throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql[i]);
}
}
}
return rowsAffected;
}
private String appendSql(String sql, String statement) {
return (StringUtils.isEmpty(sql) ? statement : sql + "; " + statement);
}
@Override
public String getSql() {
return this.currSql;
}
}而第二个jdbcTemplate.batchUpdate()方法，源码实现是这样的：
@Override
public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
if (logger.isDebugEnabled()) {
logger.debug("Executing SQL batch update [" + sql + "]");
}
return execute(sql, new PreparedStatementCallback<int[]>() {
@Override
public int[] doInPreparedStatement(PreparedStatement ps) throws SQLException {
try {
int batchSize = pss.getBatchSize();
InterruptibleBatchPreparedStatementSetter ipss =
(pss instanceof InterruptibleBatchPreparedStatementSetter ?
(InterruptibleBatchPreparedStatementSetter) pss : null);
if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
for (int i = 0; i < batchSize; i++) {
pss.setValues(ps, i);
if (ipss != null && ipss.isBatchExhausted(i)) {
break;
}
ps.addBatch();
}
return ps.executeBatch();
}
else {
List<Integer> rowsAffected = new ArrayList<Integer>();
for (int i = 0; i < batchSize; i++) {
pss.setValues(ps, i);
if (ipss != null && ipss.isBatchExhausted(i)) {
break;
}
rowsAffected.add(ps.executeUpdate());
}
int[] rowsAffectedArray = new int[rowsAffected.size()];
for (int i = 0; i < rowsAffectedArray.length; i++) {
rowsAffectedArray[i] = rowsAffected.get(i);
}
return rowsAffectedArray;
}
}
finally {
if (pss instanceof ParameterDisposer) {
((ParameterDisposer) pss).cleanupParameters();
}
}
}
});
}两段源码中，都有使用JdbcUtils.supportsBatchUpdates()方法去做数据库判定，是否支持批量执行，所以是不会走到else语句去循环调用executeUpdate()方法的。唯独的区别在于第一个用的是Statement 的executeBatch()方法，而第二个用的是PreparedStatement 的executeBatch()方法。那这两个的效率差别为什么会怎么大呢？
Statement.java和PreparedStatement.java实际上都是java.sql中的接口，所以和jdbcTemplate之间原本是没有什么关系的，只不过jdbcTemplate的两个batchUpdate()方法在实现上调用的是不同的接口。从源码上看，PreparedStatement是继承Statement的，但也依旧还是接口：
public interface PreparedStatement extends Statement {
//.......所以说，在 PreparedStatement.java和Statement.java中都没有对executeBatch()方法的具体实现，那么差别就应该在具体的实现类上。开篇之前说过，各数据库的驱动包--jdbc的jar包，均实现了java.sql中的接口。所以对PreparedStatement的接口实现和对Statement的接口实现差别，应该去看各jdbc的jar包中的实现差别。
这里分数据库解析一下，为了简单点，只展示必要的代码片段，而且这里都主要看 executeBatch方法。
oralce的实现：
- Statement的实现：OracleStatement
- PreparedStatement的实现：OraclePreparedStatement
OracleStatement中的executeBatch方法：
public int[] executeBatch() throws SQLException {
synchronized(this.connection) {
this.cleanOldTempLobs();
byte var2 = 0;
int var3 = this.getBatchSize(); //var3存储当前sql批次数量
if (var3 <= 0) {
return new int[0];
} else {
//......省略大量代码....
try {
BatchUpdateException var10;
try {
this.connection.registerHeartbeat();
this.connection.needLine();
//对当前批次进行循环
for(int var29 = 0; var29 < var3; ++var29) {
//......省略大量代码....
//连接数据库，如果没有连接则开始连接（不会每次循环都连一遍）
if (!this.isOpen) {
this.connection.open(this);
this.isOpen = true;
}
boolean var9 = true;
int var30;
try {
if (this.queryTimeout != 0) {
this.connection.getTimeout().setTimeout((long)(this.queryTimeout * 1000), this);
}
this.isExecuting = true;
this.executeForRows(false); //slq开始执行部分
//......省略大量代码....
} catch (SQLException var24) {
//......省略大量代码....
} finally {
//......省略大量代码....
}
//......省略大量代码....
}
} catch (SQLException var26) {
//......省略大量代码....
}
} finally {
//......省略大量代码....
}
//......省略大量代码....
}
}
}其中，var3存储的是当前sql批次数量，this.executeForRows(false)才是SQL的真正执行方法。循环体中，有对是否已连接的判定，如果已经连接就不会重新连接一遍。这也是为什么上面提到的比for循环单条插入sql的方式略快的原因。
但是，从这个代码结构上就可以发现，该循环体相当于对一个批次的sql语句进行循环执行操作，其涉及到对每个sql进行循环指定“执行计划”。不过具体什么是“执行计划”，笔者也不是很理解。可能就是下面要提到的executeForRowsWithTimeout方法这一块吧。
OraclePreparedStatement中的executeBatch方法：
public int[] executeBatch() throws SQLException {
synchronized(this.connection) {
int[] var2 = new int[this.currentRank];
int var3 = 0;
this.cleanOldTempLobs();
this.setJdbcBatchStyle();
if (this.currentRank > 0) {
//此处省略大量代码....
try {
this.connection.registerHeartbeat();
this.connection.needLine();
//数据库连接
if (!this.isOpen) {
this.connection.open(this);
this.isOpen = true;
}
int var5 = this.currentRank;
if (this.pushedBatches == null) {
this.setupBindBuffers(0, this.currentRank);
this.executeForRowsWithTimeout(false);
} else {
if (this.currentRank > this.firstRowInBatch) {
this.pushBatch(true);
}
boolean var18 = this.needToParse;
while(true) {
//var19是这个批次的变量集
OraclePreparedStatement.PushedBatch var19 = this.pushedBatches;
//给当前对象的全局变量赋值
this.currentBatchCharLens = var19.currentBatchCharLens;
this.lastBoundCharLens = var19.lastBoundCharLens;
this.lastBoundNeeded = var19.lastBoundNeeded;
this.currentBatchBindAccessors = var19.currentBatchBindAccessors;
this.needToParse = var19.need_to_parse;
this.currentBatchNeedToPrepareBinds = var19.current_batch_need_to_prepare_binds;
this.firstRowInBatch = var19.first_row_in_batch;
//变量绑定部分，非常重要！！！！
this.setupBindBuffers(var19.first_row_in_batch, var19.number_of_rows_to_be_bound);
this.currentRank = var19.number_of_rows_to_be_bound;
this.executeForRowsWithTimeout(false); //这里执行
var4 += this.validRows;
if (this.sqlKind == 32 || this.sqlKind == 64) {
var2[var3++] = this.validRows;
}
//如果不存在下个批次，则直接结束循环
this.pushedBatches = var19.next;
if (this.pushedBatches == null) {
this.pushedBatchesTail = null;
this.firstRowInBatch = 0;
this.needToParse = var18;
break;
}
}
}
this.slideDownCurrentRow(var5);
} catch (SQLException var13) {
//此处省略大量代码....
} finally {
//此处省略大量代码....
}
this.connection.registerHeartbeat();
return var2;
}
}数据库连接部分同理，不会多次连接。这里主要看while循环体内的部分，var19相当于这个sql批次的变量集，this.setupBindBuffers()方法会将sql和变量集进行绑定（也称为预编译），之后再调用executeForRowsWithTimeout(false)方法实现。executeForRowsWithTimeout(false)方法的源码如下：
void executeForRowsWithTimeout(boolean var1) throws SQLException {
if (this.queryTimeout > 0) {
try {
this.connection.getTimeout().setTimeout((long)(this.queryTimeout * 1000), this);
this.isExecuting = true;
this.executeForRows(var1);
} finally {
this.connection.getTimeout().cancelTimeout();
this.isExecuting = false;
}
} else {
try {
this.isExecuting = true;
this.executeForRows(var1);
} finally {
this.isExecuting = false;
}
}
}
其中也只是做了连接超时的设置，并最终调用executeForRows()方法执行，这点和OracleStatement中的executeBatch方法中，循环体内部如下代码是一致的。
try {
if (this.queryTimeout != 0) {
this.connection.getTimeout().setTimeout((long)(this.queryTimeout * 1000), this);
}
this.isExecuting = true;
this.executeForRows(false);
//.......但是，由于executeForRowsWithTimeout中也并没有循环调用executeForRows()方法的部分，而上述的while循环，如果是一个批次的sql，只会执行一次。所以从整体结构上看，OraclePreparedStatement中的executeBatch方法，只会调用一次executeForRows()方法。
由此可见，executeForRows()方法执行的次数对效率的影响会很大，具体为什么，笔者也不是很理解，包括setupBindBuffers()方法是如何实现变量绑定和预编译的，源码也没怎么看懂。如果有大神能够帮忙解释下可以在下面留言下。
这里有一篇对oracle这块解释更详细的博客，但也没有从源码层面上解释具体实现的原理。。
https://blog.csdn.net/qpzkobe/article/details/79283709
不过该博客也提及了一点：使用PreparedStatement接口的实现类可以防止SQL注入攻击的问题，所以总体上而言，用PreparedStatement接口代替Statement接口开始很值得的。
sqlserver中的实现：
- Statement的实现：SQLServerStatement
- PreparedStatement的实现：SQLServerPreparedStatement
SQLServerStatement中的executeBatch方法：
public int[] executeBatch() throws SQLServerException, BatchUpdateException {
loggerExternal.entering(this.getClassNameLogging(), "executeBatch");
//此处省略一堆代码....
int[] var12;
try {
//var1记录的是整个批次的sql数量
int var1 = this.batchStatementBuffer.size();
//此处省略一堆代码....
for(int var4 = 0; var4 < var1; ++var4) {
try {
if (0 == var4) {
//只有首次才开始执行
this.executeStatement(new SQLServerStatement.StmtBatchExecCmd(this));
} else {
//设置返回结果标志
this.startResults();
if (!this.getNextResult()) {
break;
}
}
//此处省略一堆代码....
} catch (SQLServerException var9) {
//此处省略一堆代码....
}
}
//此处省略一堆代码....
} finally {
//此处省略一堆代码....
}
return var12;
}而SQLServerPreparedStatement的executeBatch方法：
public int[] executeBatch() throws SQLServerException, BatchUpdateException {
loggerExternal.entering(this.getClassNameLogging(), "executeBatch");
//此处省略大量代码.....
if (this.batchParamValues == null) {
var1 = new int[0];
} else {
try {
//值批次校验
for(int var2 = 0; var2 < this.batchParamValues.size(); ++var2) {
Parameter[] var3 = (Parameter[])this.batchParamValues.get(var2);
for(int var4 = 0; var4 < var3.length; ++var4) {
if (var3[var4].isOutput()) {
throw new BatchUpdateException(SQLServerException.getErrString("R_outParamsNotPermittedinBatch"), (String)null, 0, (int[])null);
}
}
}
SQLServerPreparedStatement.PrepStmtBatchExecCmd var8 = new SQLServerPreparedStatement.PrepStmtBatchExecCmd(this);
//sql执行
this.executeStatement(var8);
if (null != var8.batchException) {
throw new BatchUpdateException(var8.batchException.getMessage(), var8.batchException.getSQLState(), var8.batchException.getErrorCode(), var8.updateCounts);
}
var1 = var8.updateCounts;
} finally {
this.batchParamValues = null;
}
}
loggerExternal.exiting(this.getClassNameLogging(), "executeBatch", var1);
return var1;
}从循环的角度看，二者都只有一次循环，这点和oracle并不同。所以从测试结果上看，SQLServerStatement的executeBatch还是比oracle的OracleStatement中的executeBatch要快了不少，平均时长为每条0.5~1ms之间。
INFO 2019-08-20 14:38:44 (EmpiPmiSerivceImpl.java:2375) - 数据总数：606,耗时:324
INFO 2019-08-20 14:38:46 (EmpiPmiSerivceImpl.java:2375) - 数据总数：618,耗时:643当时相比于SQLServerPreparedStatement的executeBatch方法，效率还是略低的。SQLServerPreparedStatement的executeBatch方法测试结果如下，平均时长为每条0.1ms左右。
INFO 2019-08-19 17:40:37 (EmpiPmiSerivceImpl.java:2378) - 数据总数：558,明细耗时:62
INFO 2019-08-19 17:40:17 (EmpiPmiSerivceImpl.java:2378) - 数据总数：372,明细耗时:33
主要区别还是在于，SQLServerStatement中的executeBatch方法没有做预编译，其executeStatement方法的入参--StmtBatchExecCmd对象，对sql的执行方法--doExecuteStatementBatch()也是将sql循环写入的方法。中间的while循环实际上是对SQL的拼接。
//SQLServerStatement:
private final void doExecuteStatementBatch(SQLServerStatement.StmtBatchExecCmd var1) throws SQLServerException {
this.resetForReexecute();
this.connection.setMaxRows(0);
if (loggerExternal.isLoggable(Level.FINER) && Util.IsActivityTraceOn()) {
loggerExternal.finer(this.toString() + " ActivityId: " + ActivityCorrelator.getNext().toString());
}
this.executeMethod = 4;
this.executedSqlDirectly = true;
this.expectCursorOutParams = false;
TDSWriter var2 = var1.startRequest((byte)1);
ListIterator var3 = this.batchStatementBuffer.listIterator();
var2.writeString((String)var3.next());
//拼接SQL
while(var3.hasNext()) {
var2.writeString(" ; ");
var2.writeString((String)var3.next());
}
this.ensureExecuteResultsReader(var1.startResponse(this.isResponseBufferingAdaptive));
this.startResults();
this.getNextResult();
if (null != this.resultSet) {
SQLServerException.makeFromDriverError(this.connection, this, SQLServerException.getErrString("R_resultsetGeneratedForUpdate"), (String)null, false);
}
}SQLServerPreparedStatement中，executeStatement方法的入参是PrepStmtBatchExecCmd对象，对sql的执行方法--doExecutePreparedStatementBatch()中调用了预编译处理的方法：
do {
if (var4 >= var2) {
return;
}
Parameter[] var7 = (Parameter[])this.batchParamValues.get(var3);
assert var7.length == var5.length;
for(int var8 = 0; var8 < var7.length; ++var8) {
var5[var8] = var7[var8];
}
if (var4 < var3) {
var6.writeByte((byte)-1);
} else {
this.resetForReexecute();
var6 = var1.startRequest((byte)3);
}
++var3;
} while(!this.doPrepExec(var6, var5) && var3 != var2);其中最后一行，while括号中的doPrepExec方法是预编译的实现，具体细节有很多层方法的调用，这里就不细说了（其实我也还没弄明白。。）。
Mysql和sqlsever类似，唯一不同的是mysql要通过PreparedStatement接口支持批量查询，需要在URL加入rewriteBatchedStatements=true这条属性才能生效。
另外，无论mysql还是sqlserver，都可通过用分号“;”隔开的方式，拼接SQL语句，最后再通过jdbcTemplate.excute()方法传入并一次执行。但是oracle不可以，会报错ORA-00911: 无效字符，原因是oracle 的jdbc不允许传入分号！！
总而言之，无论是oracle还是sqlserver，对PreparedStatement接口的实现都是有预编译处理的。所谓的预编译大致是这么个意思：
预编译的语句，就会放在Cache中，下次执行相同的SQL语句时，则可以直接从Cache中取出来。
不同数据的jdbc对预编译的实现都不同，但主要思想还是：语句缓存。比如：
select colume from table where colume=1;
select colume from table where colume=2;
语句缓存的话，select colume from table where colume=？部分就能缓存下来，从而减少SQL的构建成本，提高效率。
另外，使用PreparedStatement接口的sql执行方法还有如下好处：
1、提高了代码的灵活性。以参数传入的方式，比拼接SQL要好的多，而且大量参数时不易出错：
拼接SQL：
String sql = "select * from users where username= '"+username+"' and userpwd='"+userpwd+"'";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);参数传入：
String sql = "select * from users where username=? and userpwd=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, userpwd);
rs = pstmt.executeQuery();2、比Statement安全，PreparedStatement预编译时会对参数做处理，能防止SQL注入。
如：前端输入password为：'or '1' = 1'，上述Statement拼接的SQL就会变成：
String sql = "select * from user where username = 'user' and userpwd = '' or '1' = '1';";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);怎么查都会验证通过。
3、批量查询效率更高。原因上面已经分析过了，这里就不再重述。
当然，Statement在执行一次查询并返回结果的情形，效率还是高于PreparedStatement，毕竟少了预编译的额外处理，但是，依旧无法防止SQL注入问题。
最后回到主题，jdbcTemplate的所有API方法中，具体选择哪个还是非常重要的，批量处理时，一定要选择调用PreparedStatement接口进行SQL执行的方法，才能保证效率和安全性！
2156
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
