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

关键字: Spring   sping    

对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,所以其他地方从简)

 

代码

execute方法开始

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

  // 做一些数据库操作

  DataSourceUtils.releaseConnection(con, getDataSource());

execute方法结束

<script>render_code();</script>

 

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

如下:

代码

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);
......

<script>render_code();</script>

 

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

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

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

 

代码

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();

<script>render_code();</script>

 

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

代码

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;
    }
}

<script>render_code();</script>
在使用 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);

 

代码

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方法时还必须要重写这个方法 <img src="/images/forum/smiles/icon_sad.gif"/>
    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();
    }


}