commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dirk Verbeeck <dirk.verbe...@pandora.be>
Subject Re: [DBCP] Anther lame "pool exhausted exception" question
Date Fri, 15 Jul 2005 06:35:12 GMT
Hi Brian,

Examples can be found here:
http://svn.apache.org/repos/asf/jakarta/commons/proper/dbcp/trunk/doc/

If you are creating a BasicDataSource yourself then you need to keep it 
in a static variable somewhere. You create it once and only close the 
datasource when your application is shutting down. The BasicDataSource 
contains the pool and you only want one of these around for each 
database(scheme) you want to connect to.

Your program then uses ds.getConnection() / conn.close() to get/return 
connections from/to the pool.
Use the pattern Craig gave you to make sure you always close the 
rs/stmt/conn.

So key point here is to only create one datasource (and store it in a 
static somewhere) and not to close the datasource after each call.

Cheers
Dirk


Brian Cook 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
> 
> 
> ------------------------------------------------------------------------
> 
> ---------------------------------------------------------------------
> 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