Tuesday, July 31, 2007

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???

5 comments:

Anonymous said...

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 ;)

Jay Bose said...

Not sure I'd go with the "reference by value" statement. There is a difference between an object and a reference to an object. When dealing with non-primitives, your method is passed the a reference to an object. Implicitly, modifying the reference will only change your reference, not the object itself. Your situation deals with references, and not primitives such as the linked example.

I could not see an obvious reason for the leak in one case, and not the other. You may want to try Spring's JDBC templates.

Venkatt Guhesan said...

Hey jay, Thanks on your comment. Yes, I could use Spring JDBC Template but I'm trying to figure out why this is happening... So this bloag is more of an attempt to answer the "WHY"...

Anonymous said...

How are you monitoring the connection leaks? I'd like to witness this first hand.

Unknown said...

I started to do the same thing, to write a close function. But seeing this post has made me hesitate.

I can't see anything wrong with your code. When you say:

Connection con = ...

the value of con is actually a reference to a Connection object. At least that's what I think.

Lets imagine it to be 0x1234FF5E

When the con parameter is passed, a copy of the value 0x1234FF5E is made. That is, a copy of the object reference is passed, not a copy of the object.

Then when you call con.close(), the connection is closed, not a copy of the connection.

Am I wrong?

If not, why did this not work for you?

thanks,

Terry