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

Question

I have the following code:

    @Test
    public void springTest() throws SQLException{
        //Connect to the DB.
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:/data/h2/testa");
        dataSource.setUsername("");
        dataSource.setPassword("");
        JdbcTemplate jt=new JdbcTemplate(dataSource);
        jt.execute("SELECT 1");
        jt.execute("SELECT 1");
    }

I expect the two execute() lines to reuse the same connection. However, the log output says:

2011-02-10 12:24:17 DriverManagerDataSource [INFO] Loaded JDBC driver: org.h2.Driver
2011-02-10 12:24:17 JdbcTemplate [DEBUG] Executing SQL statement [SELECT 1]
2011-02-10 12:24:17 DataSourceUtils [DEBUG] Fetching JDBC Connection from DataSource
2011-02-10 12:24:17 DriverManagerDataSource [DEBUG] Creating new JDBC DriverManager Connection to [jdbc:h2:/data/h2/testa]
2011-02-10 12:24:17 DataSourceUtils [DEBUG] Returning JDBC Connection to DataSource
2011-02-10 12:24:17 JdbcTemplate [DEBUG] Executing SQL statement [SELECT 1]
2011-02-10 12:24:17 DataSourceUtils [DEBUG] Fetching JDBC Connection from DataSource
2011-02-10 12:24:17 DriverManagerDataSource [DEBUG] Creating new JDBC DriverManager Connection to [jdbc:h2:/data/h2/testa]
2011-02-10 12:24:17 DataSourceUtils [DEBUG] Returning JDBC Connection to DataSource

The above example runs quite fast but I have a larger piece of code that does basically the same thing and hangs for a long time on Creating new JDBC DriverManager Connection. I never get an error but it makes the code run very slowly. Can I somehow refactor the above code to just use the same connection?

Thanks

Answer 1

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
}