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

问题描述

我刚刚接触Java（我使用的是Java 6）。我一直使用下面的设计模式为所有我的Java POJO和servlets访问一个Oracle 11G数据库通过GlassFish 3.1.2 web服务器。

当所有可用进程（或会话，不确定有什么区别）被消耗时，我得到一个间歇性数据库错误（ORA-12519），导致我想

查看下面的设计模式，是否有更好的方法来确保与数据库的JDBC连接已释放在异常的情况下？例如，我应该把 if（conn！= null）conn.close（）; 代码放在catch块中吗？或者，有更好的设计模式吗？感谢您提供任何意见/提示。

  public String MyFunction（）throws Exception {
 
 Connection conn; 
 CallableStatement cs; 
 
 try {
 
上下文上下文= 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）//关闭连接
 conn.close（）; 
 
} catch（Exception e）{
 outParam =发生错误; 
} 
 return outparam; 
} 
  

解决方案

  if（conn！= null）//关闭连接
 conn.close（）; 
  

在此行 conn 为空。最流行的模式，直到Java 6是：

 连接conn = null; 
 try {
 //初始化连接
 //使用连接
} catch {
 //处理异常
} finally {
 if conn！= null）{
 try {conn.close（）; } catch（Exception e）{/ * handle close exception，very usually ignore * /} 
} 
} 
  

使用 Java 7 ，它的 try-with-resource 结构。上面的代码可以改成更短的

  try（Connection conn = createConnection（））{
 // use连接
} catch {
 //处理异常
} 
 //不需要明确调用
  

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

解决方案

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

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