JdbcTemplate undertanding

Question

Here's something that I am running across as of late.

My understanding of Spring's JdbcTemplate is that you can call:

JdbcTemplate template = new JdbcTemplate(dataSource);
Connection conn = template.getDataSource().getConnection();

Which returns you a connection from the JdbcTemplate using the datasource that is passed in. If I then do:

template.getDataSource().getConnection().close();

does this just get yet another connection and close it, creating a leaky resource, or does it get you THE connection that it is using?

EDIT:

I have written 2 methods in a class one is the old-school low level way of writing JDBC statements (with Connections, Statements, and ResultSets):

public void execute(String tableName) {
    try {
        Class.forName("com.ibm.as400.access.AS400JDBCDriver");
        Connection con = DriverManager.getConnection("jdbc:as400://viper", "******", "******"); 
        Statement select = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

        ResultSet rs = select.executeQuery ("SELECT * FROM ******." + tableName );
        logger.info("start clearing: " + tableName);

        while (rs.next ()) {
            rs.deleteRow();
        }
        logger.info("Step1 done clearing: " + tableName);

        ConnectionRecycler.recycleConnection(select, true, con);

        execute2(tableName);
    } catch (Exception eX) {
        logger.error(eX);
    }
}

The other method:

public void execute2(String tableName) {

    String nameOS = System.getProperty("os.name");
    String sql = (nameOS.equals("OS/400")) ? "DELETE from " + tableName : 
        "DELETE from " + tableName + " with none";

    JdbcTemplate templateSNPJ;

    templateSNPJ = new JdbcTemplate(this.snpjDataSource);
    templateSNPJ.update(sql);

    logger.info("Finished clearing: " + tableName);
    getServiceManager().unregisterService(this);
}

I have the resources being cleaned up correctly this way. The second method, is using a:

JdbcTemplate.update(sqlCommand);

But it appears that the JdbcTemplate is keeping the connection's alive longer than the pool is configured for.

I read this article on SO: Database connection management in Spring and it eluded to having to use a dataSource configuration with a destroy-method=closed parameter defined in the bean like so:

    <bean id="SnpjDataSource" class="com.atomikos.jdbc.nonxa.AtomikosNonXADataSourceBean" destroy-method="close">
      <property name="uniqueResourceName" value="@#$$datasource"/>
      <property name="driverClassName" value="com.ibm.as400.access.AS400JDBCDriver"/>
      <property name="url" value="jdbc:as400://VIPER/******"/>
      <property name="user" value="FuManChu"/>
      <property name="password" value="*%$@^%$*#^$@^$@"/>
      <property name="maxPoolSize" value="10"/>
      <property name="reapTimeout" value="40"/>
    </bean>

EDIT2:

The ConnectionRecycler.recycleConnection method:

public static void recycleConnection(Statement state, boolean closeConnection, 
        Connection connect) {
    try {
        state.close();
        if (closeConnection) {
            connect.close();
        }
    } catch (SQLException sqlEx) {
        logger.error("Error closing resources!  ", sqlEx);
    }
}

wmorrison365 wmorrison365 · Accepted Answer · 2013-04-23T15:30:22

That depends on your datasource. If you want to be certain to use the same Connection, either keep a handle on it from the #getConnection call or use a SingleConnectionDataSource. Note that you have to be operating in a thread-safe env to use this datasource. It itself isn't thread-safe.

Also, you shouldn't really need to access the Connection directly. That's the whole point of JdbcTemplate. It hides the JDBC internals... avoids risk of leaking Connections, etc.

JdbcTemplate undertanding

1 Answers