java 我想返回 ResultSet 时在哪里关闭 JDBC 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1910049/
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
Where to close a JDBC Connection while I want to return the ResultSet
提问by Aloong
It seems that the ResultSetwill be automatically closed when I close the Connection.
But I want to return the ResultSetand use it in another method, then I don't know where to close Connectionand PreparedStatement.
看来，ResultSet将当我关闭自动关闭Connection。但是我想返回ResultSet并在另一种方法中使用它，然后我不知道在哪里关闭Connection和PreparedStatement。
public ResultSet executeQuery(String sql, String[] getValue)
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
conn = getConn();
pstmt = conn.prepareStatement(sql);
if (getValue != null)
{
for (int i = 0; i < getValue.length; i++)
{
pstmt.setString(i + 1, getValue[i]);
}
}
rs = pstmt.executeQuery();
} catch (Exception e)
{
e.printStackTrace();
closeAll(conn, pstmt, rs);
}
return rs;
}
I've moved closeAll(conn, pstmt, null);into catch block because I found that if I put it in finally block I'll lost my rsimmediately just before it returns.
Now when I want to close the rs, I can't close the connand pstmt. Is there any solution?
我已经closeAll(conn, pstmt, null);进入了 catch 块，因为我发现如果我把它放在 finally 块中，我会rs在它返回之前立即丢失我的。现在，当我想关闭 时rs，我无法关闭conn和pstmt。有什么解决办法吗？
回答by Mirek Pluta
Use CachedRowSetfor holding info after disconnecting
使用CachedRowSet用于断开后持有信息
Connection con = ...
ResultSet rs = ...
CachedRowSet rowset = new CachedRowSetImpl();
rowset.populate(rs);
con.close()
回答by z5h
One clean way of coding this is to pass in an object that has a callback method that takes a result set.
一种干净的编码方式是传入一个对象，该对象具有一个接受结果集的回调方法。
Your other method creates the object with the callback method with it's resultSet handling code, and passes that to the method that executes the SQL.
您的另一个方法使用带有 resultSet 处理代码的回调方法创建对象，并将其传递给执行 SQL 的方法。
That way, your SQL & DB code stays where it belongs, your result set handling logic is closer to where you use the data, and your SQL code cleans up when it should.
这样，您的 SQL 和 DB 代码会留在它所属的位置，您的结果集处理逻辑更接近您使用数据的位置，并且您的 SQL 代码会在应该清理的时候进行清理。
interface ResultSetCallBack{
void handleResultSet(ResultSet r);
}
void executeQuery(..., ResultSetCallBack cb){
//get resultSet r ...
cb.handleResultSet(r);
//close connection
}
void printReport(){
executeQuery(..., new ResultSetCallBack(){
public void handleResultSet(ResultSet r) {
//do stuff with r here
}
});
}
回答by BalusC
You should neverpass ResultSet(or Statementor Connection) into the public outside the method block where they are to be acquired andclosed to avoid resource leaks. A common practice is just to map the ResultSetto a List<Data>where Datais just a javabean object representing the data of interest.
你应该从来没有通过ResultSet（或Statement或Connection）进入公众的外部方法块，他们是被收购并关闭以避免资源泄漏。通常的做法是只映射ResultSet到一个List<Data>地方Data只是表示感兴趣的数据JavaBean对象。
Here's a basic example:
这是一个基本示例：
public class Data {
private Long id;
private String name;
private Integer value;
// Add/generate public getters + setters.
}
and here's a basic example of how to handle it correctly:
这是如何正确处理它的基本示例：
public List<Data> list() throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Data> list = new ArrayList<Data>();
try {
connection = database.getConnection();
statement = connection.prepareStatement("SELECT id, name, value FROM data");
resultSet = statement.executeQuery();
while (resultSet.next()) {
Data data = new Data();
data.setId(resultSet.getLong("id"));
data.setName(resultSet.getString("name"));
data.setValue(resultSet.getInt("value"));
list.add(data);
}
} finally {
if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}
return list;
}
you can use it as follows:
您可以按如下方式使用它：
List<Data> list = dataDAO.list();
To learn more about the best practices with JDBC you may find this basic kickoff articleuseful as well.
要了解有关 JDBC 最佳实践的更多信息，您可能会发现这篇基本启动文章也很有用。
回答by Will Hartung
You can call ResultSet.getStatementto retrieve the Statement, and Statement.getConnectionto retrieve the Connection.
您可以调用ResultSet.getStatement以检索Statement，并Statement.getConnection检索Connection。
From these you can write a closeResultSetutility method that will close all 3 for you, given nothing but the ResultSet.
从这些中，您可以编写一个closeResultSet实用程序方法，该方法将为您关闭所有 3 个，除了ResultSet.
回答by Alexander Pogrebnyak
You can't use ResultSetafter you've closed Connectionand/or PreparedStatement.
So, you need to pass an object on which to make a callback into this method.
你不能使用ResultSet你已经关闭后Connection和/或PreparedStatement。因此，您需要将一个对象传递给该方法以进行回调。
All cleanup should be done in finallyblocks.
所有的清理工作都应该在finally块中完成。
Rewrite it like this
改写成这样
public ResultSet executeQuery(
String sql,
String[] getValue,
CallbackObj cbObj
) throws SQLException
{
final Connection conn = getConn( );
try
{
final PreparedStatement pstmt = conn.prepareStatement(sql);
try
{
if (getValue != null)
{
for (int i = 0; i < getValue.length; i++)
{
pstmt.setString(i + 1, getValue[i]);
}
}
final ResultSet rs = pstmt.executeQuery();
try
{
cbObj.processResultSet( rs );
}
finally
{
// You may want to handle SQLException
// declared by close
rs.close( );
}
}
finally
{
// You may want to handle SQLException
// declared by close
pstmt.close( );
}
}
finally
{
// You may want to handle SQLException
// declared by close
conn.close( );
}
}
回答by Andy Gherna
The way you have it right now, the connection would never close which would cause problems later (if not immediately) for your program and the RDBMS. It would be better to create a Java class to hold the fields from the ResultSet and return that. The ResultSet is linked to the connection, so returning it and closing the connection is not possible.
您现在拥有的方式，连接永远不会关闭，这会在以后（如果不是立即）导致您的程序和 RDBMS 出现问题。最好创建一个 Java 类来保存 ResultSet 中的字段并返回它。ResultSet 链接到连接，因此返回它并关闭连接是不可能的。
回答by Pascal Thivent
Where to close a JDBC Connection while I want to return the ResultSet
我想返回 ResultSet 时在哪里关闭 JDBC 连接
Actually, you've almost answered that question yourself. As you experimented, closing the Connectionwill release the JDBC resources associated to it (at least, this is how things should work). So, if you want to return a ResultSet(I'll come back on this later), you need to close the connection "later". One way to do this would be obviously to pass a connection to your method, something like this:
事实上，你自己几乎已经回答了这个问题。正如您所试验的那样，关闭Connection将释放与其关联的 JDBC 资源（至少，事情应该这样工作）。所以，如果你想返回一个ResultSet（我稍后会回来），你需要“稍后”关闭连接。执行此操作的一种方法显然是将连接传递给您的方法，如下所示：
public ResultSet executeQuery(Connection conn, String sql, String[] getValue);
The problem is that I don't really know what is your final goal and why you need so low level stuff so I'm not sure this is a good advice. Unless if you are writing a low level JDBC framework (and please, don't tell me you are not doing this), I would actually not recommend returning a ResultSet. For example, if you want to feed some business class, return some JDBC-independent object or a collection of them as other have advised instead of a ResultSet. Also bear in mind that a RowSetisa ResultSetso if you should not use a ResultSetthen you should not use a RowSet.
问题是我真的不知道你的最终目标是什么，为什么你需要这么低级的东西，所以我不确定这是一个很好的建议。除非您正在编写一个低级 JDBC 框架（并且请不要告诉我您没有这样做），否则我实际上不建议返回ResultSet. 例如，如果你想提供一些业务类，返回一些与 JDBC 无关的对象或它们的集合，而不是像其他人建议的那样返回ResultSet. 还要记住， a是a所以如果你不应该使用 a那么你就不应该使用 a 。RowSetResultSetResultSetRowSet
Personally, I think you should use some helper class instead of reinventing the wheel. While Spring may be overkill and has a bit of learning curve (too much if you don't know it at all), Spring is not the only way to go and I strongly suggest to look at Commons DbUtils. More specifically, look at QueryRunnerand especially this query()method:
就个人而言，我认为您应该使用一些辅助类而不是重新发明轮子。虽然 Spring 可能有点矫枉过正，并且有一些学习曲线（如果你根本不知道，那就太多了），但 Spring 并不是唯一的出路，我强烈建议查看Commons DbUtils。更具体地说，看看QueryRunner特别是这个query()方法：
public <T> T query(String sql,
ResultSetHandler<T> rsh,
Object... params)
throws SQLException
As you can see, this method allows to pass a ResultSetHandlerwhich exposes a callback method to convert ResultSetsinto other objects as described in z5h's answerand DbUtils provides several implementations, just pick up the one that will suit your needs. Also have a look at the utility methods of the DbUtilsclass, for example the various DbUnit.close()that you may find handy to close JDBC resources.
如您所见，此方法允许传递一个ResultSetHandler公开回调方法以转换ResultSets为其他对象的方法，如z5h 的回答中所述，并且 DbUtils 提供了多种实现，只需选择适合您需求的实现即可。还可以查看DbUtils该类的实用程序方法，例如DbUnit.close()您可能会发现关闭 JDBC 资源很方便的各种方法。
Really, unless you have very good reasons to do so (and I'd be curious to know them), don't write yet another JDBC framework, use an existing solution, it will save you some pain and, more important, some bugs and you'll benefit from proven good design. Even for low level stuff, there are existing (and simple) solutions as we saw. At least, check it out.
真的，除非你有很好的理由这样做（我很想知道他们），不要再写另一个 JDBC 框架，使用现有的解决方案，它会为你节省一些痛苦，更重要的是，一些错误并且您将从久经考验的良好设计中受益。即使对于低级的东西，也有我们看到的现有（和简单）的解决方案。至少，检查一下。
回答by Kai Burghardt
The cleaner way is to use CachedRowSetImpl. But on MySQL 5.x+ there are some bugs with selecting columns by name or label.
更简洁的方法是使用CachedRowSetImpl。但是在 MySQL 5.x+ 上，按名称或标签选择列存在一些错误。
For use with MySQL use this version: https://stackoverflow.com/a/17399059/1978096
要与 MySQL 一起使用，请使用此版本：https: //stackoverflow.com/a/17399059/1978096
回答by tangens
回答by duffymo
I'd recommend that you do something more like this:
我建议你做更多这样的事情：
public List<Map> executeQuery(Connection connection, String sql) throws SQLException
{
List<Map> rows = new ArrayList<Map>();
PreparedStatement stmt = null;
ResultSet rs = null;
try
{
pstmt = conn.prepareStatement(sql);
rs = stmt.execute();
int numColumns = rs.getMetaData().getColumnCount();
while (rs.next())
{
Map<String, Object> row = new LinkedHashMap<String, Object>();
for (int i = 0; i < numColumns; ++i)
{
String column = rs.getColumnName(i+1);
Object value = rs.getObject(i+1);
row.put(column, value);
}
rows.add(row);
}
}
finally
{
close(rs);
close(stmt);
}
return rows;
}
public static void close(Statement s)
{
try
{
if (s != null)
{
s.close();
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
public static void close(ResultSet rs)
{
try
{
if (rs != null)
{
rs.close();
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
