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.
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.
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.
0 Comments