在Java中处理SQL连接池清理和错误处理的正确方法是什么？

I have been learning about using MySQL within Java using Oracle JDBC and I am trying to get into the mindset of try/catch and pool cleanup.

我一直在学习如何使用Oracle JDBC在Java中使用MySQL,我试图进入try / catch和pool cleanup的思维模式。

I am wondering if the following code is the correct way to perfectly clean everything up or if you notice holes in my code that requires something I've missed. For the record, I intend to use InnoDB and its row locking mechanism which is why I turn auto commit off.

我想知道以下代码是否是正确清理所有内容的正确方法,或者如果您发现我的代码中存在需要我错过的内容的漏洞。为了记录,我打算使用InnoDB及其行锁定机制,这就是我关闭自动提交的原因。

try
{
    connection = getConnection(); // obtains Connection from a pool

    connection.setAutoCommit(false);

    // do mysql stuff here
}
catch(SQLException e)
{
    if(connection != null)
    {
        try
        {
            connection.rollback(); // undo any changes
        }
        catch (SQLException e1)
        {
            this.trace(ExtensionLogLevel.ERROR, e1.getMessage());
        }
    }
}
finally
{
    if(connection != null)
    {
        try
        {
            if(!connection.isClosed())
            {
                connection.close(); // free up Connection so others using the connection pool can make use of this object
            }
        }
        catch (SQLException e)
        {
            this.trace(ExtensionLogLevel.ERROR, e.getMessage());
        }
    }
}

getConnection() returns a Connection object from a pool and connection.close() closes it releasing it back to the pool (so I've been told, still new to this so apologies if I am talking rubbish). Any help on any of this would be greatly appreciated!

getConnection()从池中返回一个Connection对象,并且connection.close()关闭它,将它释放回池中(所以我被告知,如果我说的是垃圾,我仍然是新的,所以道歉)。任何这方面的任何帮助将不胜感激!

Thank you!

1 个解决方案

#1

I recommend not setting autocommit back to true in the finally block - your other threads that are relying on autocommit being set to true should not assume that the connections in the pool are in this state, but instead they should set autocommit to true before using a connection (just as this thread is setting autocommit to false).

我建议不要在finally块中将autocommit设置为true - 依赖于autocommit的其他线程设置为true不应该假设池中的连接处于此状态,而是应该在使用之前将autocommit设置为true连接(正如此线程将autocommit设置为false)。

In addition, you should check the connection's isClosed property before calling close() on it.

此外,您应该在调用close()之前检查连接的isClosed属性。

Other than that, I don't see any problems.

除此之外,我没有看到任何问题。

#1