title: BasicDataSource with JDBCTemplate not pooling connections as expected
body: <p>I am trying to use BasicDataSource to pool connections with JDBCTemplate in a spring application. From everything I've read, this should be really simple: Just configure the BasicDataSource in XML, inject the data source into a bean, and in the setter method, create a new JDBCTemplate.  </p>
  
  <p>When I did this, I noticed my performance was terrible. So then I switched to Spring's SingleConnectionDataSource, just to see what would happen, and my performance got <em>much</em> better.  I started investigating with a profiler tool, and I noticed that when using BasicDataSource, a new connection was being created for every query.  </p>
  
  <p>Investigating further, I can see where the connection is being closed after the query is finished. Specifically in Spring's DataSourceUtil class:</p>
  
  <pre><code>public static void doReleaseConnection(Connection con, DataSource dataSource) throws SQLException {
      if (con == null) {
          return;
      }
  
      if (dataSource != null) {
          ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
          if (conHolder != null &amp;&amp; connectionEquals(conHolder, con)) {
              // It's the transactional Connection: Don't close it.
              conHolder.released();
              return;
          }
      }
  
      // Leave the Connection open only if the DataSource is our
      // special SmartDataSoruce and it wants the Connection left open.
      if (!(dataSource instanceof SmartDataSource) || ((SmartDataSource) dataSource).shouldClose(con)) {
          logger.debug("Returning JDBC Connection to DataSource");
          con.close();
      }
  }
  </code></pre>
  
  <p>The thing that I notice is there is some special logic for 'SmartDataSource' which leaves the connection open. This partially explains the behavior I was seeing: Since SingleConnectionDataSource implements SmartDataSource, the connection is not closed.  However, I thought by using BasicDataSource, the close() method on the connection would just return the connection to the pool.  However, when I look at what is happening in my profiler, the close method is actually being called on my sybase connection: not any kind of 'Pooled Connection Wrapper' like I would expect to see.</p>
  
  <p>One last thing (this is what I'm going to investigate now): I use TransactionTemplate for some of my queries (involving commits to the database), but simple queries are not inside a transactionTemplate.  I don't know if that has anything to do with the problem or not.</p>
  
  <p>EDIT 1:</p>
  
  <p>Ok finally got some more time to investigate after getting pulled off the project a bit and, here is a very simple test that shows the problem</p>
  
  <pre><code>    public class DBConnectionPoolTest {
  
  @Autowired
  @Qualifier("myDataSource")
  private DataSource dataSource;
  
  @Test
  public void test() throws Exception{
      JdbcTemplate template = new JdbcTemplate(dataSource);
      StopWatch sw = new StopWatch();
      sw.start();
      for(int i=0; i&lt;1000; i++){
          template.queryForInt("select count(*) from mytable"); 
      }
      sw.stop();
  
      System.out.println("TIME: " + sw.getTotalTimeSeconds() + " seconds");   
  }}
  </code></pre>
  
  <p>Here are my two datasource configurations:</p>
  
  <pre><code>&lt;bean id="myDataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource"&gt;
      &lt;property name="driverClassName" value="${db.driver}" /&gt;
      &lt;property name="url" value="${db.url}" /&gt;
      &lt;property name="username" value="${db.username}" /&gt;
      &lt;property name="password" value="${db.password}" /&gt;
  &lt;/bean&gt;
  
  &lt;bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource"&gt;
      &lt;property name="driverClassName" value="${db.driver}" /&gt;
      &lt;property name="url" value="${db.url}" /&gt;
      &lt;property name="username" value="${db.username}" /&gt;
      &lt;property name="password" value="${db.password}" /&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p>When I run the test with the first configuration, it takes about 2.1 seconds. When I run it with the second configuration, it takes about 4.5 seconds.  I have tried various parameters on BasicDataSource, such as setting maxActive=1 and testOnBorrow=false, but nothing makes a difference. </p>