Why is a try with resources recommended in this example?

[Tim Bant]

Hi all,

I'm studying for the Oracle SE17 exam, and it keeps mentioning to have a nested try with resources statement when setting parameters. Can someone explain why option 1 wouldn't work (I added this in)? The material I am using had option 2.

Thanks!

public class db {
	public static void main (String [] args) throws SQLException {
		Connection conn = DriverManager.getConnection("JDBC conn url");
		var sql = "call learn(?)";
		 try (var cs = conn.prepareCall(sql)) {
		    cs.setString(1, "java");
		    
		    //Option 1
		    var rs = cs.executeQuery();
		    while (rs.next()) {
		    	  System.out.println(rs.getString(3));
		    }

		    //Option 2
		    try (var rs = cs.executeQuery()) {
		       while (rs.next())
		          System.out.println(rs.getString(3));
		    }
		    
		    
		 }
	}
}

[Mike Simmons]

Generally, for any class that implements Closeable (and thus has a close() method), you should make sure it gets closed when you are done with it.  That applies to the CallableStatement cs, the ResultSet rs, and the Connection conn.  It's good programming practice to get in the habit of always making sure these things get closed when you're done - and try-with-ressources is the easiest and most reliable way to do this.  In this case, that's like this:

  public static void main (String [] args) throws SQLException {
    try (Connection conn = DriverManager.getConnection("JDBC conn url")) {
      var sql = "call learn(?)";
      try (var cs = conn.prepareCall(sql)) {
        cs.setString(1, "java");
        try (var rs = cs.executeQuery()) {
          while (rs.next())
            System.out.println(rs.getString(3));
        } // rs gets closed here
      } // cs gets closed here
    } // conn gets closed here
  }

 Each of those try statements is ensuring that when you leave that try statment, the associated object (conn / cs / rs) will be closed, no matter what.
 
 What happens if you neglect to do this?  Well, in many cases, not much.  Often the thing that you forgot to close will get closed automatically later on, e.g. when your program exits.  But sometimes, it won't get closed quickly enough, and that can cause problems.  You might later be trying to do a database operation, and you find you can't connect because your DB can't support that many simultaneous connections, or users, or something... and you say, what simultaneous users? And it's actually because you're doing something repeatedly in a loop and leaving resources unclosed, and the DB is freaking out because it can't do that many things at once, except it doesn't really need to do so many things at once - you just need to tell the db that you're done with those objects, when you're done with them.  Which is what the close() method is for.
 
 If you do get problems, they're often rather confusing and mysterious, occurring sometime after you failed to close something.  So it can be a pain to track down the cause.  It's generally much easier to just get in the habit of always closing the things that are Closeable.  Which is what try-with-resources is very, very good at.

[Tim Holloway]

What Mike said. When you leave rs orphaned, as in Option 1, you never know when the garbage collector will actually dispose of it.

Worse, you don't know IF all the resources are truly freed. The Java stuff will, but something like a network connection may destroy the Connection object but not release its network socket to the OS. Don't ask me how I know. All I can say is it wasn't me.

[Rob Spoor]

Mike Simmons wrote:...

With a little reordering you can combine the Connection and CallableStatement in one try-with-resources block:

public static void main (String [] args) throws SQLException {
    var sql = "call learn(?)";
    try (Connection conn = DriverManager.getConnection("JDBC conn url");
         var cs = conn.prepareCall(sql)) {
        cs.setString(1, "java");
        try (var rs = cs.executeQuery()) {
            while (rs.next())
                System.out.println(rs.getString(3));
        } // rs gets closed here
    } // cs gets closed here, then conn gets closed here even if closing cs fails
}

[Tim Holloway]

Incidentally, before try-with-resources, a properly-coded get-a-connectio/execute/statement/read-resultset sequence required a real rat's nest of try/catch/finally's. Espectially since the cleanup of some catch clauses could throw exceptions themselves! Try-with-resources is not only much, much tidier, but more reliable.