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.
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 much better. I started investigating with a profiler tool, and I noticed that when using BasicDataSource, a new connection was being created for every query.
Investigating further, I can see where the connection is being closed after the query is finished. Specifically in Spring's DataSourceUtil class:
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 && 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();
}
}
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.
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.
EDIT 1:
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
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<1000; i++){
template.queryForInt("select count(*) from mytable");
}
sw.stop();
System.out.println("TIME: " + sw.getTotalTimeSeconds() + " seconds");
}}
Here are my two datasource configurations:
<bean id="myDataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
<property name="driverClassName" value="${db.driver}" />
<property name="url" value="${db.url}" />
<property name="username" value="${db.username}" />
<property name="password" value="${db.password}" />
</bean>
<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${db.driver}" />
<property name="url" value="${db.url}" />
<property name="username" value="${db.username}" />
<property name="password" value="${db.password}" />
</bean>
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.
