尝试/尝试使用资源并关闭连接，语句和结果集 [英] Try / Try-with-resources and Connection, Statement and ResultSet closing
问题描述
我最近与我的教授讨论了如何处理基本的jdbc连接方案.假设我们要执行两个查询，这就是他的建议
I have recently having some discussions with my professor about how to handle the basic jdbc connection scheme. Suppose we want to execute two queries, this is what he proposes
public void doQueries() throws MyException{
Connection con = null;
try {
con = DriverManager.getConnection(dataSource);
PreparedStatement s1 = con.prepareStatement(updateSqlQuery);
PreparedStatement s2 = con.prepareStatement(selectSqlQuery);
// Set the parameters of the PreparedStatements and maybe do other things
s1.executeUpdate();
ResultSet rs = s2.executeQuery();
rs.close();
s2.close();
s1.close();
} catch (SQLException e) {
throw new MyException(e);
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException e2) {
// Can't really do anything
}
}
}
我不喜欢这种方法，对此我有两个问题:
I don't like this approach, and I have two questions about it:
1.A)我认为，如果在执行其他操作"的地方或行rs.close()或s2.close()中抛出任何异常，则在方法结束时不会关闭s1.我说得对吗?
1.A) I think that, if any exception is thrown where we do 'other things', or in the line rs.close() or s2.close() then s1 wouldn't have been closed when the method ends. Am I right about that?
1.B)教授一直要求我明确关闭ResultSet(即使Statement文档明确表明它将关闭ResultSet)她说Sun建议这样做.有什么理由吗?
1.B) The professor keeps asking me to explicitly close the ResultSet (even when the Statement documentation makes clear that it will close the ResultSet) She says that Sun recommends it. Is there any reason to do so?
现在这是我认为适用于同一件事的正确代码:
Now this is what I think is the correct code for the same thing:
public void doQueries() throws MyException{
Connection con = null;
PreparedStatement s1 = null;
PreparedStatement s2 = null;
try {
con = DriverManager.getConnection(dataSource);
s1 = con.prepareStatement(updateSqlQuery);
s2 = con.prepareStatement(selectSqlQuery);
// Set the parameters of the PreparedStatements and maybe do other things
s1.executeUpdate();
ResultSet rs = s2.executeQuery();
} catch (SQLException e) {
throw new MyException(e);
} finally {
try {
if (s2 != null) {
s2.close();
}
} catch (SQLException e3) {
// Can't do nothing
}
try {
if (s1 != null) {
s1.close();
}
} catch (SQLException e3) {
// Can't do nothing
}
try {
if (con != null) {
con.close();
}
} catch (SQLException e2) {
// Can't do nothing
}
}
}
2.A)此代码正确吗? (是否保证在方法结束时全部关闭?)
2.A) Is this code correct? (Is it guaranteed that all will be closed when the method ends?)
2.B)这是非常庞大且冗长的(如果有更多的语句，它会变得更糟)是否有任何更短或更优雅的方法来执行此操作而不使用try-with-resources?
2.B) This is very large and verbose (and it gets worse if there are more Statements) Is there any shorter or more elegant way to do this without using try-with-resources?
最后这是我最喜欢的代码
Finally this is the code I like the most
public void doQueries() throws MyException{
try (Connection con = DriverManager.getConnection(dataSource);
PreparedStatement s1 = con.prepareStatement(updateSqlQuery);
PreparedStatement s2 = con.prepareStatement(selectSqlQuery))
{
// Set the parameters of the PreparedStatements and maybe do other things
s1.executeUpdate();
ResultSet rs = s2.executeQuery();
} catch (SQLException e) {
throw new MyException(e);
}
}
3)此代码正确吗?我认为我的教授不喜欢这种方式，因为ResultSet没有显式关闭，但是她告诉我只要在文档中清楚所有都已关闭就可以了.您能否提供带有类似示例的官方文档链接，或者基于文档显示此代码没有问题?
3) Is this code correct? I think my professor doesn't like this way because there is no explicit close of the ResultSet, but she has told me that she is fine with it as long as in the documentation it is clear that all is closed. Can you give any link to the official documentation with a similar example, or based in the documentation show that there is are no problems with this code?
推荐答案
tl; dr
- 理论上，关闭语句会关闭结果集.
- 实际上，众所周知，某些错误的JDBC驱动程序实现未能做到这一点.因此，您的指导老师的建议是她从硬敲门学校"中学到的.除非您熟悉可能为您的应用程序部署的每个 JDBC驱动程序的每种实现，否则请使用
尝试资源以自动关闭 JDBC 工作，例如语句和结果集. - In theory closing the statement closes the result set.
- In practice, some faulty JDBC driver implementations failed to do so, notoriously. Thus the advice from your instructor that she learned from the School Of Hard Knocks. Unless you are familiar with every implementation of every JDBC driver that might be deployed for your app, use try-with-resources to auto-close every level of your JDBC work such as statements and result sets.
tl;dr
没有一个代码是完全使用 try- with-resources .在try-with-resources语法中，您在括号之前声明并实例化Connection，PreparedStatement和ResultSet.请参见 Oracle教程.
None of your code is fully using try-with-resources. In try-with-resources syntax, you declare and instantiate your Connection, PreparedStatement, and ResultSet in parentheses, before the braces. See Tutorial by Oracle.
虽然您的ResultSet在上一个代码示例中未明确关闭，但应该在其语句关闭时间接关闭.但是如下所述，由于错误的JDBC驱动程序，它可能不被关闭.
While your ResultSet is not being explicitly closed in your last code example, it should be closed indirectly when its statement is closed. But as discussed below, it might not be closed because of faulty JDBC driver.
Any such objects implementing AutoCloseable will automatically have their close method invoked. So no need for those finally clauses.
对于阅读本文的人文科学专业的人来说，是的，Java团队拼写了"closesable".
For the Humanities-majors reading this, yes, the Java team misspelled "closable".
您如何知道哪些对象是自动关闭的，哪些不是?查看他们的课程文档，看看它是否声明 AutoCloseable 作为超级接口.相反，请参见 AutoCloseable的JavaDoc页面获取所有捆绑的子接口和实现类的列表(实际上是几十个).
How do you know which objects are auto-closable and which are not? Look at their class documentation to see if it declares AutoCloseable as a super-interface. Conversely, see the JavaDoc page for AutoCloseable for a list of all the bundled sub-interfaces and implementing classes (dozens actually).
For example, for SQL work, we see that Connection, Statement, PreparedStatement, ResultSet, and RowSet are all auto-closable but DataSource is not. This makes sense, as DataSource stores data about potential resources (database connections) but is not itself a resource. A DataSource is never "open" so no need to close.
请参见Oracle教程， try-with-resources语句 .
See Oracle Tutorial, The try-with-resources Statement.
您的最后一个代码示例已接近完成，但应该将ResultSet包装在try-with-resources语句中，以自动关闭.
Your last code example is getting close to good, but should have wrapped ResultSet in a try-with-resources statement to get automatically closed.
引用 ResultSet JavaDoc :
To quote ResultSet JavaDoc:
当ResultSet对象关闭，重新执行或用于从多个结果序列中检索下一个结果时，将自动关闭ResultSet对象.
A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.
正如您的老师所建议的那样，某些JDBC驱动程序中存在严重的缺陷，未能遵循JDBC规范的承诺，即当ResultSet或PreparedStatement关闭时，它会关闭ResultSet.因此，许多程序员习惯于显式关闭每个ResultSet对象.
As your teacher has been suggesting, there have been serious flaws in some JDBC drivers that failed to live up to the promise of the JDBC spec to close the ResultSet when its Statement or PreparedStatement is closed. So many programmers make a habit of closing each ResultSet object explicitly.
使用try-with-resources语法现在可以轻松完成此额外任务.在实际工作中，无论如何，您都可能会对所有AutoCloseable对象(例如ResultSet)进行尝试.因此，我个人的观点是:为什么不将其设为 try-with-resources + else ?不会造成伤害，使您的代码更能自动记录您的意图，并且，如果您的代码遇到错误的JDBC驱动程序之一，这可能会帮助您. 唯一的花费是一对parens ，前提是您仍然要尝试try-catch-else.
This extra duty is easier now with the try-with-resources syntax. In real work you’ll likely have a try-else around all your AutoCloseable objects such as ResultSet anyways. So my own opinion is: Why not make it a try-with-resources + else? Does not hurt, makes your code more self-documenting about your intentions, and it might help if your code ever encounters one of those faulty JDBC drivers. The only cost is a pair of parens, assuming you’d have a try-catch-else in place anyways.
如 Oracle教程所述，多个
提示:try-with-resources语法允许在最后声明的资源项上使用可选的分号.我将分号作为一种习惯，因为它在我看来很好看，很一致，并且便于剪切和粘贴编辑.我将其包含在您的PreparedStatement s2行中.
Tip: The try-with-resources syntax allows an optional semicolon on the last declared resource item. I include the semicolon as a habit because it reads well to my eye, is consistent, and facilitates cut-and-paste editing. I include it on your PreparedStatement s2 line.
public void doQueries() throws MyException{
// First try-with-resources.
try ( Connection con = DriverManager.getConnection( dataSource ) ;
PreparedStatement s1 = con.prepareStatement( updateSqlQuery ) ;
PreparedStatement s2 = con.prepareStatement( selectSqlQuery ) ;
) {
… Set parameters of PreparedStatements, etc.
s1.executeUpdate() ;
// Second try-with-resources, nested within first.
try (
ResultSet rs = s2.executeQuery() ;
) {
… process ResultSet
} catch ( SQLException e2 ) {
… handle exception related to ResultSet.
}
} catch ( SQLException e ) {
… handle exception related to Connection or PreparedStatements.
}
}
我认为这种工作可能有一种更优雅的语法，可能会在将来的编程语言中发明.但是现在，我们有try-with-resources，我确实很乐意使用它.虽然try-with-resources并不完美，但它是对较旧语法的很大改进.
I suppose there is a more elegant syntax for this kind of work that might be invented in a future programming language. But for now, we have try-with-resources, and I do use it happily. While try-with-resources is not perfectly elegant, it is a big improvement over the older syntax.
顺便说一句，Oracle建议使用 DataSource 实现用于获取连接，而不是
By the way, Oracle recommends using a DataSource implementation for getting connections rather than the DriverManager approach seen in your code. Using DataSource throughout your code makes it easier to switch drivers or switch to a connection pool. See if your JDBC driver provides an implementation of DataSource.
现在在Java 9中，您可以在try-with-resources之前 初始化资源.请参阅本文.在某些情况下，这种灵活性可能很有用.
Now in Java 9 you can initialize the resources before the try-with-resources. See this article. This flexibility may be useful in some scenarios.
这篇关于尝试/尝试使用资源并关闭连接，语句和结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
