commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Craig R. McClanahan" <craig...@apache.org>
Subject Re: DBCP: Closed connections
Date Thu, 24 Apr 2003 16:29:27 GMT


On Thu, 24 Apr 2003, Behrens Matt - Grand Rapids wrote:

> Date: Thu, 24 Apr 2003 07:24:46 -0400
> From: Behrens Matt - Grand Rapids <Matt.Behrens@Kohler.Com>
> Reply-To: Jakarta Commons Users List <commons-user@jakarta.apache.org>
> To: Jakarta Commons Users List <commons-user@jakarta.apache.org>
> Subject: Re: DBCP: Closed connections
>
> Chris Forbis wrote:
>
> > Don't know if this is your issue but I had the same issue and it took me
> > forever to track down...  It all came back to the following code...
> >
> > //Stm = Statement
> > //Rs = ResultSet
> >
> > stm.close();
> > rs.close();
> >
> > The problem was simple, because of the order of these is wrong the
> > connection pool got messed up bad.
> >
> > Had to change to
> > rs.close();
> > stm.close();
> >
> > and all started working fine...
>
> I don't close my ResultSets or Statements -- just the connections.  I
> suppose this could be a problem, actually.  I don't know.
>

Yes, that is definitely going to caues you problems.  If you do not close
result sets or statements, you are leaving database resources in use until
the garbage collector finally gets around to cleaning up after you -- the
net effect is that your database will support fewer users than it should.

When you are using a connection pool, conn.close() does not actually close
the physical connection -- it puts that connection back into the pool so
it is available for someone else later.  Most particularly, any open
result sets and statements are likely to be left hanging around.

> I've had a few little issues like that.  Most memorable was when I had
> inexplicable hangs (this is an interactive server app where the users
> come in via telnet), and I beat my head against the wall for a little
> bit before I realized I was just running out of connections because I
> never explicitly returned a few to the pool...

Always returning them to the pool is very important -- but so is closing
result sets and statements first.

To make sure I get it right, I always try to use an idiom like this in my
code that accesses a database via JDBC:

    DataSource ds = ... acquire reference to connection pool ...;
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        conn = ds.getConnection();
        stmt = conn.prepareStatement(...);
        rs = stmt.executeQuery();
        ... process the result set ...
    } catch (SQLException e) {
        ... deal with the exception ...
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                ;
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                ;
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                ;
            }
            conn = null;
        }
    }

The finally block guarantees that whatever resources I've allocated get
cleaned up after me, in the correct order, no matter what happens.

Craig

Mime
View raw message