8 Getting Unconnected from Oracle Database

While unconnecting from the database in JDeveloper is a simple task, it is not a process by itself in a Java application. In the application, you must explicitly close all ResultSet, Statement, and Connection objects after you are through using them. When you close the Connection object, you are unconnected from the database. The close methods clean up memory and release database cursors. Therefore, if you do not explicitly close ResultSet and Statement objects, serious memory leaks may occur, and you may run out of cursors in the database. You must then close the connection.

This chapter includes the following sections:

8.1 Creating a Method to Close All Open Objects

The following steps add a closeAll method to the DataHandler class:

  1. Open DataHandler.java in the Java Source Editor by double-clicking it in the Application Navigator.

  2. Declare the closeAll method at the end of the DataHandler class as follows:

    public void closeAll() {
 
}

  3. Within the method body, check whether the ResultSet object is open as follows:

    if ( rset != null ) {

  4. If it is open, close it and handle any exceptions as follows:

      try { rset.close(); } catch ( Exception ex ) {} 
  rset = null;
}

  5. Repeat the same actions with the Statement object.

    if ( stmt != null ) {
  try { stmt.close(); } catch ( Exception ex ) {} 
  stmt = null;
}

  6. Finally, close the Connection object.

    if ( conn != null ) {
  try { conn.close(); } catch ( Exception ex ) {} 
  conn = null;
}

8.2 Closing Open Objects in the Application

You must close the ResultSet, Statement, and Connection objects only after you have finished using them. In the DataHandler class, the insert, update, and delete methods must close these objects before returning. Note that the query methods cannot close these objects until the employees.jsp page has finished processing the rows returned by the query.

In the following steps, you add the appropriate calls to the closeAll method in the DataHandler.java file:

  1. Open DataHandler.java in the Java Source Editor.

  2. At the end of the addEmployee method, after the closing brace of the catch block, add the following call to the closeAll method in a finally block:

    finally {
  closeAll();
}

  3. Add the same call to the addEmployeeSP, deleteEmployeeById, findEmployeeById, updateEmployee, and authenticateUser methods.

  4. Open the employees.jsp file in the Visual Editor. Find the scriptlet inside the Employees table, and double-click to open the Insert Scriptlet dialog box.

  5. Add the following statement after the while loop:

    empsbean.closeAll();

  6. Save your work, and compile and run the application to ensure that everything still works correctly.