When a JdbcTemplate object is instantiated using a DataSource object, does it open a connection?

Question

I have a piece of code inside my constructor that looks like this:

public class Foo {
    JdbcTemplate jdbcTemplate;

    @Autowired
    public Foo(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate( dataSource );
    }
}

My question is: When the JdbcTemplate object is created, does it obtain a connection from the connection pool and hold it in use?

I'm asking this question because I want to avoid the situation where instantiating a Foo object takes up a connection from the pool and holds it. Such an arrangement will mean rapid exhaustion of my connection pool just from bean instantiation.

Answer 1

You actually have two distinct questions in this post.

Let's take one at a time.

When a JdbcTemplate object is instantiated using a DataSource object, does it open a connection?

No, creating JdbcTemplate object does not open the connection to the Database. It does not even create it.

We can see the source code of afterPropertiesSet() method, which is invoked in the JdbcTemplate(DataSource dataSource) constructor:

@Override
public void afterPropertiesSet() {
    if (getDataSource() == null) {
        throw new IllegalArgumentException("Property 'dataSource' is required");
    }
    if (!isLazyInit()) {
        getExceptionTranslator();
    }
}

The moment, when the connection is actually opened is when you invoke one of the query methods of JdbcTemplate API.

For instance, the execute(ConnectionCallback<T> action) method, looks like this:

@Override
@Nullable
public <T> T execute(ConnectionCallback<T> action) throws DataAccessException {
    Assert.notNull(action, "Callback object must not be null");

    Connection con = DataSourceUtils.getConnection(obtainDataSource());
    try {
        // Create close-suppressing Connection proxy, also preparing returned Statements.
        Connection 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.
        String sql = getSql(action);
        DataSourceUtils.releaseConnection(con, getDataSource());
        con = null;
        throw translateException("ConnectionCallback", sql, ex);
    }
    finally {
        DataSourceUtils.releaseConnection(con, getDataSource());
    }
}

---

Second question which you are asking in the actual body of your question is:

Do the JdbcTemplate query methods API reuse the JDBC connection or they create new one each time?

Let's have a look at DataSourceUtils.getConnection(obtainDataSource()); method, which, in turn, tries doGetConnection(dataSource). The source code of latter looks as follows:

public static Connection doGetConnection(DataSource dataSource) throws SQLException {
    Assert.notNull(dataSource, "No DataSource specified");

    ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
    if (conHolder != null && (conHolder.hasConnection() || conHolder.isSynchronizedWithTransaction())) {
        conHolder.requested();
        if (!conHolder.hasConnection()) {
            logger.debug("Fetching resumed JDBC Connection from DataSource");
            conHolder.setConnection(fetchConnection(dataSource));
        }
        return conHolder.getConnection();
    }
    // Else we either got no holder or an empty thread-bound holder here.

    logger.debug("Fetching JDBC Connection from DataSource");
    Connection con = fetchConnection(dataSource);

    if (TransactionSynchronizationManager.isSynchronizationActive()) {
        try {
            // Use same Connection for further JDBC actions within the transaction.
            // Thread-bound object will get removed by synchronization at transaction completion.
            ConnectionHolder holderToUse = conHolder;
            if (holderToUse == null) {
                holderToUse = new ConnectionHolder(con);
            }
            else {
                holderToUse.setConnection(con);
            }
            holderToUse.requested();
            TransactionSynchronizationManager.registerSynchronization(
                    new ConnectionSynchronization(holderToUse, dataSource));
            holderToUse.setSynchronizedWithTransaction(true);
            if (holderToUse != conHolder) {
                TransactionSynchronizationManager.bindResource(dataSource, holderToUse);
            }
        }
        catch (RuntimeException ex) {
            // Unexpected exception from external delegation call -> close Connection and rethrow.
            releaseConnection(con, dataSource);
            throw ex;
        }
    }

    return con;
}

and finally, we see:

if (conHolder != null && (conHolder.hasConnection() || conHolder.isSynchronizedWithTransaction())) {
    conHolder.requested();
    if (!conHolder.hasConnection()) {
        logger.debug("Fetching resumed JDBC Connection from DataSource");
        conHolder.setConnection(fetchConnection(dataSource));
    }
    return conHolder.getConnection();
}

and here we are. You can clearly see that if the connection exists, it's fetched and reused.

See the full code of how connection is created if it doesn't exist.

The other methods of JdbcTemplate API work in a similar manner.