connection pool in UDB does not close connections

[Irene Loos]

We are getting connections from the pool and closing them after we done, which releases connections to the pool. But even after the application is closed connection to UDB is still exist. Does anybody experience this?

 Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
   conn = DBHelper.instance().getConnection(false);
sql.append("SELECT ID FROM TABLE WHERE KEY = " + key);
// Create a statement
stmt = conn.createStatement();
// Run the query
rs = stmt.executeQuery(sql.toString());
if (!rs.next())
return null;

id = rs.getInt(1);
return id;
}
catch (Exception exception) {
throw new DAOException(exception.toString());
}
finally {
try {
if (rs != null)
rs.close();

if (stmt != null)
stmt.close();

if (conn != null)
DBHelper.instance().closeConnection(conn);
}
catch (Exception exception) {
throw new DAOException(exception.toString());
}
}
 

[ May 02, 2003: Message edited by: Irene Loos ]

[Peter den Haan]

What is this DBHelper thing? If it's a homegrown connection pool then it's not at all unlikely that it doesn't properly clean up on application shutdown -- if this is the case, I'd recommend using one of the many solid open-source pools such as Apache Jakarta DBCP (really, a connection pool these days should offer a DataSource interface, connections should be returned simply by closing them, and the implementation should leverage the driver's pooling support whenever possible).
Another possible source of problems is that your database clean-up code is broken. Specifically, if rs.close() throws an exception then the statement won't be closed. If either rs.close() or stmt.close() throws an exception then the connection won't be returned to the pool.

Connection conn = DBHelper.instance().getConnection(false);
try {
   Statement stmt = conn.createStatement();
   try {
      // ... your code ...
   } finally {
      stmt.close();
   }
} finally {
   DBHelper.instance().closeConnection(conn);
}

The above is a watertight way to ensure that ResultSet and Statement are closed and the connection goes back into the pool. It makes use of the fact that closing a Statement will also close any ResultSet that might be open.
A completely unrelated issue that I couldn't help but notice is that you catch Exception (often a bad idea born out of lazyness) and re-throw it as a DAOException (fine) throwing away all stack trace information. That is going to make your debugging life a misery at some point. Why not properly wrap your root exception? If you're using JDK 1.4, use the Exception constructor that takes a Throwable cause. If you're using JDK 1.3 or earlier, write your own Exception root class along the following lines:

public abstract class MyAppException extends Exception {
    /** The nested exception */
    private final Throwable cause;
    
    /**
     * Creates new <code>MyAppException</code> without detail message.
     */
    public MyAppException() {
        this((Throwable)null);
    }
    
    /**
     * Constructs an <code>MyAppException</code> with the specified detail message.
     * @param msg the detail message.
     */
    public MyAppException(String msg) {
        this(msg, (Throwable)null);
    }
    
    /**
     * Constructs an <code>MyAppException</code> with the specified root cause.
     * @param cause the root cause of the exceptioin.
     */
    public MyAppException(Throwable cause) {
        super();
        this.cause = cause;
    }
    
    /**
     * Constructs an <code>MyAppException</code> with the specified message and
     * root cause.
     * @param msg the detail message.
     * @param cause the root cause of the exception.
     */
    public MyAppException(String msg, Throwable cause) {
        super(msg);
        this.cause = cause;
    }
    /**
     * Returns the root cause
     * @return The root cause or <code>null</code>
     */
    public Throwable getCause() {
        return cause;
    }
    
    /** Prints the stack trace
     * @param w The writer to print to
     */    
    public void printStackTrace(PrintWriter w) {
        if (cause == null) {
            super.printStackTrace(w);
        } else {
            synchronized (w) {
                w.println(this);
                cause.printStackTrace(w);
            }
        }
    }
    
    /** Prints the stack trace
     * @param s The stream to print to
     */    
    public void printStackTrace(PrintStream s) {
        if (cause == null) {
            super.printStackTrace(s);
        } else {
            synchronized (s) {
                s.println(this);
                cause.printStackTrace(s);
            }
        }
    }
}

Hope this helps
- Peter

[Irene Loos]

Thank you, Peter. I will try this out.