CODEWITHSUNDEEP
javaoracle-databasejdbc
ggkmath
ggkmath

Reputation: 4246

Best design pattern to close database connection when exception occurs

I'm new to Java (I'm using Java 6). I've been using the below design pattern for all my Java POJOs and servlets to access an Oracle 11G database via GlassFish 3.1.2 web server.

I'm getting an intermittent database error (ORA-12519) when all available processes (or sessions, not sure what the difference is) are consumed, leading me to think somehow the processes are not being released by the application.

Looking at the design pattern below, is there a better way to make sure that the JDBC connection to the database is released in the event of an exception? For example, should I also place the if ( conn != null) conn.close(); code INSIDE the catch block? Or, is there a better design pattern? Thanks in advance for any comments/hints.

public String MyFunction() throws Exception {     

    Connection conn;
    CallableStatement cs;

  try {

      Context context = new InitialContext();
      DataSource ds = (DataSource)context.lookup("jdbc/MyPool");
      conn = ds.getConnection();        

      cs = conn.prepareCall( "{call my_sproc (?)}" );

      cs.registerOutParameter(1, Types.VARCHAR);

      cs.execute();

      String outParam = cs.getString(1); 

      if ( conn != null )  // close connection
         conn.close();

  } catch (Exception e) {
      outParam = "an error occurred";
  }
    return outparam;
}

Upvotes: 9

Views: 27769

Answers (4)

Vladislav Bauer
Vladislav Bauer

Reputation: 962

I prefer another more elegant way than:

} finally {
  if (conn != null) {
     try {
         conn.close();
     } catch (Exception e) {
         /* handle close exception, quite usually ignore */
     } 
  }
}

You could use DbUtils.closeQuietly: http://commons.apache.org/dbutils/apidocs/org/apache/commons/dbutils/DbUtils.html

Upvotes: 2

brunoss
brunoss

Reputation: 31

java se 7 supports the try-with-resources feature. that generates the finally for you. http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html

the finally block will close the resources alocated in the try. like you were using the keyword using in c#

however the user is using java se6 ... See the options of other users :)

IMPORTANT: Statements used should be closed too.

Upvotes: 2

Miserable Variable
Miserable Variable

Reputation: 28752

if ( conn != null )  // close connection
         conn.close();

At this line conn cannot be null. The most popular pattern, up until Java 6 is:

Connection conn = null;
try {
   // initialize connection
   // use connection 
} catch {
  // handle exception
} finally {
  if (conn != null) {
     try { conn.close(); } catch (Exception e) { /* handle close exception, quite usually ignore */ } 
     }
}

With Java 7 this will become less cumbersome with its try-with-resource construct. The above code can change to the much shorter

try (Connection conn  = createConnection()) {
    // use connection 
} catch {
    // handle exception
}
// close is not required to be called explicitly

Upvotes: 31

Kazekage Gaara
Kazekage Gaara

Reputation: 15052

Use a finally block always to free up resources.

The finally block always executes when the try block exits. This ensures that the finally block is executed even if an unexpected exception occurs. 

  try {

      Context context = new InitialContext();
      DataSource ds = (DataSource)context.lookup("jdbc/MyPool");
      conn = ds.getConnection();        

      cs = conn.prepareCall( "{call my_sproc (?)}" );

      cs.registerOutParameter(1, Types.VARCHAR);

      cs.execute();

      String outParam = cs.getString(1); 


  } catch (Exception e) {
      outParam = "an error occurred";
  }
 finally {
       conn.close();
    }

Upvotes: 4

Related Questions