发生异常时关闭数据库连接的最佳设计模式 [英] Best design pattern to close database connection when exception occurs

问题描述

我是 Java 新手(我使用的是 Java 6).我一直在为我的所有 Java POJO 和 servlet 使用以下设计模式，以通过 GlassFish 3.1.2 Web 服务器访问 Oracle 11G 数据库.

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.

当所有可用进程(或会话，不确定有什么区别)都被消耗时，我收到间歇性数据库错误 (ORA-12519)，这让我想到应用程序没有以某种方式释放进程.

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.

看看下面的设计模式，有没有更好的方法来确保在发生异常时释放与数据库的JDBC连接?例如，我是否也应该将 if ( conn != null) conn.close(); 代码放在 catch 块中?或者，有没有更好的设计模式?提前感谢您的任何评论/提示.

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

推荐答案

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

在这一行conn 不能为空.最流行的模式，直到 Java 6 是:

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 */ } 
     }
}

在 Java 7 中，使用 try-with-resource 构造.上面的代码可以改成更短的

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

这篇关于发生异常时关闭数据库连接的最佳设计模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！