When to close a prepareStatement,ResultSet,Connection

[adeeb alexander]

Hi all.
Consider this program
try
{
Connection conn;
PreparedStatement ps ;
ps= conn.prepareStatement("Some Query1");
rs = ps.executeQuery();
rs.next();

ps= conn.prepareStatement("Some Query2");
rs = ps.executeQuery();
rs.next();

}
catch(Exception ce)
{

}


Like above i used one try block for may statements and preparedStatements to execute. Now Someone please tell me where shall i close all these. can i close in between or at the end of try block?


Thanks and Regards
alexander

[Paul Sturrock]

Closing them in a finally block is considered best practice. This is the only way you know for sure the resources have been freed.

[Rob Spoor]

I usually use nested try-finally blocks:

Connection conn = DriverManager.getConnection(...);
try
{
    PreparedStatement stmt = conn.prepareStatement(...);
    try
    {
        stmt.setString(1, ...);
        ResultSet rs = stmt.executeQuery();
        try
        {
            while (rs.next())
            {
                ...
            }
        }
        finally
        {
            rs.close();
        }
    }
    finally
    {
        stmt.close();
    }
}
finally
{
    conn.close();
}

I know it looks weird, and I have to write 3 levels of try-finally blocks, but I won't have to declare the PreparedStatement and ResultSet at the start with a value of null, then have to check if they are null before closing them.

[Jeanne Boyarsky]

I use

Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
  conn = DriverManager.getConnection(...);  
  stmt = conn.prepareStatement(...);  
  rs = stmt.executeQuery();  
  
  while (rs.next())  { 
    // do stuff
  }
 } finally {
  DbUtil.close(conn, stmt, rs);
}
 

That way I don't have to worry about writing the null checks/try catch on close either. I did it once .

[adeeb alexander]

Very late reply but a doubt guyz.

Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(...);
stmt = conn.prepareStatement(...);
rs = stmt.executeQuery();

while (rs.next()) {
// do stuff
}
} finally {
DbUtil.close(conn, stmt, rs);
}

What is DbUtil in the above code.

Thanks.