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

java, jdbc, spring

Solution

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

Problem

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

Original source