title: Best design pattern to close database connection when exception occurs
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>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 <code>if ( conn != null) conn.close();</code> code INSIDE the catch block? Or, is there a better design pattern? Thanks in advance for any comments/hints.</p>
  
  <pre><code>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;
  }
  </code></pre>