关闭PreparedStatements(Closing PreparedStatements)

PreparedStatements和ResultSets的使用是否在每次使用时都创建一个“新数据库实例”？换句话说，如果我使用PreparedStatement和ResultSet，我应该在每次使用后还是结束后关闭它们？

例：

while (...){ p = connection.prepareStatement(...); r = p.executeQuery(); while (r.next()) { .... } } // We close at the end. Or there are any other p and r still opened...? p.close(); r.close();

要么

while (...){ p = connection.prepareStatement(...); r = p.executeQuery(); while (r.next()) { .... } p.close(); r.close(); }

注意：当然我会尝试并正确关闭，这只是一个例子。

Does use of PreparedStatements and ResultSets creates a "new database instance" everytime they are used? Or, whith other words, if I use a PreparedStatement and a ResultSet, should I close them after every use or once I finish?

Example:

while (...){ p = connection.prepareStatement(...); r = p.executeQuery(); while (r.next()) { .... } } // We close at the end. Or there are any other p and r still opened...? p.close(); r.close();

OR

while (...){ p = connection.prepareStatement(...); r = p.executeQuery(); while (r.next()) { .... } p.close(); r.close(); }

NOTE: Of course I would use try and close properly, this is just an example.

最满意答案

你应该关闭你打开的每一个。当你创建一个准备好的语句或结果集时，数据库会为这些资源分配资源，关闭这些资源会告诉数据库释放这些资源（可能数据库会在超时后最终重新分配这些资源，但通过close调用可以让数据库知道它可以继续并清理）。你的第二个例子比较好，除了在准备好的语句之前关闭结果集。

所以包含try块的情况如下所示：

while (...){ PreparedStatement p = connection.prepareStatement(...); try { ResultSet r = p.executeQuery(); try { while (r.next()) { .... } } finally { try { r.close(); } catch (SQLException e) { // log this or something -- prevent these from masking original exception } } } finally { try { p.close(); } catch (SQLException e) { // log this or something -- prevent these from masking original exception } } }

从关闭中捕获异常很丑陋，但如果在执行准备语句期间或在遍历结果集期间抛出异常，则需要确保看到该异常，而不是在关闭准备时抛出异常语句或结果集（这是由于一些网络故障，你无法做任何事情）。

另外请注意，使用try-with-resources将会起作用，除非如果你有一个数据库操作成功的情况，但是调用close关闭的结果是一个异常，那么异常将被抛出。

我建议人们使用spring-jdbc库（它可以为你处理所有的事情），而不是用手动启动iffy或verbose jdbc。

You should close every one you open. When you create a prepared statement or result set the database allocates resources for those, and closing them tells the database to free those resources (it's likely the database will reallocate these resources eventually after a timeout period, but calling close lets the database know it can go ahead and clean up). Your second example is better, except I'd close the result set before the prepared statement.

So with try blocks included it would look like:

while (...){ PreparedStatement p = connection.prepareStatement(...); try { ResultSet r = p.executeQuery(); try { while (r.next()) { .... } } finally { try { r.close(); } catch (SQLException e) { // log this or something -- prevent these from masking original exception } } } finally { try { p.close(); } catch (SQLException e) { // log this or something -- prevent these from masking original exception } } }

Catching the exceptions from the close is ugly, but if you have an exception thrown during execution of the prepared statement, or during traversal of the result set, you want to make sure that you see it, and not an exception thrown when closing the prepared statement or result set (which is due to some network glitch you can't do anything about anyway).

Also be aware that using try-with-resources will work, except that if you have a case where the database operation succeeds but calling close results in an exception then the exception will get thrown.

I recommend people use the spring-jdbc library (which handles closing everything for you) instead of cranking out iffy or verbose jdbc by hand.

更多推荐