commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brian Cook <bc...@printtime.com>
Subject [DBCP] Anther lame "pool exhausted exception" question
Date Thu, 14 Jul 2005 20:59:42 GMT

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





Mime
View raw message