CODEWITHSUNDEEP
javaspring-securityapache-commons-dbcp
java1007
java1007

Reputation: 1

DBCP2 initialization

I'm using DBCP2 with Spring Security (XML configuration). My problem is that everytime that I made a INSERT or UPDATE creates connections (initialSize = 100), so after a time I get "Too many connections".

I do not know if I doing something wrong in my configuration or in another step.

This is my configuration:

1. springSecurity.xml file

<beans:bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close"> 
       <beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />
       <beans:property name="url" value="${bd.url}" />
       <beans:property name="username" value="${bd.user}" />
       <beans:property name="password" value="${bd.password}" />
       <beans:property name="validationQuery" value="SELECT 1" />
       <beans:property name="removeAbandonedTimeout" value="30" />
       <beans:property name="removeAbandonedOnBorrow" value="true" />
       <beans:property name="removeAbandonedOnMaintenance" value="true" />
       <beans:property name="initialSize" value="100" />
       <beans:property name="maxTotal" value="1000" />
       <beans:property name="maxIdle" value="50" />
       <beans:property name="timeBetweenEvictionRunsMillis" value="30" />        
   </beans:bean>

<beans:bean id="npjt" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" >
     <beans:constructor-arg ref="dataSource"/>
</beans:bean>

<beans:bean id="DAOEmail" class="general.Email"  >  
       <beans:property name="npjt" ref="npjt" />
</beans:bean> 

<beans:bean id="DAOAddress" class="general.Address" >  
    <beans:property name="npjt" ref="npjt" />
</beans:bean> 

<beans:bean id="DAOPhone" class="general.Phone" >  
    <beans:property name="npjt" ref="npjt" />
</beans:bean> 

<beans:bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <beans:property name="dataSource" ref="dataSource"/>
</beans:bean>

<tx:annotation-driven transaction-manager="txManager"/>

2. DAO

public interface DAOEmail{
    public void save(HashMap<String, String> params);
    public void update(HashMap<String, String> params);
}

public interface DAOPhone{
    public void save(HashMap<String, String> params);
    public void update(HashMap<String, String> params);
}

public interface DAOAddress{
    public void save(HashMap<String, String> params);
    public void update(HashMap<String, String> params);
}

3. Calling from a Servlet

 ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("springSecurity.xml");
 DAOAddress dao = (DAOAddress) context.getBean("DAOAddress");
 dao.save(params);
 context.registerShutdownHook();

Same process per Servlet (Phone, email, address)

4. Operations per file (Address, phone, email)

public class Address implements DAOAddress {

    private NamedParameterJdbcTemplate npjt;

    public NamedParameterJdbcTemplate getNpjt() {
        return npjt;
    }

    public void setNpjt(NamedParameterJdbcTemplate npjt) {
        this.npjt = npjt;
    }

    @Transactional
    @Override
    public void save(HashMap<String, String> params) {
       try { 
         ....
            npjt.update();  
         ....
       } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
       }
    }


@Transactional
    @Override
    public void update(HashMap<String, String> params) {
      try { 
         ....
            npjt.update();  
         ....
       } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
       }

    }

Thanks!!!

Upvotes: 0

Views: 1612

Answers (2)

Kevin Le
Kevin Le

Reputation: 264

Try to use simplest configuration for datasource with minimum parameters, use default value for others as below:

<beans:bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"> 
       <beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />
       <beans:property name="url" value="${bd.url}" />
       <beans:property name="username" value="${bd.user}" />
       <beans:property name="password" value="${bd.password}" />
       <beans:property name="validationQuery" value="SELECT 1" />     
   </beans:bean>

If above configuration can resolve your issue, add the other parameters one by one to check which parameter causes problem.

Hope above solution can help.

Upvotes: 0

Kevin Le
Kevin Le

Reputation: 264

You need to modify this value in datasource configuration

<beans:property name="initialSize" value="100" />

"initialSize" in "dataSource" bean is the initial number of connections that are created when the pool is started. That value in your configuration is "100". It is two high.

Remove that property to use default value (0) or use lower value can resolve your issue.

Upvotes: 1

Related Questions