How to reuse the same connection with a Spring's JdbcTemplate?

Spring provides a special DataSource that allows you to do this: SingleConnectionDataSource

Changing your code to this should do the trick:

SingleConnectionDataSource dataSource = new SingleConnectionDataSource();....// The rest stays as is

For use in multi-threaded applications, you can make the code re-entrant by borrowing a new connection from the pool and wrapping it around the database-intensive section of code:

// ... this code may be invoked in multiple threads simultaneously ...try(Connection conn = dao.getDataSource().getConnection()) {    JdbcTemplate db = new JdbcTemplate(new SingleConnectionDataSource(conn, true));    // ... database-intensive code goes here ...     // ... this code also is safe to run simultaneously in multiple threads ...    // ... provided you are not creating new threads inside here}

Here's an example using Apache DBCP:-

BasicDataSource dbcp = new BasicDataSource();dbcp.setDriverClassName("com.mysql.jdbc.Driver");dbcp.setUrl("jdbc:mysql://localhost/test");dbcp.setUsername("");dbcp.setPassword("");JdbcTemplate jt = new JdbcTemplate(dbcp);jt.execute("SELECT 1");jt.execute("SELECT 1");

The log4j output is:-

[DEBUG] [JdbcTemplate] [execute:416] - Executing SQL statement [SELECT 1][DEBUG] [DataSourceUtils] [doGetConnection:110] - Fetching JDBC Connection from DataSource[DEBUG] [DataSourceUtils] [doReleaseConnection:332] - Returning JDBC Connection to DataSource[DEBUG] [JdbcTemplate] [execute:416] - Executing SQL statement [SELECT 1][DEBUG] [DataSourceUtils] [doGetConnection:110] - Fetching JDBC Connection from DataSource[DEBUG] [DataSourceUtils] [doReleaseConnection:332] - Returning JDBC Connection to DataSource

In one word, Spring JDBCTemplate DriverManagerDataSource does not support connection pool. If you want to use connection pool, DBCP and C3P0 are both good choices.

Let's go through JDBCTemplate source code to see why...

No matter call update, queryForObject and other methods, they finally will call execute method:

    @Override    public <T> T execute(ConnectionCallback<T> action) throws DataAccessException {        Assert.notNull(action, "Callback object must not be null");        Connection con = DataSourceUtils.getConnection(getDataSource());        try {            Connection conToUse = con;            if (this.nativeJdbcExtractor != null) {                // Extract native JDBC Connection, castable to OracleConnection or the like.                conToUse = this.nativeJdbcExtractor.getNativeConnection(con);            }            else {                // Create close-suppressing Connection proxy, also preparing returned Statements.                conToUse = createConnectionProxy(con);            }            return action.doInConnection(conToUse);        }        catch (SQLException ex) {            // Release Connection early, to avoid potential connection pool deadlock            // in the case when the exception translator hasn't been initialized yet.            DataSourceUtils.releaseConnection(con, getDataSource());            con = null;            throw getExceptionTranslator().translate("ConnectionCallback", getSql(action), ex);        }        finally {            DataSourceUtils.releaseConnection(con, getDataSource());        }    }

It calls DataSourceUtils.getConnection method to get connection and DataSourceUtils.releaseConnection to release connection.

From DataSourceUtils source code, we see Connection con = dataSource.getConnection(); and con.close();.

Which means get connection operation is defined by implementing DataSource interface, and close connection operation is defined by implementing Connection interface. This allows other DataSource/Connection implementations easily inject to Spring JDBCTemplate.

The DataSource implementation in Spring JDBCTemplate is DriverManagerDataSource. From:

protected Connection getConnectionFromDriverManager(String url, Properties props) throws SQLException {    return DriverManager.getConnection(url, props);}

And

public static void doCloseConnection(Connection con, DataSource dataSource) throws SQLException {    if (!(dataSource instanceof SmartDataSource) || ((SmartDataSource) dataSource).shouldClose(con)) {        con.close();    }}

We see each time it returns a new connection, and close current connection. That's why it does not support connection pool.

While in DBCP, the DataSource implementation is PoolingDataSource, we see getConnection() is from a connection pool; the Connection implementation is PoolableConnection, we see close() method is not to close connection, instead it returns the connection to connection pool.

That's the magic!