对spring JdbcTemplate的一个扩展(使其支持单Connection)

对spring JdbcTemplate的一个扩展(使其支持单Connection).

不怕大家笑话,以前一直没怎么使用过spring jdbc template,
印象中只用过
public List queryForList(String sql, Object[] args)
public Map queryForMap(String sql, Object[] args)
和SqlFunction

在orm大行其道,spring诞生快一个实际的今天,再来探讨jdbc的一些封装实在不知道有没有意义.
不过还是想把刚刚弄出来的一点东西和大家分享.

看了一下 JdbcTemplate,
发现起核心是那几个 execute 方法.
而那几个execute方法的机构大概是这样(重点讨论Connection,所以其他地方从简)

[code]
execute方法开始

Connection con = DataSourceUtils.getConnection(getDataSource());

// 做一些数据库操作

DataSourceUtils.releaseConnection(con, getDataSource());

execute方法结束
[/code]

当你要批量执行一些操作时(不是 每个操作使用不同的sql,或者是其他不适合 addBatch的情形).

如下:
[code]
JdbcTemplate jdbcTemplate=new JdbcTemplate(ds);
jdbcTemplate.query(sql_1, args_1,rch_1);
jdbcTemplate.query(sql_2, args_2,rch_2);
jdbcTemplate.query(sql_3, args_3,rch_3);
jdbcTemplate.query(sql_4, args_4,rch_4);
......
[/code]

此时,在内部实际上执行了,n次 getConnection,releaseConnection.

而这些操作,在很多时候,是可以通过一个Connection来完成的.

我的扩展就是实现了这个功能.

[code]

JdbcTemplatePlus jdbcTemplate=new JdbcTemplatePlus(ds);
// 内部使用一个唯一的Connection
jdbcTemplate.setUseOneConnection(true);
jdbcTemplate.query(sql_1, args_1,rch_1);
jdbcTemplate.query(sql_2, args_2,rch_2);
jdbcTemplate.query(sql_3, args_3,rch_3);
jdbcTemplate.query(sql_4, args_4,rch_4);
......
// 最后调用该方法 释放那个内部唯一的Connection
// 虽然我在finalize 里调用了,不过finalize毕竟不是总能在正确的时间被正确的调用
jdbcTemplate.releaseConnection();

[/code]


我们系统中,有大量的嵌套查询.使用该JdbcTemplatePlus的唯一Connection特性后,类似的操作速度提升明显.
(不过可能我们原先的做法不对,也许 spring内部已经实现这个机制了 这些我就不明白了,欢迎大家来指正)
我们原先的做法(伪代码):
[code]

public List queryNsubQueryUserList(Map param){

// 外层查询
final String bsql="select * from ......";
final JdbcTemplate jdbcTemplate=createJdbcTemplate();

// 子查询相关
final String subSql="select ............ ";
final JdbcTemplate subJdbcTemplate=createJdbcTemplate();

List rslist=jdbcTemplate.query(bsql.toString(),sqlArg,
new ResultSetHandler(){
public void processRow(ResultSet rs) throws SQLException {
final 一个VO recordObj=new 一个VO();
// 子查询
subJdbcTemplate.query(subSql, subQueryArgs,
new ResultSetHandler(){
public void processRow(ResultSet rs) throws SQLException {
// 一些操作........
}
}
);
// 一些操作........
recordObj.setXXXXX(rs.getString("XXXXX"));
.........
// 将记录放入集合
addRecord(recordObj);
}
}
);
return rslist;
}
}

[/code]
在使用 JdbcTemplatePlus 代替 JdbcTemplate,并设置.setUseOneConnection(true)后,
耗时变为原先的1/8左右.


扩展的 JdbcTemplatePlus 代码如下,欢迎大家拍砖,挑错.
对 execute方法的修改如下:
把所有的 this.ativeJdbcExtractor换成 getNativeJdbcExtractor(), 这个是必须的 呵呵.

把所有 Connection con = DataSourceUtils.getConnection(getDataSource()); 换成
Connection con = tryGetConnection();

把所有 DataSourceUtils.releaseConnection(con, getDataSource()); 换成
tryReleaseConnection(con);

[code]

package com.neusoft.tdframework.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterDisposer;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.SqlProvider;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.Assert;

