Connection pool replacement for already implemented Spring Jdbctemplate project

Question

I am a doing a mid size project with spring jdbc and MsSQL server , project is almost 50% done , now when every request doing lots of inserts and updates specially with those tables which contains lots of columns and large datasets is performing very slow , and sometimes showing connection closed. Now i am thinking to integrate C3p0 or similar connection pooling but i cant change any DAO code which i already done .. I implemented a DAOHelper class with JDBCTemplate variable and injecting the JDBCTemplate dependency in applicationContext.xml with autowiring of DAOClass in controller class , and i extended this DAOHelper to all DAO classes and using this jdbcTemplate to do JDBC operations.

<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="url" value="jdbc:sqlserver://192.168.1.101:1433;databaseName=OrderManager"/>
    <property name="username" value="sa"/>
    <property name="password" value="520759"/>   
</bean> 
<bean id="JdbcDataSource" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="ds"/>
</bean>
<bean id="OrderDAO" class="com.ordermanager.order.dao.OrderDAO" >
    <property  name="jdbcTemplate" ref="JdbcDataSource"/>
    <property  name="transactionManager" ref="transactionManager"/>
</bean>  




@Controller
public class OrderController {

@Autowired
OrderDAO orderDAO;

@RequestMapping(value = "/addNewItem", method = RequestMethod.GET)
public ModelAndView addItem(@RequestParam("ParamData") JSONObject paramJson) {
    ApplicationContext ctx = new FileSystemXmlApplicationContext(ConstantContainer.Application_Context_File_Path);
    OrderDAO orderDAO = (OrderDAO) ctx.getBean("OrderDAO");
    return new ModelAndView("MakeResponse", "responseValue", orderDAO.addItem(paramJson));
}



public class DAOHelper {

private JdbcTemplate jdbcTemplate;
private PlatformTransactionManager transactionManager;

public PlatformTransactionManager getTransactionManager() {
    return transactionManager;
}

public void setTransactionManager(PlatformTransactionManager txManager) {
    this.transactionManager = txManager;
}

public JdbcTemplate getJdbcTemplate() /*I am using this Method for all JDBC Task*/ {
    return jdbcTemplate;
}

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
}

Now with minimal code changes how can i integrate C3p0 or any good connection pooling library with my already written code.

Answer 1

Just change the ds bean in your config xml with following and consider adding other c3p0 properties according to your own. make sure to have c3p0 jar in your classpath.

    <bean id="ds" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
        <property name="jdbcUrl" value="jdbc:sqlserver://192.168.1.101:1433;databaseName=OrderManager" />
        <property name="user" value="sa" />
        <property name="password" value="520789" />
    </bean>