How to properly clean up JDBC resources in Java?
What is considered best practices when cleaning up JDBC resources and why? I kept the example short, thus just the cleaning up of the ResultSet.
finally
{
if(rs != null)
try{ rs.close(); } catch(SQLException ignored) {}
}
versus
finally
{
try{ rs.close(); } catch(Exception ignored) {}
}
Personally I favour the second option since开发者_StackOverflow社区 it is a bit shorter. Any input on this is much appreciated.
Nowadays JDK 7 gives you the easiest option to clean up resources:
String query = "select COF_NAME, PRICE from COFFEES";
try (Statement stmt = con.createStatement()) {
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String coffeeName = rs.getString("COF_NAME");
float price = rs.getFloat("PRICE");
System.out.println(coffeeName + ", " + price);
}
}
The try statement ensures that each resource is closed at the end of the statement. See http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html
As others have pointed out, JDBC resources (statements, result sets, etc...) are rarely null
. If they are, you have bigger issues on your hands than NullPointerException
s. In that regard, the NullPointerException
s will help alert you to severe problems with your JDBC driver. The typical checking for null
before calling close()
would silently hide the problem if your JDBC driver was, in fact, providing you with null
references.
As well, not all JDBC drivers follow the specification precisely. For example, some drivers will not automatically close a ResultSet
when it's associated Statement
is closed. Therefore, you have to ensure that you explicitly close both the ResultSet
and its Statement
(sigh).
In practice, I have found this technique useful (although its not the prettiest):
PreparedStatement statement = connection.prepareStatement("...");
try {
ResultSet results = statement.executeQuery();
try {
while (results.next()) {
// ...
}
} finally {
results.close();
}
} finally {
statement.close();
}
This technique guarantees that every close()
statement is executed, starting with the ResultSet
and working its way outward. NullPointerException
s are still thrown should the driver provide you with null
references, but I allow this for the reasons explained at the beginning. SQLException
s are still thrown if any of the close()
statements fail (I consider this a good thing - I want to know if something is going wrong).
I see no problem with your second (uncommon) version.
- usually, rs will not be
null
, so an NPE will occur in rare cases. So I see no performance problem here. - both version behave exactly the same in case of
rs = null
The only disadvantage - if we have more then one resource to close, then we'd have to add one try/catch for each resource, if we want to close as many resources as possible. Otherwise, we'd enter the catch clause with the first null
and that could cause undiscored leaks.
So it would look like that:
finally {
try{rs.close(); }catch(Exception ignored){}
try{stmt.close();}catch(Exception ignored){}
try{conn.close();}catch(Exception ignored){}
}
... which is still readable and understandable. But, according to never change a common pattern - I'd stick to the old-fashioned way of testing null
first and catching SQLException
while closing.
I tend to use the following approach. I think it is good to check for null
because it shows your intent i.e. that you do realise that these objects could be null in rare cases. (A null check is also faster than the creation of a NullPointerException
.) I also think it is good to log the exceptions, instead of swallowing them. In the cases where close
fails, I want to know about it and have it in my log files.
finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
LOG.warn("Failed to close rs", e);
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
LOG.warn("Failed to close st", e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
LOG.warn("Failed to close conn", e);
}
}
}
If you are going to be doing this frequently, instead of copying and pasting this code over and over again, create a utility class with static methods to close the ResultSet, Statement and Connection.
With DBUtils you can perform this cleanup quite concisely as follows:
finally {
DBUtils.closeQuietly(rs);
DBUtils.closeQuietly(st);
DBUtils.closeQuietly(conn);
}
public static void close(Statement... statements) {
for (Statement stmt : statements) {
try {
if (stmt != null)
stmt.close();
} catch (SQLException se) {
}// nothing we can do
}
}
public static void close(Connection conn) {
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
}// nothing we can do
}
public static void close(ResultSet rs) {
try {
if (rs != null)
rs.close();
} catch (SQLException se) {
}// nothing we can do
}
This is my approach for JDK 6. If you have JDK 7+ you better use the approach I describe here https://stackoverflow.com/a/9200053/259237
private void querySomething() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
// get connection
// prepare statement
// execute query
// and so on
} catch (SQLException e) {
throw new MyException("Error while talking to database", e);
} finally {
close(connection, statement, rs);
}
}
// useful because you probably have more than one method interacting with database
public static void close (Connection connection, Statement statement, ResultSet rs) {
if (rs != null) {
try { rs.close(); } catch (Exception e) { _logger.warning(e.toString()); }
}
if (statement != null) {
try { statement.close(); } catch (Exception e) { _logger.warning(e.toString()); }
}
if (connection != null) {
try { connection.close(); } catch (Exception e) { _logger.warning(e.toString()); }
}
}
- It's short.
- It defines a close method that can be statically imported.
- It avoids empty catch blocks.
- It handles any SQLException that may occur (even in getConnection or close methods).
- It's null-safe.
ResultSet rs = //initialize here
try {
// do stuff here
} finally {
try { rs.close(); }
catch(SQLException ignored) {}
}
If you are writing a long running application you should consider connection pooling.
The Apache DBCP project does a lot of this work for you. You could also look at something like Spring JDBC or Hibernate as well.
The Spring stuff uses object pooling and adds some really nice methods for abstracting away JDBC nastiness.
protected void closeAll(){
closeResultSet();
closeStatement();
closeConnection();
}
protected void closeConnection(){
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
/*Logger*/
}
connection = null;
}
}
protected void closeStatement() {
if (stmt != null) {
try {
ocstmt.close();
} catch (SQLException e) {
/*Logger*/
}
ocstmt = null;
}
}
protected void closeResultSet() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
/*Logger*/
}
rs = null;
}
}
精彩评论