Java – Oracle data source connection pooling not working used with Spring and JDBCTemplate

connection-poolingjavajdbctemplateoracle11gspring

Question: Lot of active unclosed physical connections with database even with connection pooling. Can someone tell me why is it so?

I configured the connection pool settings using oracle.jdbc.pool.OracleDataSource. However it seems the physical connections are not getting closed after use.
I thought, Since it is connection pooling, the connections will be reused from the pool, so so many physical connections will not be made,
but thats not what is happening now!

There are 100+ active physical connections in the database generating from the application [not from plsql developer or any such client tools],
due to which it kicks off TNS error while trying to do write operations on database,
where as read operations are fine even with large number of active connections.

Here is the Spring configuration,

<bean id="oracleDataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close"
                                p:URL="${url}"
                                p:user="${username}"
                                p:password="${password}"
                                p:connectionCachingEnabled="true">
                                <property name="connectionProperties">
                                   <props merge="default">
                                      <prop key="AutoCommit">false</prop>
                                   </props>
                                </property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
                                p:dataSource-ref="oracleDataSource" />

<bean id="transactionManager"
                                class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
                                p:dataSource-ref="oracleDataSource">
</bean>

The SQL that returned the 100+ active connections is ,

select username, terminal,schemaname, osuser,program from v$session where username = 'grduser'

Best Answer

You should configure connection cache, the default value of max connections for implicit connection cache is the max number of database sessions configured for the database.