Cannot close Oracle connection from WebSphere datasource

Question

I try to connect to Oracle database (check connection status). I'm using following code, which works fine.

public String getDatabaseStatus() {
    Connection conn;
    try {
        conn = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.0.70:1521:XE", "foo","bar");
        conn.close();
    } catch (Exception e) {
        return "ERR - " + e.getMessage();
    }
    return "Connection succesful";
}

However, when using Websphere datasource, after 10 (connection limit) refreshes page hangs. Code:

public String getDatabaseStatus() {
    Connection conn;
    try {
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("jdbc/xe");
        conn = WSCallHelper.getNativeConnection(ds.getConnection());
    } catch (Exception e) {
        return "ERR - " + e.getMessage();
    }
    return "Connection succesful";
}

I tried to close provided connection, but it gives me error:

J2CA0206W - A connection error occurred. To help determine the problem, enable the Diagnose Connection Usage option on the Connection Factory or Data Source. This is the multithreaded access detection option. Alternatively check that the Database or MessageProvider is available.

Any help will be appreciated.

I can get native Oracle connection this way. I need this one to get results from Oracle functions; OracleCallableStatement is type of object received. — The Raven

Steve C Steve C · Accepted Answer · 2015-07-21T14:08:39

You must close the connection that you received from the DataSource:

public String getDatabaseStatus() {
    Connection conn;
    try {
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("jdbc/xe");
        java.sql.Connection connection = ds.getConnection();
        try {
            conn = WSCallHelper.getNativeConnection(connection);
        } finally {
            safeClose(connection);
        }
    } catch (Exception e) {
        return "ERR - " + e.getMessage();
    }
    return "Connection succesful";
}

private void safeClose(java.sql.Connection connection) {
    try {
        connection.close();
    } catch (SQLException e) {
        log.warn("Failed to close database connection", e);
    }
}

If you're using Java 7 or better you can simplify it to:

public String getDatabaseStatus() {
    Connection conn;
    try {
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("jdbc/xe");
        try (java.sql.Connection connection = ds.getConnection()) {
            conn = WSCallHelper.getNativeConnection(connection);
        }
    } catch (Exception e) {
        return "ERR - " + e.getMessage();
    }
    return "Connection succesful";
}

If you fail to do this your connections will not be returned to the pool and you will run out of connections.

Cannot close Oracle connection from WebSphere datasource

1 Answers