使用一个连接执行两个 Java PreparedStatements - 样式选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6486680/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Executing two Java PreparedStatements with one connection - style choice
提问by Kevin Jin
Okay, I've realized that I really have asked way too many questions without contributing back to the community, but I want your opinions on this. Say if I have
好的，我意识到我真的问了太多问题而没有回馈社区，但我希望您对此发表意见。说如果我有
private void closeAll(ResultSet rs, PreparedStatement ps, Connection con) {
if (rs != null)
try {
rs.close();
} catch (SQLException e) {
}
if (ps != null)
try {
ps.close();
} catch (SQLException e) {
}
if (con != null)
try {
con.close();
} catch (SQLException e) {
}
}
and I wanted to do several operations on my MySQL database using a single connection. Is it better to write
我想使用单个连接对我的 MySQL 数据库执行多项操作。是不是更好写
Connection con = ...;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = con.prepareStatement(...);
rs = ps.executeQuery();
if (rs.next()) ...;
} catch (SQLException e) {
System.err.println("Error: " + e);
} finally {
closeAll(rs, ps, null);
}
try {
ps = con.prepareStatement(...);
rs = ps.executeQuery();
if (rs.next()) ...;
} catch (SQLException e) {
System.err.println("Error: " + e);
} finally {
closeAll(rs, ps, con);
}
or
或者
Connection con = ...;
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = con.prepareStatement(...);
rs = ps.executeQuery();
if (rs.next()) ...;
rs.close();
ps.close();
ps = con.prepareStatement(...);
rs = ps.executeQuery();
if (rs.next()) ...;
} catch (SQLException e) {
System.err.println("Error: " + e);
} finally {
closeAll(rs, ps, con);
}
I consider better to mean either safer, clearer, more concise, or more robust. I'm not sure whether the latter will always close whichever prepared statements and result sets are open whenever it encounters an exception, while I believe it does look more concise. But the former looks nicer since it's more consistent, yet it puts more overhead since it uses more try finally blocks.
我认为更好的意思是更安全、更清晰、更简洁或更健壮。我不确定后者是否会在遇到异常时始终关闭任何准备好的语句和结果集，但我相信它确实看起来更简洁。但前者看起来更好，因为它更一致，但它带来了更多的开销，因为它使用了更多的 try finally 块。
I realize that Java 7's automatic resource management part of Project Coin will force me to lean to the former since the resources used in the header are implicitly final in the body. However, I have quite some time before I have to worry about revising my code to adapt it to ARM and be able to remove the boilerplate code, so the question still stands: of the above two styles, which would be better practice? If they both do the expected behaviors, will the latter give me a noticeable performance boost that would excuse the "uglier" style?
我意识到 Project Coin 的 Java 7 的自动资源管理部分将迫使我倾向于前者，因为标题中使用的资源在正文中隐含地是最终的。但是，在我不得不担心修改我的代码以使其适应 ARM 并能够删除样板代码之前，我有相当长的时间，所以问题仍然存在：在上述两种风格中，哪种做法更好？如果他们都做了预期的行为，后者是否会给我一个显着的性能提升，从而为“丑陋”的风格找借口？
采纳答案by Daniel Hollinrake
It seems to me this is a case of personal preference. In my time I've written code that resembles both blocks. As regards performance I don't think there would be a particularly noticeable difference, only performance tests would tell.
在我看来，这是个人喜好的情况。在我的时代，我编写了类似于这两个块的代码。至于性能，我认为不会有特别明显的差异，只有性能测试才能说明问题。
It might be a case that the millisecond or so difference that one version delivers isn't half as important as the ten minutes or so that another person reading your code six months after you've written it would spend asking why.
在这种情况下，一个版本提供的毫秒左右的差异不如十分钟左右重要，以至于在您编写代码六个月后阅读您的代码的另一个人会花时间问为什么。
My personal preference would be the second. You say you're holding a connection open to the database. With the first block of code if you get an exception thrown, that would be handled but then you'd drop down to the second try/catch and try again. You might not want that if the first one failed. With the second, an exception would cause you come out of the code and then close your connection.
我个人的偏好是第二个。你说你正在保持一个与数据库的连接。对于第一个代码块，如果您抛出异常，则会处理该代码块，但随后您将下降到第二个 try/catch 并重试。如果第一个失败，您可能不希望这样。对于第二个，异常会导致您退出代码，然后关闭您的连接。
I've programmed mainly in C#. It was about eight years ago when I last did any Java. But I think there is and have been plenty of C# programmers who've pondered this. I have at any rate.
我主要用 C# 编程。我上次使用 Java 是在大约八年前。但我认为已经有很多 C# 程序员考虑过这个问题。无论如何我都有。
回答by mvmn
If you look at it, you'll see that logic is somewhat different - first version will execute second query even if first query handling will fail. Second version will only proceed to executing second query, if handling first query+results will succeed.
如果您查看它，您会发现逻辑有些不同 - 即使第一个查询处理失败，第一个版本也会执行第二个查询。如果处理第一个查询+结果会成功，第二个版本只会继续执行第二个查询。
回答by Fabian Barney
The 2nd one is the way to go. The first one may fail to close the connection when an exception occurs in first try-block. When you just do printing to stderr in the catch-block then not, but you won't do that. Or should the 2nd statement be executed without respect to success/failure of first one?
第二个是要走的路。当第一个 try-block 中发生异常时，第一个可能无法关闭连接。当您只是在 catch 块中打印到 stderr 时，则不会，但您不会那样做。还是应该在不考虑第一个语句的成功/失败的情况下执行第二个语句？
回答by avanderw
I realise that this post is old, but if you are lucky enough to be running in a Java7 environment- I would suggest using the try-with-resource.
我意识到这篇文章很旧，但如果你有幸在 Java7 环境中运行 - 我建议使用try-with-resource。
It will handle the connection closing for you- as the new versions of the class implement the Closableinterface.
它将为您处理连接关闭 - 因为该类的新版本实现了Closable接口。
public static void viewTable(Connection con) throws SQLException {
String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
try (Statement stmt = con.createStatement()) {
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String coffeeName = rs.getString("COF_NAME");
int supplierID = rs.getInt("SUP_ID");
float price = rs.getFloat("PRICE");
int sales = rs.getInt("SALES");
int total = rs.getInt("TOTAL");
System.out.println(coffeeName + ", " + supplierID + ", " +
price + ", " + sales + ", " + total);
}
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
}
}
