title: Try / Try-with-resources and Connection, Statement and ResultSet closing
body: <p>I have recently having some discussions with my professor about how to handle the basic jdbc connection scheme. Suppose we want to execute two queries, this is what he proposes</p>
  
  <pre><code>public void doQueries() throws MyException{
      Connection con = null;
      try {
          con = DriverManager.getConnection(dataSource);
          PreparedStatement s1 = con.prepareStatement(updateSqlQuery);
          PreparedStatement s2 = con.prepareStatement(selectSqlQuery);
  
          // Set the parameters of the PreparedStatements and maybe do other things
  
          s1.executeUpdate();
          ResultSet rs = s2.executeQuery();
  
          rs.close();
          s2.close();
          s1.close();
      } catch (SQLException e) {
          throw new MyException(e);
      } finally {
          try {
              if (con != null) {
                  con.close();
              }
          } catch (SQLException e2) {
              // Can't really do anything
          }
      }
  }
  </code></pre>
  
  <p>I don't like this approach, and I have two questions about it:</p>
  
  <p>1.A) I think that, if any exception is thrown where we do 'other things', or in the line <code>rs.close()</code> or <code>s2.close()</code> then <code>s1</code> wouldn't have been closed when the method ends. Am I right about that?</p>
  
  <p>1.B) The professor keeps asking me to explicitly close the ResultSet (even when the Statement documentation makes clear that it will close the ResultSet) She says that Sun recommends it. Is there any reason to do so?</p>
  
  <p>Now this is what I think is the correct code for the same thing:</p>
  
  <pre><code>public void doQueries() throws MyException{
      Connection con = null;
      PreparedStatement s1 = null;
      PreparedStatement s2 = null;
      try {
          con = DriverManager.getConnection(dataSource);
          s1 = con.prepareStatement(updateSqlQuery);
          s2 = con.prepareStatement(selectSqlQuery);
  
          // Set the parameters of the PreparedStatements and maybe do other things
  
          s1.executeUpdate();
          ResultSet rs = s2.executeQuery();
  
      } catch (SQLException e) {
          throw new MyException(e);
      } finally {
          try {
              if (s2 != null) {
                  s2.close();
              }
          } catch (SQLException e3) {
              // Can't do nothing
          }
          try {
              if (s1 != null) {
                  s1.close();
              }
          } catch (SQLException e3) {
              // Can't do nothing
          }
          try {
              if (con != null) {
                  con.close();
              }
          } catch (SQLException e2) {
              // Can't do nothing
          }
      }
  }
  </code></pre>
  
  <p>2.A) Is this code correct? (Is it guaranteed that all will be closed when the method ends?)</p>
  
  <p>2.B) This is very large and verbose (and it gets worse if there are more Statements) Is there any shorter or more elegant way to do this without using try-with-resources?</p>
  
  <p>Finally this is the code I like the most </p>
  
  <pre><code>public void doQueries() throws MyException{
      try (Connection con = DriverManager.getConnection(dataSource);
           PreparedStatement s1 = con.prepareStatement(updateSqlQuery);
           PreparedStatement s2 = con.prepareStatement(selectSqlQuery))
      {
  
          // Set the parameters of the PreparedStatements and maybe do other things
  
          s1.executeUpdate();
          ResultSet rs = s2.executeQuery();
  
      } catch (SQLException e) {
          throw new MyException(e);
      }
  }
  </code></pre>
  
  <p>3) Is this code correct? I think my professor doesn't like this way because there is no explicit close of the ResultSet, but she has told me that she is fine with it as long as in the documentation it is clear that all is closed. Can you give any link to the official documentation with a similar example, or based in the documentation show that there is are no problems with this code?</p>