What you have below looks good as far as I can tell. Connections appear
to be returned ASAP after using them. Do all your calls to the database
follow this pattern? Are there any places where the connection isn't
closed immediately and within a finally block?
As an aside, you can store the DataSource object once you've done the
JNDI look-up for the first time. That won't harm anything and might
improve speed a little.
--David
[EMAIL PROTECTED] wrote:
Now instead of set connection static i just create a new one for each query to
perform, so if there are concurrent queries it shouldn't cause any problem:
private Connection getConnection() throws Exception {
// get context: provides the starting point for resolution of names
Context ctx = new InitialContext();
if (ctx == null) {
throw new Exception("No Context");
}
// retrieve datasource
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/xxDB");
if (ds == null) {
throw new Exception("No Datasource");
}
// return db connection
return ds.getConnection();
}
public void doSomething() {
Connection con = null;
Statement stmt = null;
try {
// get connection
con = getConnection();
if (con == null) {
throw new Exception("No Connection");
}
stmt = con.createStatement();
stmt.executeUpdate("UPDATE yy SET `zz`=0 WHERE `ww`='oo'");
}
catch (Exception e1) {
//
}
// close resources
finally {
try {
stmt.close();
stmt = null;
}
catch (Exception e2) {
//
}
try {
con.close();
con = null;
}
catch (Exception e3) {
//
}
}
}
is there anything else i should change for getting the pool connection to work?
Thanks in advance.
---------- Initial Header -----------
From : "[EMAIL PROTECTED]" [EMAIL PROTECTED]
To : "users" users@tomcat.apache.org
Cc :
Date : Sat, 21 Apr 2007 10:08:21 +0200
Subject : Cannot get a connection, pool exhausted
Hello,
i'm trying to achieve DBCP with tomcat 5.5.9. I thought to have done things right since
the application could connect to db, but after a night that the application was running,
in the morning, in logs, i saw a lot of "Cannot get a connection, pool
exhausted" errors.
This is my configuration:
SERVER.XML:
<Server port="8005" shutdown="SHUTDOWN">
<Listener className="org.apache.catalina.mbeans.ServerLifecycleListener" />
<Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />
<Listener className="org.apache.catalina.storeconfig.StoreConfigLifecycleListener" />
<GlobalNamingResources>
<Environment name="simpleValue" type="java.lang.Integer" value="30" />
<Resource name="UserDatabase" auth="Container" type="org.apache.catalina.UserDatabase" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" pathname="conf/tomcat-users.xml" />
</GlobalNamingResources>
<Service name="Catalina">
<Connector port="8080" maxHttpHeaderSize="8192" maxThreads="150" minSpareThreads="25" maxSpareThreads="75" enableLookups="false" redirectPort="8443" acceptCount="100" connectionTimeout="20000" disableUploadTimeout="true" />
<Connector port="8009" enableLookups="false" redirectPort="8443" protocol="AJP/1.3" />
<Engine name="Catalina" defaultHost="localhost">
<Realm className="org.apache.catalina.realm.UserDatabaseRealm"
resourceName="UserDatabase" />
<Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true" liveDeploy="true"
xmlValidation="false" xmlNamespaceAware="false">
</Host>
</Engine>
</Service>
</Server>
CONTEXT.XML:
<Context path="/xx" docBase="xx" debug="5" reloadable="true"
crossContext="true">
<Resource
name="jdbc/xxDB"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWait="10000"
username="user"
password="pass"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1:3306/xx_xx?autoReconnect=true"/>
</Context>
WEB.XML:
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd";
version="2.4">
<display-name>xx</display-name>
<listener>
<listener-class>xxx.ApplicationWatch</listener-class>
</listener>
<servlet>
<servlet-name>htmlcontent</servlet-name>
<servlet-class>xxx.HtmlContentServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>htmlcontent</servlet-name>
<url-pattern>/htmlcontent.view</url-pattern>
</servlet-mapping>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/xxDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
<security-constraint>
<display-name>Security Constraint</display-name>
<web-resource-collection>
<web-resource-name>Protected Area</web-resource-name>
<url-pattern>/*</url-pattern>
</web-resource-collection>
<auth-constraint>
<role-name>xx</role-name>
</auth-constraint>
</security-constraint>
<login-config>
<auth-method>BASIC</auth-method>
<realm-name>Protected Area</realm-name>
</login-config>
<security-role>
<role-name>xx</role-name>
</security-role>
</web-app>
THE WAY I CONNECT TO DB (this is one of the 6-7 methods i have to do stuff on
db):
private static Connection con = null;
private static Connection getConnection() throws Exception {
// get context: provides the starting point for resolution of names
Context ctx = new InitialContext();
if (ctx == null) {
throw new Exception("No Context");
}
// retrieve datasource
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/xxDB");
if (ds == null) {
throw new Exception("No Datasource");
}
// return db connection
return ds.getConnection();
}
public static void doSomething() {
Statement stmt = null;
try {
// get connection
con = getConnection();
if (con == null) {
throw new Exception("No Connection");
}
stmt = con.createStatement();
stmt.executeUpdate("UPDATE yy SET `zz`=0 WHERE `ww`='oo'");
}
catch (Exception e1) {
//
}
// close resources
finally {
try {
stmt.close();
stmt = null;
}
catch (SQLException e2) {
//
}
try {
con.close();
con = null;
}
catch (SQLException e3) {
//
}
}
}
Anybody have a clue of what can be? any help appreciated.
------------------------------------------------------
Passa a Infostrada. ADSL e Telefono senza limiti e senza canone Telecom
http://click.libero.it/infostrada
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
------------------------------------------------------
Passa a Infostrada. ADSL e Telefono senza limiti e senza canone Telecom
http://click.libero.it/infostrada
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