public class JdbcTemplatePlus extends JdbcTemplate {

private Connection connection=null;
private boolean useOneConnection=false;


public JdbcTemplatePlus() {
super();
}
public JdbcTemplatePlus(DataSource dataSource) {
super(dataSource);
}
public JdbcTemplatePlus(DataSource dataSource, boolean lazyInit) {
super(dataSource,lazyInit);
}

private Connection tryGetConnection(){
if (useOneConnection){
if (connection==null){
connection= DataSourceUtils.getConnection(getDataSource());
}
return connection;
}
return DataSourceUtils.getConnection(getDataSource());
}

private void tryReleaseConnection(Connection con){
if (!useOneConnection){
DataSourceUtils.releaseConnection(con, getDataSource());
}
}

public Connection getConnection(){
return connection;
}

public void setConnection(Connection connection){
this.connection=connection;
}

public boolean isUseOneConnection() {
return useOneConnection;
}

public void setUseOneConnection(boolean useOneConnection) {
this.useOneConnection = useOneConnection;
}

public void releaseConnection(){
DataSourceUtils.releaseConnection(connection, getDataSource());
}


// 不明白这个方法为什么spring不把他弄成 protected 或 public,
// 导致我要重写execute方法时还必须要重写这个方法 :(
public static String getSql(Object sqlProvider) {
if (sqlProvider instanceof SqlProvider) {
return ((SqlProvider) sqlProvider).getSql();
}
else {
return null;
}
}

/*
对 execute方法的修改如下:
把所有的 this.ativeJdbcExtractor换成 getNativeJdbcExtractor(), 这个是必须的 呵呵.

把所有 Connection con = DataSourceUtils.getConnection(getDataSource()); 换成
Connection con = tryGetConnection();

把所有 DataSourceUtils.releaseConnection(con, getDataSource()); 换成
tryReleaseConnection(con);
*/

public Object execute(ConnectionCallback action) throws DataAccessException {
Assert.notNull(action, "Callback object must not be null");

Connection con = tryGetConnection();
try {
Connection conToUse = con;
if (getNativeJdbcExtractor() != null) {
conToUse = getNativeJdbcExtractor().getNativeConnection(con);
} else {
conToUse = createConnectionProxy(con);
}
return action.doInConnection(conToUse);
} catch (SQLException ex) {
tryReleaseConnection(con);
con = null;
throw getExceptionTranslator().translate("ConnectionCallback",
getSql(action), ex);
} finally {
tryReleaseConnection(con);
}
}

public Object execute(StatementCallback action) throws DataAccessException {
Assert.notNull(action, "Callback object must not be null");

Connection con = tryGetConnection();
Statement stmt = null;
try {
Connection conToUse = con;
if (getNativeJdbcExtractor() != null
&& getNativeJdbcExtractor()
.isNativeConnectionNecessaryForNativeStatements()) {
conToUse = getNativeJdbcExtractor().getNativeConnection(con);
}
stmt = conToUse.createStatement();
applyStatementSettings(stmt);
Statement stmtToUse = stmt;
if (getNativeJdbcExtractor() != null) {
stmtToUse = getNativeJdbcExtractor().getNativeStatement(stmt);
}
Object result = action.doInStatement(stmtToUse);
handleWarnings(stmt.getWarnings());
return result;
} catch (SQLException ex) {
JdbcUtils.closeStatement(stmt);
stmt = null;
tryReleaseConnection(con);
con = null;
throw getExceptionTranslator().translate("StatementCallback",
getSql(action), ex);
} finally {
JdbcUtils.closeStatement(stmt);
tryReleaseConnection(con);
}
}

public Object execute(PreparedStatementCreator psc,
PreparedStatementCallback action) throws DataAccessException {

Assert.notNull(psc, "PreparedStatementCreator must not be null");
Assert.notNull(action, "Callback object must not be null");
if (logger.isDebugEnabled()) {
String sql = getSql(psc);
logger.debug("Executing prepared SQL statement"
+ (sql != null ? " [" + sql + "]" : ""));
}

Connection con = tryGetConnection();
PreparedStatement ps = null;
try {
Connection conToUse = con;
if (getNativeJdbcExtractor() != null
&& getNativeJdbcExtractor()
.isNativeConnectionNecessaryForNativePreparedStatements()) {
conToUse = getNativeJdbcExtractor().getNativeConnection(con);
}
ps = psc.createPreparedStatement(conToUse);
applyStatementSettings(ps);
PreparedStatement psToUse = ps;
if (getNativeJdbcExtractor() != null) {
psToUse = getNativeJdbcExtractor()
.getNativePreparedStatement(ps);
}
Object result = action.doInPreparedStatement(psToUse);
handleWarnings(ps.getWarnings());
return result;
} catch (SQLException ex) {
if (psc instanceof ParameterDisposer) {
((ParameterDisposer) psc).cleanupParameters();
}
String sql = getSql(psc);
psc = null;
JdbcUtils.closeStatement(ps);
ps = null;
tryReleaseConnection(con);
con = null;
throw getExceptionTranslator().translate(
"PreparedStatementCallback", sql, ex);
} finally {
if (psc instanceof ParameterDisposer) {
((ParameterDisposer) psc).cleanupParameters();
}
JdbcUtils.closeStatement(ps);
tryReleaseConnection(con);
}
}

public Object execute(CallableStatementCreator csc,
CallableStatementCallback action) throws DataAccessException {

Assert.notNull(csc, "CallableStatementCreator must not be null");
Assert.notNull(action, "Callback object must not be null");
if (logger.isDebugEnabled()) {
String sql = getSql(csc);
logger.debug("Calling stored procedure"
+ (sql != null ? " [" + sql + "]" : ""));
}

Connection con = tryGetConnection();
CallableStatement cs = null;
try {
Connection conToUse = con;
if (getNativeJdbcExtractor() != null) {
conToUse = getNativeJdbcExtractor().getNativeConnection(con);
}
cs = csc.createCallableStatement(conToUse);
applyStatementSettings(cs);
CallableStatement csToUse = cs;
if (getNativeJdbcExtractor() != null) {
csToUse = getNativeJdbcExtractor()
.getNativeCallableStatement(cs);
}
Object result = action.doInCallableStatement(csToUse);
handleWarnings(cs.getWarnings());
return result;
} catch (SQLException ex) {
// Release Connection early, to avoid potential connection pool
// deadlock
// in the case when the exception translator hasn't been initialized
// yet.
if (csc instanceof ParameterDisposer) {
((ParameterDisposer) csc).cleanupParameters();
}
String sql = getSql(csc);
csc = null;
JdbcUtils.closeStatement(cs);
cs = null;
tryReleaseConnection(con);
con = null;
throw getExceptionTranslator().translate(
"CallableStatementCallback", sql, ex);
} finally {
if (csc instanceof ParameterDisposer) {
((ParameterDisposer) csc).cleanupParameters();
}
JdbcUtils.closeStatement(cs);
tryReleaseConnection(con);
}
}


protected void finalize() throws Throwable{
super.finalize();
releaseConnection();
}



}

[/code]