Databases
StackOverflow2013
Postsdbo
POBasicDataSource with JDBCTemplate not pooling connections as expected
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POBasicDataSource with JDBCTemplate not pooling connections as expected
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKyle
    1. USKyle
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POBasicDataSource with JDBCTemplate not pooling connections as expected
      1. This table or related slice is empty.
      1. VTUpMod
    1. COShow us your config, around where you configure and inject the data source and the JdbcTemplate.
      singulars
      1. POBasicDataSource with JDBCTemplate not pooling connections as expected
      1. USskaffman
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload