JDBC connection pooling issue

Question

Hello

I have, I might say, a quite a big issue.

I'm working on Java web application which use springs BasicDataSource to setup DB connection. I was testing the application locally and it works just fine... but, when application is online, connection to DB in some point just stuck. I was than investigating regarding connection pooling, and I figure it out that on each new HTTP request, where I have some of the queries executed, new pool is created. As I know, pooling is introduced to be reusable, and not created the each time when new DB access is involved. Or I'm wrong?

Here is my spring datasource config:

<bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="url"/>
        <property name="username" value="username"/>
        <property name="password" value="password"/>
        <property name="defaultAutoCommit" value="true"/>
 <property name="defaultTransactionIsolation" value="1"/>
 <property name="initialSize" value="0"/>
 <property name="maxActive" value="20"/>
 <property name="minIdle" value="0"/>
</bean>

Than I have configured:

<bean id="EventDao" class="my.managament.database.class">
    <property name="dataSource" ref="dataSource"/>
</bean>

And mainPageController which handles all HTTP requests sent to application

<bean id="mainController" class="my.management.main.controller.class">

In the rest of application, I use gedDatabase() to acquire DB connection, and do select through JDBCTemplate.

Where am I getting wrong?

Thanks

Answer 1

What is the life time of your EventDao ? You are injecting your DataSource into the dataSource property. I suspect that you are creating multiple EventDao beans and each time you create one you have a new DataSource. I think we would need to have further understanding of the code to be able to answer your question properly.

My two cents:
As far as I am concerned having code and wiring things through XML is a terrible anti-pattern.

Answer 2

You want to use dao and jdbcTemplate and dataSource through a connection pool. My guess the closest correct approach to your setup is having a dao which has JdbcTemplate field and a JdbcTemplate bean created with your dataSource bean. It would look like:

public class MyDAO {
  private JdbcTemplate jdbcTemplate;

  // your dao methods using jdbcTemplate here
}

where jdbcTemplate comes from a bean like:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <constructor-arg ref="dataSource">
</bean>

You should never need to obtain a connection from dataSource (which is apache dbcp based connection pool in your case) directly. JdbcTemplate will get a connection itself when needed. I'm not sure what "gedDatabase" is but it sounds like you tried to get connection yourself and possibly forgot to close it. This would result in pool quickly running out of connections. After handling 20 requests, the subsequent requests would be stuck on attempt to get connection from the pool.

Also I don't understand why and how you see multiple pools. You have a single connection pool which can hold up to 20 connections. All your beans are created as singletons which is the default spring scope.