使用hibernate框架时,通常数据库的交互离不开框架的缓存机制。如果因业务需求,需要批量执行更新语句等情况也是难免的,但是直接多次执行更新语句明显性能会下降了许多。因此,可以采用jdbc的批量处理sql。原来程序使用c3p0线程池,换成hikariCP后,批量执行sql一小段时间后,会陷入疑似阻塞的状态,也就是说整个程序不再执行任务了。
经排查,是数据库连接池没有回收,最终导致无可用资源导致的。
下面贴出我的修改部分,希望能帮到有缘人....
public void updateSqlList(List<String> updateSqlList) throws Exception {
Session session = this.getSessionFactory().openSession();
Connection connection = session.connection();
Statement statement = null;
try {
connection.setAutoCommit(false);
statement = connection.createStatement();
int updateSqlListSize = updateSqlList.size();
long startTimeTotal = System.currentTimeMillis();
for (int i = 0; i < updateSqlListSize; i++) {
statement.addBatch(updateSqlList.get(i));
if (i % 1000 == 0 && i != 0) {
long startTime = System.currentTimeMillis();
int[] count = new int[0];
try {
count = statement.executeBatch();
} catch (SQLException e) {
if(null != count){
for (int j = 0, countSize=count.length; j < countSize; j++) {
int i1 = count[j];
if(Statement.EXECUTE_FAILED == count[j]){
System.out.println("语句有误:" + updateSqlList.get(j));
}
}
}
logger.error("语句有误", e);
throw e;
}
connection.commit();
statement.clearBatch();
}
}
statement.executeBatch();
connection.commit();
connection.setAutoCommit(true);
} catch (Exception e) {
logger.error("批量执行语句失败!", e);
try {
connection.rollback();
} catch (Exception e1) {
logger.error("回滚批量执行语句失败!", e1);
}
throw new Exception("系统异常,批量执行语句失败!");
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException sqlex) {
logger.error("无法关闭数据库连接!", sqlex);
}
}
if (connection != null) {
try {
connection.setAutoCommit(true);
connection.close();
logger.info("关闭数据库连接成功...");
} catch (SQLException sqlex) {
logger.error("无法关闭数据库连接!", sqlex);
}
}
if(null != session){
session.close();
}
}
}