Introduction

Close and release JDBC resources when finished using them. Ensure that they are closed/released in all circumstances, including exceptions and errors. JDBC connection pooling provides a defined and finite number of JDBC connections. Shortages cause long waits. Failing to close JDBC connections causes waits for idle JDBC connections to be reaped and reused. Closing JDBC connections will allow quicker reuse and improve performance.

This best practice applies to the following product, version, and platform:

• WebSphere Application Server Base, all platforms, versions 3.0.2.x, 3.5.x, 4.0

Back to top

Recommendation

Failing to close and release JDBC connections can cause other users to experience long waits for connections. Although a JDBC connection that is left unclosed will be reaped and returned by WebSphere® Application Server after a timeout period, others may have to wait for this to occur.

Close JDBC statements when you are through with them. JDBC ResultSets can be explicitly closed as well. If not explicitly closed, ResultsSets are released when their associated statements are closed.

Ensure that your code is structured to close and release JDBC resources in all cases, even in exception and error conditions. The following shows that the acquisition and use of JDBC resources is wrapped in a "Try-Catch-Finally." Here the close of the JDBC resources is handled in the finally clause. The close will happen in all circumstances.

Proper way to close JDBC Connections and PreparedStatements

Connection conn = null;
ResultSet rs = null;
PreparedStatement pss = null;
try
{
        conn = dataSource.getConnection(USERID,PASSWORD);
        pss = conn.prepareStatement("SELECT SAVESERIALZEDDATA
            FROM SESSION.PINGSESSION3DATA WHERE SESSIONKEY = ?");
        pss.setString(1,sessionKey);
        rs = pss.executeQuery();
        pss.close();
        conn.close();
}
catch (Throwable t)
{
        // Insert Appropriate Error Handling Here
}
finally
{
        // The finally clause is always executed - even in error
        // conditions PreparedStatements and Connections will always be closed
        try
        {
                  if (pss != null)
                              pss.close();
        }
        catch(Exception e) {}

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

Back to top

Alternative

The following code shows the wrong way to close JDBC resources. They will be lost in exception scenarios. Here the application will fail to close a JDBC connection if an exception is thrown.

JDBC alternative - session data

Connection conn = null;
ResultSet rs = null;
PreparedStatement pss = null;
try
{
         conn = dataSource.getConnection(USERID,PASSWORD);
         pss = conn.prepareStatement("SELECT SAVESERIALZEDDATA
            FROM SESSION.PINGSESSION3DATA WHERE SESSIONKEY = ?");
         pss.setString(1,sessionKey);
         rs = pss.executeQuery();
         pss.close();
         conn.close();
}
catch (Throwable t)
{
          // If i reach this spot, I blew a JDBC Connection.
}

Resources

• WebSphere Application Server Development Best Practices for Performance and Scalability
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Recommendation
• Alternative
• Resources
• About the author
• Comments

Next steps from IBM

Rational Application Developer provides a comprehensive Integrated Development Environment (IDE) used to develop Web 2.0 applications requiring J2EE, Servlets and JavaServer Pages (JSPs).

---

• Try: Try the free Rational Application Developer (RAD), which helps developers quickly design, develop, test, analyze, and deploy high-quality Java, Javaserver pages, J2EE and Web 2.0 applications.
• Article: See how Rational Application Developer and Service Data Objects have been extended to JavaServer Pages to create data views and database queries using a simple, wizard-based, drag-and-drop technology.
• Tutorial: Take a look at developing a J2EE Web application and JavaServer Pages (JSPs) for both an HTML input form and the HTML output from the server, using Rational Application Developer.
• Demo: This demo highlights the enhanced Web 2.0, rich Internet application development features in Rational Application Developer including AJAX and Java Persistence API application development.
• Buy: Rational Application Developer for WebSphere Software

Dig deeper into WebSphere on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers