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执行的方法,才能保证效率和安全性!