CODEWITHSUNDEEP
javadatabaseoracle-databaseconnectionojdbc
Adey
Adey

Reputation: 2216

Why OracleConnection isn't returned to the pool?

I have a problem which I did not find on the Internet.

I'm trying to connect to DB with ojdbc6. I get resource from jndi

<Resource name="jdbc/ds_conn" type="javax.sql.DataSource" maxTotal="10" 
url="jdbc:oracle:thin:@someDB.com:1731/nameDB" 
driverClassName="oracle.jdbc.OracleDriver" 
connectionProperties="SetBigStringTryClob=true; 
oracle.net.READ_TIMEOUT=5000;" password="pass" username="user"
maxWait="10000" maxIdle="300" maxActive="1000" auth="Container"/>

with the method 

public OracleConnection getConnection() throws OracleDatabaseReaderConnectionException, NamingException {

    Context initialContext = null;
    try {
        initialContext = new InitialContext();
        DataSource dataSource = (DataSource) initialContext.lookup("java:comp/env/jdbc/ds_conn");
        OracleConnection connection = (OracleConnection) dataSource.getConnection().unwrap(OracleConnection.class);
        return connection;
    } catch (NamingException | SQLException e) {
        e.printStackTrace();
        throw new OracleDatabaseReaderConnectionException(e.getMessage());
    } finally {
        if (initialContext != null) {
            initialContext.close(); 
        }
    }
}

and next I'm executing query

try (OracleConnection connection = getConnection();
    OraclePreparedStatement preparedStatement = (OraclePreparedStatement) connection.prepareStatement(sqlQuery);
        OracleResultSet resultSet = (OracleResultSet) preparedStatement.executeQuery()) {

    //some actions

} catch (OracleDatabaseReaderConnectionException | SQLException | NamingException e) {
        throw new OracleDatabaseReaderException(e.getMessage());
}

}

As I understand it's a usual actions to connect to db and execute the query.

But connection doesn't close. As you can see in my jndi I have param maxTotal="10". So after 10 queries pool is becoming full and other queries just waiting for a place in pool. I dont know how to resolve this problem and make connections closed. Maybe I have conflict in casting Connection to OracleConnection and so on? Maybe thy-with-resources doesn't work well?

Upvotes: 0

Views: 1297

Answers (1)

Kayaman
Kayaman

Reputation: 73528

The problem is that you're closing the inner OracleConnection instead of the outer wrapper. The difference being that the former really closes the connection, whereas closing the wrapper will just return the connection to the pool.

(OracleConnection) dataSource.getConnection().unwrap(OracleConnection.class);

Here calling unwrap() causes the pooling wrapper to disappear, and you can't call close on it anymore. One way fix this would be to call unwrap() only after starting a try-with-resources, like this:

try (Connection connection = getConnection()) {  // Now the pooled connection will be "closed"
    OracleConnection oc = connection.unwrap(OracleConnection.class);

    try(OraclePreparedStatement ps ...) ...

But that's not very pretty, at least if you need to do it in lots of places. Is it absolutely necessary to use the Oracle specific classes instead of the JDBC interfaces here?

Upvotes: 3

Related Questions