ZK+Tomcat Data Connection Issue

asked 2009-04-23 03:42:44 +0800

leoyc
163 ● 3

Entironment:Java6.0+ZK3.60+Tomcat6.18

***********
context.xml in webapp\META-INF
contents like this:
********
<?xml version="1.0" encoding="UTF-8"?>
<Context reloadable="true" crossContext="true" antiResourceLocking="false" privileged="true" >
<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->

<Resource
name="jdbc/DBPool"
auth="Container"
type="javax.sql.DataSource"
maxActive="200"
maxIdle="30"
maxWait="10000"
logAbandoned="true"
username="root"
password="123456"
driverClassName="com.mysql.jdbc.Driver"
defaultAutoCommit="false"
url="jdbc:mysql://localhost:3307/coffeedb?autoReconnect=true"/>
</Context>

********
DBPool.java
********
package my;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;


public class DBPool {
private static DataSource pool;
static {
Context env = null;
try {
env = (Context) new InitialContext().lookup("java:comp/env");
pool = (DataSource)env.lookup("jdbc/DBPool");

if(pool==null)
System.err.println("'DBPool' is an unknown DataSource");
} catch(NamingException ne) {
ne.printStackTrace();
}
}
public static DataSource getPool() {
return pool;
}
}
**************
use pool like this:

import java.sql.*;
import my.DBPool;
Connection conn = null;
PreparedStatement stmt = null;
conn = DBPool.getPool().getConnection();
stmt = conn.prepareStatement("SELECT * FROM products where (pcode=? or qcode=?)");
stmt.setString(1,pcode.value);
stmt.setString(2,pcode.value);
rs=stmt.executeQuery();

but when webapp runing several days, it's wrong.
errors like this:

2009-4-23 9:37:09 org.zkoss.zk.ui.impl.UiEngineImpl handleError:702
严重: >>org.zkoss.zk.ui.UiException: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
>>org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
>>java.util.NoSuchElementException: Timeout waiting for idle object
>> at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:958)
>> at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)
>> at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>> at java.lang.reflect.Method.invoke(Unknown Source)
>> at bsh.Reflect.invokeMethod(Unknown Source)
>> at bsh.Reflect.invokeObjectMethod(Unknown Source)


Give me some advise,please.