Spring jdbcTemplate is stuck at Fetching JDBC Connection

apache-commons-dbcpjdbctemplatelog4jspringspring-jdbc

I am having issue with spring jdbcTemplate. It used to work fine but now it doesn't return any value neither error message. My table size has grown considerably large, around 35 million records which could be a reason for this.

But my main concern is that jdbcTemplate is not throwing any exception neither releasing the control to next line of code and my application breaks.
I would like the jdbcTemplate to release connection if for some reason it is unable to perform the query operation. Below is my code, data source details and log statements that I get from log4j after enabling debug logs. Nothing is logged after Fetching JDBC connection. 

SqlRowSet oRs = jdbcTemplate.queryForRowSet(strSql, new Object[] { Integer.valueOf(1) });

Data Source configuration

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:4928/dbtest" />
        <property name="username" value="root" />
        <property name="password" value="" />
        <property name="removeAbandoned" value="true" />
        <property name="initialSize" value="3" />
        <property name="maxActive" value="10" />
    </bean>
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource">
            <ref local="dataSource" />
        </property>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource" />
    </bean>
    <bean id="sendMail" class="com.app.SendMails">
        <property name="jdbcTemplate" ref="jdbcTemplate" />
    </bean>
    <bean id="response" class="com.app.BuildResponses">
        <property name="jdbcTemplate" ref="jdbcTemplate" />
    </bean>
</beans>

Logs:

DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Executing prepared SQL statement 
DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource

any help is appreciated

Best Answer

Could it be that the JDBC connection pool is exhausted and the thread is waiting for a connection ? Do you have other threads or transactions which use the same data source ?

To check if the connection pool is exhausted add

<property name="maxWait" value="1000"/>

To your dataSource bean definition. You will get an exception if a connection can't be allocated within timeout which is 1 second (1000 milliseconds).

BasicDataSource waits forever when borrowing a connection from the pool unless you specify a timeout.

Related Topic