Using Jdbc Template with DataSourceUtils.getConnection method

Question

I am not sure below is the correct way to manage a db connectivity in a connection pooled environment using Jdbc template with DataSourceUtils.getConnection.

What I have done:

• We are using apache dbcp for connection pooling.In Spring appconfig configured connection pooling and Jdbc template as shown below

  <!-- data source for xxxx--> <bean id="dbName" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="${oracle.driver}" /> <property name="url" value="${xxxx.oracle.url}" /> <property name="username" value="${xxxx.oracle.username}" /> <property name="password" value="${xxxx.oracle.password}" /> <property name="minIdle" value="1" /> </bean> <bean id="jdbcTemplateUib" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dbName" /> </bean>

• In DAOImpl, inside the method we have to invoke a stored procedure with custom SQL data types. Hence we are required to get connection object for the same. We are getting the connection as follows :

  this.connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());

  where DataSourceUtils is a Spring implementation described here.

The above configuration is working fine in local but we are bit worried on the following aspects of this program:

• If we access connection in this manner will it override the connection pooling mechanism in appconfig file?
• As per documnetation here, DataSourceUtils.getConnection(DataSource dataSource) will reuse existing connection. So I am assuming this will not break any connection created via JdbcTemplate and dbcp objects.
• No need of explicitly releasing this connection since it is managed by the jdbcTemplate.

I am not quite sure on the above mentioned items. Need some expert opinion.

Answer 1

I would recommend using the JdbcTemplate#execute(ConnectionCallback action) method. That allows you access to the connection while still taking advantage of Spring's resource and exception management features. You just need to implement the doInConnection callback method.

jdbcTemplate.execute(new ConnectionCallback<Object>() {
    @Override
    public Object doInConnection(Connection connection) 
            throws SQLException, DataAccessException {

         // do your processing here

        return null;
    }
});