Unable to close Oracle connection to WebSphere data source
I am trying to connect to an Oracle database (check the connection status). I am using the following code which works great.
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 a Websphere datasource after 10 (connection limit) the refresh 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 the provided connection, but that gives me an error:
J2CA0206W - A connection error has occurred. To help identify the problem, enable the Connection Diagnostics option in the Connection Factory or data source. This is the multithreaded access detection option. Alternatively, check for database or MessageProvider availability.
Any help would be appreciated.
source to share
You have to close the connection you got 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 are using Java 7 or better, you can simplify it:
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 don't, your connections will not be returned to the pool and you will end up with a connection.
source to share