oracle - 无法关闭来自 WebSphere 数据源的 Oracle 连接

Question

我尝试连接到 Oracle 数据库(检查连接状态)。我正在使用以下代码，效果很好。

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";
}

但是，当使用 Websphere 数据源时，在 10(连接限制)刷新后页面挂起。代码:

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";
}

我试图关闭提供的连接，但它给我错误:

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.

我们将不胜感激。

Answer 1

您必须关闭从数据源收到的连接:

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);
    }
}

如果您使用的是 Java 7 或更高版本，您可以将其简化为:

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";
}

如果你不这样做，你的连接将不会返回到池中，你将用完连接。