Error catching for DataSource

[Jon Camilleri]

This newbie tutorial , includes an example of using DataSource, where the author closed the connections to the database within the try block, rather than, what I would do, within the finally block.

Do you agree with this approach? Why?

try
        {
          Connection conn = _ds.getConnection();

          Statement stmt = conn.createStatement();
          ResultSet result = stmt.executeQuery("SELECT * FROM books");

          ...
      
          result.close();
          stmt.close();
          conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(DataSourceExample.class.getName()).log(
                    Level.SEVERE, null, ex);
        } 
        finally 
        {
            
        }
}

[Campbell Ritchie]

I would agree with you about using the finally block. There is another problem in that code however; each of the three things to be closed might throw an Exception, so your finally block might complte abruptly. So you should maybe have three try-finally pairs, one to open the connection, one the statement and one the result set. And of course to close them again.

[Rob Spoor]

In other words:

try
{
    Connection conn = _ds.getConnection();   
    try
    {
        Statement stmt = conn.createStatement();   
        try
        {
            ResultSet result = stmt.executeQuery("SELECT * FROM books");   
            try
            {
                ...   
            }
            finally
            {
                result.close();   
            }
        }
        finally
        {
            stmt.close();   
        }
    }
    finally
    {
        conn.close();   
    }
}
catch (SQLException ex)
{   
    Logger.getLogger(DataSourceExample.class.getName()).log(Level.SEVERE, null, ex);   
}

Or, with Java 7:

try (Connection conn = _ds.getConnection();
     Statement stmt = conn.createStatement();
     ResultSet result = stmt.executeQuery("SELECT * FROM books"))
{
    ...   
}
catch (SQLException ex)
{   
    Logger.getLogger(DataSourceExample.class.getName()).log(Level.SEVERE, null, ex);   
}

If you need to call some methods on conn or stmt before using them you may want to split them into multiple try-with-resources blocks, but the code will be almost equally clean.

[Campbell Ritchie]

Thank you, Rob.

Jon Camilleri: are you sure you have copied everything correctly from the tutorial? When I looked, there was a finally with a close() call in.

[Jon Camilleri]

Campbell Ritchie wrote:Thank you, Rob.

are you sure you have copied everything correctly from the tutorial? When I looked, there was a finally with a close() call in.

In the tutorial the PrintWriter object is closed within the finally block, whilst my question was about the Connection object.

[Seetharaman Venkatasamy]

I personally handle this situation by enclosing try finally with try catch

try
		{
			Connection conn = null;
			Statement stmt = null;
			ResultSet result = null;
			try  
			{  
				conn = _ds.getConnection();
				stmt = conn.createStatement();
				result = stmt.executeQuery("SELECT * FROM books"); 

			}  
			finally  
			{  
				//call closeDBResources(Connection conn, Statement stmt, ResultSet result);    
			}  
		}  
		catch (SQLException ex)  
		{     
			//Logger
		} 	

[Campbell Ritchie]

Rob's solution is neater, I think.

[Campbell Ritchie]

Seetharaman Venkatasamy wrote:. . . by enclosing try finally with try catch . . .

That is where you will need the != null tests in the finally blocks.

[Seetharaman Venkatasamy]

Campbell Ritchie wrote:That is where you will need the != null tests in the finally blocks.

in closeDBResources(Connection conn, Statement stmt, ResultSet result):

private static void closeDBResources(Connection conn, Statement stmt, ResultSet result)
	{
		try
		{
			result.close();
		}
		catch(SQLException e)
		{
			//Log
		}
		try
		{
			stmt.close();
		}
		catch(SQLException e)
		{
			//Log
		}
		try
		{
			conn.close();
		}
		catch(SQLException e)
		{
			//Log
		}
	}

In Rob code, I just feel the many nested try finally may make less readability! but what I made the method above encapsulate the closing part. is not it? correct me If I am wrong please

[Jon Camilleri]

Seetharaman Venkatasamy wrote:

Campbell Ritchie wrote:That is where you will need the != null tests in the finally blocks.

in closeDBResources(Connection conn, Statement stmt, ResultSet result):

private static void closeDBResources(Connection conn, Statement stmt, ResultSet result)
	{
		try
		{
			result.close();
		}
		catch(SQLException e)
		{
			//Log
		}
		try
		{
			stmt.close();
		}
		catch(SQLException e)
		{
			//Log
		}
		try
		{
			conn.close();
		}
		catch(SQLException e)
		{
			//Log
		}
	}

In Rob code, I just feel the many nested try finally may make less readability! but what I made the method above encapsulate the closing part. is not it? correct me If I am wrong please

Yes, the nested statements are a bit tough to digest, in fact I preferred the Java 7 part suggested by Rob Spoor, which I am planning to practice shortly.

Why are you enclosing each .close() within a try..catch block, rather than grouping them? I'm afraid this would be slightly less efficient than grouping them, hence, JVM would process the nested statements faster, than a series of try..catch blocks. What do you think?

[Rob Spoor]

Seetharaman Venkatasamy wrote:In Rob code, I just feel the many nested try finally may make less readability! but what I made the method above encapsulate the closing part. is not it? correct me If I am wrong please

I don't like to either include or re-implement a utility class all over the place, so my approach will work everywhere. However, once I switch to Java 7, I'll definitely use the try-with-resources version. It's shorter, and handles the closing for you.

Jon Camilleri wrote:Why are you enclosing each .close() within a try..catch block, rather than grouping them? I'm afraid this would be slightly less efficient than grouping them, hence, JVM would process the nested statements faster, than a series of try..catch blocks. What do you think?

Because each of the close() methods can throw an SQLException. If that occurs, you still want the others to be closed (if possible). But again, this is something that the try-with-resources will do for you.