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 } 

Answer 2

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