Best design pattern to close database connection when exception occurs

Question

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;
}

Note that it may also be good practice to close resultsets and statements. Here's a useful link (see Stefan Schweizer's reply in the link for good design pattern): http://stackoverflow.com/questions/103938/resultset-not-closed-when-connection-closed — ggkmath, Jun 22 '12 at 17:33

Miserable Variable · Accepted Answer · 2012-06-22T16:53:04.940

31

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

edited Jun 22 '12 at 16:53

answered Jun 22 '12 at 16:47

Miserable Variable

28,432
15
72
133

1

Thanks Miserable Variable, when you say "up until Java 6" do you mean "up to and including Java 6" (that's what I'm using)? I assume so. Also, when you say "quite usually ignored", are you refering to only the `try...catch` within the finally block (that is, the `if (conn != null) conn.close();` is always there, right?)? Why do people usually ignore handling the exception in the finally block for closing the connection? – ggkmath Jun 22 '12 at 16:57
Yes, exceptions from `connection.close()` are often ignored or at most logged but not handled in any other way:) I don't know why; I have never actually seen a close throwing an exception. – Miserable Variable Jun 22 '12 at 16:59
Just to be clear, since finally executes no matter what, I don't need to close the connection inside the try block right? I can just close the connection once place in the code, which is in the finally block. Is that right? – ggkmath Jun 22 '12 at 17:03
Yes, as I showed in my template, connections are typically only closed in finally. – Miserable Variable Jun 22 '12 at 17:29

score 4 · Answer 2 · answered Jun 22 '12 at 16:43

4

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();
    }

answered Jun 22 '12 at 16:43

Kazekage Gaara

14,972
14
61
108

Thanks Kazekage, Will the `conn.close()` line of code in the finally block give an error if the connection hasn't been open yet or any other reason? If so, shouldn't I use that `if (conn!=null)` statement above in the finally block? – ggkmath Jun 22 '12 at 16:50
If you suspect that `conn.close()` will again give an error, surround that with a `try catch finally` as well. – Kazekage Gaara Jun 22 '12 at 16:52
1

@ggkmath yes you need to test for `conn != null`; it can be null if `ds.getConnection()` throws exception. And whether you suspect or not, you need to put `close()` in `try/catch/finally`; else the method has to incloude it in its `throws` clause which is not good. – Miserable Variable Jun 22 '12 at 16:58

brunoss · Answer 3 · 2012-06-22T17:19:31.947

2

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.

edited Jun 22 '12 at 17:19

answered Jun 22 '12 at 16:58

brunoss

31
6

I didn't read but the user Miserable Variable talked about try-with-resources too. – brunoss Jun 22 '12 at 17:12
I will confirm that in 1 min but i guess you need to close the statements – brunoss Jun 22 '12 at 17:32
Doesn't seem universal. See http://stackoverflow.com/questions/103938/resultset-not-closed-when-connection-closed and http://www.theserverside.com/discussions/thread.tss?thread%5Fid=41036 – ggkmath Jun 22 '12 at 17:34
2

I was wrong. The apidocs for [`Connection`](http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#close()) do not say that closing a `Connection` closes its `Statements`; but note that closing [`Statement`](http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#close()) *is* required to close the `ResultSets` – Miserable Variable Jun 22 '12 at 17:48
3

@MiserableVariable The JDBC spec specifies that Statements are closed when the connection is closed (section 9.4.4.1 of the JDBC 4.1 spec). Also the JavaDoc of `Connection.close()` states "Releases this Connection object's database and JDBC resources immediately" JDBC Resources are any objects created from a connection (ie Statements) – Mark Rotteveel Jun 23 '12 at 10:12
@MarkRotteveel thank you for that clarification but the javadocs is not as explicit about closing `Statements` when closing `Connection` as it is about closing `ResultSets` when closing `Statements` – Miserable Variable Jun 24 '12 at 17:50
@MiserableVariable True, the documentation (and the spec) does sometimes lack detail or clarity, it is even worse when you try to implement a JDBC driver ;) – Mark Rotteveel Jun 26 '12 at 19:08

score 2 · Answer 4 · answered Jun 22 '12 at 17:42

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

Best design pattern to close database connection when exception occurs

4 Answers4

Linked

Related