Java - Closing JDBC Connections - A Common Mistake

Where is the leak? Can someone explain?

Java programmers who have done some JDBC programming by now know that it's important to close the ResultSet, Statements and Connections. And the code looks like the sample below:

Connection conn = null;
ResultSet rs = null;
Statement stmt = null;
try
{
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
Connection conn = DriverManager.getConnection("jdbc:odbc:issue","x","x");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM TABLE" );
}
catch (SQLException e)
{
logger.error(e.getMessage(), e);
}
finally
{
try
{
if (rs != null)
rs.close();
}
catch (Exception e)
{
// it's a good idea to log it!
logger.error("Cannot close ResultSet", e);
}
try
{
if (stmt != null)
stmt.close();
}
catch (Exception e)
{
// it's a good idea to log it!
logger.error("Cannot close Statement", e);
}
try
{
if (conn != null)
conn.close();
}
catch (SQLException e)
{
// it's a good idea to log it!
logger.error("Cannot close Connection", e);
}
}

While the above example is accurate, having different methods with a common part "finally" would make one think... why not put the parts of the finally block into a common method to close the resources... So they begin writing a method that looks like this:

public static void closeResources(ResultSet rs, Statement stmt, Connection conn)
{
try
{
if (rs != null)
rs.close();
}
catch (Exception e)
{
// it's a good idea to log it!
logger.error("Cannot close ResultSet", e);
}
try
{
if (stmt != null)
stmt.close();
}
catch (Exception e)
{
// it's a good idea to log it!
logger.error("Cannot close Statement", e);
}
try
{
if (conn != null)
conn.close();
}
catch (SQLException e)
{
// it's a good idea to log it!
logger.error("Cannot close Connection", e);
}
}

and the finally block begins to look like this:

try
{
...
}
catch (Exception e)
{
...
}
finally
{
closeResources(rs, stmt, conn);
}

Is the second approach right or wrong???

Here is my reasoning...

I ran a test where I had a connection-pool and 2nd approach yielded connection leaks... and when I replaced my closeResources(...) method calls with actual try-catch block within my finally blocks to close the connections... the leaks disappeared... So can someone explain why that is the case...

Well, my first thought was this ... from here: http://www.javaworld.com/javaworld/javaqa/2000-05/03-qa-0526-pass.html


A common error made by Java language newcomers. Indeed, even seasoned veterans find it difficult to keep the terms straight.

Java does manipulate objects by reference, and all object variables are references. However, Java doesn't pass method arguments by reference; it passes them by value.

So what you are closing in the closeResources(...) method is a copy of the connection and not the actual connection.

So if you are trying to consolidate common code try to avoid this common mistake. This can cost you a lot of headache when you are trying to find your connection leaks.

But a anonymous friend said the following:

I'm sorry to tell you you're wrong : Java neither passes objects by reference nor value : it passes "references by value". Therefore, the parameter your method gets os actually the value of the reference of the connection, which is properly resolved.

http://www.javaworld.com/javaworld/javaqa/2000-05/03-qa-0526-pass.html ;)

So why am I seeing the behavior that I'm seeing...

Can someone explain???

Spring Tip #1: Application Context

Did you know that if your method needs access to the Application Context then all you need to do is implement a Spring interface "ApplicationContextAware" and override the setApplicationContext(...) method in your bean.

@Override
public void setApplicationContext(ApplicationContext applicationcontext)
throws BeansException {
ctx = applicationcontext;
}

Here is an sample example that shows you how to use it.

Also, if you want to listen to all events... then implement ApplicationListener interface and catch your events like this:

@Override
public void onApplicationEvent(ApplicationEvent event) {
if (event instanceof SegmentEvent) {
final SegmentEvent segEvent = (SegmentEvent) event;
processSegment(segEvent.getSegment());
}
}

And now for some user feedback... :-)

Do you feel that there is not a very good Spring Book or reference that provides you with all the tips-n-tricks on how to harness Spring effectively?

Yes, the knowledge is out there in the Spring reference online and in those various sources but I haven't seen a good source of bite-sized book on Spring Tips...

When I asked around a few of you shared the same comment... Wouldn't it be great if there was a book/blog that gave you a daily Spring Tip with a short example...

If you share my thoughts, drop me a line and be counted...