0bj3ct 0bj3ct - 1 year ago 201
Java Question

Spring oracle datasource does not destroy connection

I use

for Oracle Pooling connections, but it seems the connections stay alive in the database. Today I got an error while connecting to Oracle Database:

ORA-12516: TNS:listener could not find available handler with matching protocol stack

And I was told by our database administrator that too many open connections stay in "IDLE" mode from my application.

I use ojdbc7:


Spring applicationContext.xml file:

<bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
<property name="URL" value="${jdbc.url}" />
<property name="user" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="connectionCachingEnabled" value="true" />

The DbConnect class:

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

public class DbConnect {

private DataSource dataSource;

public Connection getConnection() throws SQLException {
return dataSource.getConnection();

Also I use jdk7 try-with-resources syntax in my dao level for connections:

private DbConnect dbConnect;

public List<User> getAllUsers() {
List<User> list = new ArrayList<>();

try(Connection connection = dbConnect.getConnection()) {
try(PreparedStatement preparedStatement = connection.prepareStatement("select * from V_USERS t")) {
try(ResultSet resultSet = preparedStatement.executeQuery()) {
while(resultSet.next()) {
catch(Exception e) {
log.error(e.getMessage(), e);

return list;

Cannot figure out where is the actual problem. Should I use c3p0 instead of OracleDataSource? Is there any bug in ojdbc7 or error in my code?

Thanks in advance!

Answer Source

The whole point of a pool is to keep connections open. However I'm not sure if the OracleDataSource is an actual pool or that you need to OracleConnectionPoolDataSource.

Instead of using a plain Oracle pool I suggest using HikariCP. The page on configuration options and the defaults.

In that case your datasource would look like

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"> 
    <property name="datasourceClassName" value="oracle.jdbc.pool.OracleDataSource" />
    <property name="dataSourceProperties">
            <prop key="URL" value="${jdbc.url}" /> 
            <prop key="user" value="${jdbc.username}" /> 
            <prop key="password" value="${jdbc.password}" /> 
            <prop key="connectionCachingEnabled" value="true" />

Note: There is also a nice page on pool sizing (which is actually from Oracle!).

I would also suggest, to cleanup your code, to remove the DbConnect class and inject (or create) a JdbcTemplate to use instead of working with the plain connection. Saves you managing all the JDBC objects.

private final JdbcTemplate jdbcTemplate;

public YourRepository(DataSource ds) {
    this.jdbcTemplate=new JdbcTemplate(ds);

public List<User> getAllUsers() {
    List<User> list = new ArrayList<>();
    return this.jdbcTemplate("select * from V_USERS t", new RowMapper() {
        public User mapRow(ResultSet rs, int row) throws SQLException {
            return RowFetcher.fetchUser(rs);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download