Closing Your JDBC Objects

In the examples shown in this chapter, I ve created a number of JDBC objects: a Connection object named myConnection , a Statement object named myStatement , and two ResultSe t objects named customerResultSet and productResultSet . As mentioned earlier in the section on result sets, ResultSet objects should be closed when they are no longer needed using the close() method. Similarly, you should also close the Statement and Connection objects when those objects are no longer needed.

In the following example, the myStatement and myConnection objects are closed using the close() method:

 myStatement.close(); myConnection.close(); 

You should typically close your Statement and Connection objects in a finally clause. Any code contained in a finally clause is guaranteed to be run, no matter how control leaves the try statement. If you want to add a finally clause to close your Statement and Connectio n objects, those objects should be declared before the first try / catch statement used to trap exceptions. The following example shows how to structure the main() method so that the Statement and Connection objects may be closed in a finally clause:

 public static void main (String args []) {  // declare Connection and Statement objects  Connection myConnection = null;  Statement myStatement = null;  try {  // register the Oracle JDBC drivers   DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());   // connect to the database as store  // using the Oracle JDBC Thin driver  myConnection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL",   "store",  "store_password");  // create a Statement object   myStatement = myConnection.createStatement();  // more of your code goes here   ...  } catch (SQLException e) {   e.printStackTrace();  } finally {   try {   // close the Statement object using the close() method  if (myStatement != null) {    myStatement.close();   }  // close the Connection object using the close() method    if (myConnection != null) {    myConnection.close();  }   } catch (SQLException e) {    e.printStackTrace();  }  } } // end of main() 

Notice that the code in the finally clause checks to see if the Statement and Connectio n objects are not equal to null before closing them using the close() method. If they are equal to null , there is no need to close them. Because the code in the finally clause is the last thing to be run and is guaranteed to be run, the Statement and Connection objects are always closed if required, regardless of what else happens in your program. For the sake of brevity, only the first program featured in this chapter uses a finally clause to close the Statement and Connectio n objects.

You have now seen how to write JDBC statements that connect to a database, run DML and DDL statements, control transactions, handle exceptions, and close JDBC objects. The following section contains a complete program that illustrates the use of JDBC.