How To Ensure No ResultSet/Connection/Statement Leaks Exist in Your JDBC Code

The classes shown below require explicit closure to release resources established at the database level. Connection objects leave database sessions open, and Statement and ResultSet objects leave cursors open at the database level. The corresponding JDBC API's are listed below.


java.sql.Connection
java.sql.Statement
java.sql.CallableStatement
java.sql.PreparedStatement
java.sql.ResultSet

In order to release resources associated with these objects the method close() must be called once the program has finished working with the objects. Below are some common problems encountered when closing resources and a simple utility class for closing these resources.



Problems


1. Only closing Connection object and not Statement and ResultSet objects used from that Connection object
In a typical program, one Connection object can map to multiple Statement and ResultSet objects, so it is easy to forget to close these objects. In the example below the Statement and ResultSet objects are not closed resulting in two open cursors at the database level which are not released until the program exits.

public void run() throws SQLException
{
  // obtain Conection object normally from a DataSource object
  Connection conn = getConnection();
  // Create a Statement object
  Statement stmt = conn.createStatement();
  // Execute a Query against the Statement
  ResultSet rset = stmt.executeQuery("select sysdate from dual");
  // Move to the first and only row in this case
  rset.next();
  // Display todays date
  System.out.println(rset.getString(1));

  conn.close();

}

So in order to avoid this the Statement and ResultSet objects must be closed as well, as shown below.

public void run() throws SQLException
{
  // obtain Conection object normally from a DataSource object
  Connection conn = getConnection();
  // Create a Statement object
  Statement stmt = conn.createStatement();
  // Execute a Query against the Statement
  ResultSet rset = stmt.executeQuery("select sysdate from dual");
  // Move to the first and only row in this case
  rset.next();
  // Display todays date
  System.out.println(rset.getString(1));

  rset.close();
  stmt.close();
  conn.close();

}


2. Failing to close resources in a FINALLY Block
Failing to close cursors in a finally block is a major cause of cursor leaks in JDBC programs. In the example below, all resources are released , but should an exception occur before the close() method is called, no finally clause exists resulting in cursor leaks and or active sessions from Connection objects.

  public void run() throws SQLException
  {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;

    try
    {
      // obtain Conection object normally from a DataSource object
      conn = getConnection();
      // Create a Statement object
      stmt = conn.createStatement();
      // Execute a Query against the Statement
      rset = stmt.executeQuery("select sysdate from dual");
      // Move to the first and only row in this case
      rset.next();
      // Display todays date
      System.out.println(rset.getString(1));
  
      rset.close();
      stmt.close();
      conn.close();
    }
    catch (Exception ex)
    {
      // fatal error occured , should be logged
      ex.printStackTrace();
    }

  }

To avoid this a finally clause should always be added.  Also note that each close() method is wrapped in it's own try catch block ensuring each object has the opportunity to be closed in the event of an exception.
  public void run() throws SQLException
  {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;

    try
    {
      // obtain Conection object normally from a DataSource object
      conn = getConnection();
      // Create a Statement object
      stmt = conn.createStatement();
      // Execute a Query against the Statement
      rset = stmt.executeQuery("select sysdate from dual");
      // Move to the first and only row in this case
      rset.next();
      // Display todays date
      System.out.println(rset.getString(1));
  
    }
    catch (Exception ex)
    {
      // fatal error occured , should be logged
      ex.printStackTrace();
    }
    finally    {
      try
      {
        if (rset != null)
           rset.close();
      }
      catch (SQLException se1)
      {
      }

      try
      {
        if (stmt != null)
           stmt.close();
      }
      catch (SQLException se1)
      {
      }
  
      try
      {
        if (conn != null)
           conn.close();
      }
      catch (SQLException se1)
      {
      }
        
    }

  }

3. Simple Utility Class

Here is a simple class which can used to ensure resources are always cleaned up from your JDBC program.

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JDBCUtil {

    public static void close(ResultSet resultSet, Statement statement, 

                             Connection connection) {

        try {

            if (resultSet != null)

                close(resultSet);

            if (statement != null)

                close(statement);

            if (connection != null)

                close(connection);

        }  catch (Exception e) {

            e.printStackTrace();

        }

    }

    public static void close(ResultSet resultSet, Statement statement) {

        try {

            if (resultSet != null)

                close(resultSet);

            if (statement != null)

                close(statement);

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    public static void close(ResultSet resultSet) {

        try {

            if (resultSet != null)

                resultSet.close();

        } catch (SQLException ex) {

            while (ex != null) {

                System.out.println("SQLState: " + ex.getSQLState());

                System.out.println("Message: " + ex.getMessage());

                System.out.println("Vendor: " + ex.getErrorCode());

                ex.printStackTrace();

                ex = ex.getNextException();

                System.out.println("");

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    public static void close(Statement statement) {

        try {

            if (statement != null)

                statement.close();

        } catch (SQLException ex) {

            while (ex != null) {

                System.out.println("SQLState: " + ex.getSQLState());

                System.out.println("Message: " + ex.getMessage());

                System.out.println("Vendor: " + ex.getErrorCode());

                ex.printStackTrace();

                ex = ex.getNextException();

                System.out.println("");

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    public static void close(Connection connection) {

        try {

            if (connection != null)

                connection.close();

        } catch (SQLException ex) {

            while (ex != null) {

                System.out.println("SQLState: " + ex.getSQLState());

                System.out.println("Message: " + ex.getMessage());

                System.out.println("Vendor: " + ex.getErrorCode());

                ex.printStackTrace();

                ex = ex.getNextException();

                System.out.println("");

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

}
 

Here is an example of using the class JDBCUtil that ensures no leaking cursors will occur.


  public void run() throws SQLException
  {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;

    try
    {
      // obtain Conection object normally from a DataSource object
      conn = getConnection();
      // Create a Statement object
      stmt = conn.createStatement();
      // Execute a Query against the Statement
      rset = stmt.executeQuery("select sysdate from dual");
      // Move to the first and only row in this case
      rset.next();
      // Display todays date
      System.out.println(rset.getString(1));
  
    }
    catch (Exception ex)
    {
      // fatal error occured , should be logged
      ex.printStackTrace();
    }
    finally
    {
      JDBCUtil.close(rset, stmt, conn);
    }

  }


Note: Today's J2EE applications are making use of framework's such as Spring which masks the underlying JDBC access through it's own API's avoiding such problems as those encountered in this note.


Some common errors that can occur if resources are not released correctly are as follows:

1. ORA 1000 -  "maximum open cursors exceeded". This is controlled by the database parameter open_cursors, which when exceeded with result in this error on the database.

2. ORA 20 - "maximum number of processes (%s) exceeded". This will occur when connections are created from JDBC programs and not released. The database parameter for this is processes.