commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig McClanahan <craig...@gmail.com>
Subject Re: [DBCP] Anther lame "pool exhausted exception" question
Date Thu, 14 Jul 2005 22:10:23 GMT
The key to returning connections to the connection pool is to call
close() on the *connection* itself.  The most common way people get
themselves in trouble is to skip this somehow (perhaps because an
exception is thrown).

To avoid that sort of problem, I tend to use an idiom like this for
JDBC access code:

    DataSource ds = ...; // Acquire a reference from JNDI or wherever
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = ds.getConnection(); // Or whatever
        stmt = conn.prepareStatement("...");
        rs = stmt.executeQuery();
        ... process the results ...
    } catch (SQLException e) {
        ... process the exception ...
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                ... record or ignore ...
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                ... record or ignore ...
            }
            rs = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                ... record or ignore ...
            }
            conn = null;
        }
    }

Courtesy of the "finally" logic, I'm guaranteed to always execute my
cleanup code, no matter where an exception might get thrown.

You'll note that it can be pretty tedious to do all this stuff with
raw JDBC.  You might also want to look at using one of the SQL
frameworks that encapsulate this sort of stuff, but still give you the
guarantee that the connection will ultimately be returned to the pool.

Craig



On 7/14/05, Brian Cook <bcook@printtime.com> wrote:
> 
> I have several apps written that use org.apache.commons.dbcp.  And I am
> getting the  org.apache.commons.dbcp.SQLNestedException: Cannot get a
> connection, pool exhausted exception.
> 
> When I run netstat sure enough it shows about 60 - 100 "ESTABLISHED"
> connections open at any given time.  Looking deeper it looks like new
> connections are created most but not each time the Java Bean the uses
> the data base is called.  So it seems pretty clear that I am failing to
> close all of the connections in my app.  My problem is that I can not
> find where that is.
> 
> My understanding is that as long as I close every ResultSet, and each
> Connection object I should have my bases covered.  I have gone back
> though the app repeatedly and verified that all the ResultSets,
> Connections, and BasicDataSource objects are all being closed.
> 
> Due to an internal political problem I am not using JNDI for the
> connections.  Instead I have a class file that is called when a
> connection is need. After the object is used a close() method is called
> and closes the Statement, Connection, ResultSet, BasicDataSource objects.
> 
> So my questions are ..
> 
>     1.  Is there something else I should be doing to return the
> connections to the pool?
>     2.  Does not using JNDI with org.apache.commons.dbcp cause problems
> with closing the connection pool some how?
> 
> 
> I have been though about 6 months of the archives for this list and did
> not find any hints,  I went to look at the "examples" lnik at on the
> Commons.DBCP page but the link to
> http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems
> to be broken.  Googling has only just returned the usall advice of make
> sure you are closing every connection.
> 
> I am open to any ideas or thoughts to what I am failing to due here.
> Details and the code are included below.
> 
> 
> OS:                 Linux Red Hat 9.0
> Container:       Tomcat 4.127
> DBCP:            commons-dbcp-1.2.1.jar, commons-pool-1.2.jar
> JDK:               1.5
> Database:        MySQL 4.1
> JDBC Driver:  mysql-connector-java-3.1.7-bin.jar
> 
> 
> 
> public class DBConector {
> 
> 
> <Omited Code/>
> 
> 
>     public DBConector(String dataBaseName) {
> 
>       try{
>         String DataBaseURL = "jdbc:mysql://"+DataBaseHost+"/"+dataBaseName+
>         "?user="+DataBaseUser+"&password="+DataBasePassword;
> 
>         mysqlCon = getPooledConection(DataBaseURL);
>         sqlStatement = mysqlCon.createStatement();
> 
>       }
>       catch(Exception s) {
>         System.err.println(new java.util.Date()+" Error throw by" +
>             " DBConector()\n"+s);
>       }
>     }
> 
> 
> <Omited Code/>
> 
> 
>     public ResultSet read(String SQL) {
> 
>       try{
>          rs = sqlStatement.executeQuery( SQL );
>        }                                   // Close try
>        catch(Exception s) {
>             System.err.println(new java.util.Date()+" Error throw by an
> SQL " +
>             "call in DBConector.read() class : \n"+s+"\n"+s.getMessage()+
>             "\nSQL Queary : "+SQL+"\n");
>        }                                  // Close catch
> 
>         return(DataFromDB);
>     }                                       // Close the read method
> 
> 
> 
> <Omited Code/>
> 
> 
>    protected static Connection getPooledConection(String connectURI) {
>         ds = new BasicDataSource();
>         ds.setDriverClassName(DBDriver);
>         ds.setUsername(DataBaseUser);
>         ds.setPassword(DataBasePassword);
>         ds.setUrl(connectURI);
>         ds.setInitialSize(3);
>         ds.setMaxActive(32);
>         ds.setMaxIdle(8);
>         ds.setMinIdle(3);
>         Connection con = null;
> 
>       try {
>         con = ds.getConnection();
>       }
>       catch(Exception a){System.out.println(new java.util.Date() +
>         "PrintTimeDataBase6.getPooledConection() has thrown an
> exception.\n"+a+
>         "\n"+ a.getMessage()   );
>       }
>         return con;
>     }                               // Close method
> 
> 
> <Omited Code/>
> 
> 
>    public void close() {
>        try {  sqlStatement.close(); } catch(Exception a) {  }
>        try {  mysqlCon.close();      } catch(Exception a) {  }
>        try {  rs.close();   } catch(Exception a) {  }
>        try {  ds.close();   } catch(Exception a) {  }
>    }                                // Close method
> }                                   // Close the PrintTimeWebServices Class
> 
> --
> Brian Cook
> Digital Services Analyst
> Print Time Inc.
> bcook@printtime.com
> 
> 
> 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
> 
>

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message