I've recently had problems with a webapp running in tomcat trying to use a stale jdbc connection via the apache commons database connection pools library (dbcp). I fixed this problem by adding a validation query to the configuration in the webapp's context.xml file. The documentation
for dbcp options is here:
http://jakarta.apache.org/commons/dbcp/configuration.html
The change involves adding the testOnBorrow parameter explicitely to the
configuration (although by default this is set to true). However, for the testOnBorrow setting to work, you have to specify a select sql query for dbcp to validate against the database before the connection is handed to the user code. In my case, I reused the basic select from the dbcp examples 'select 1' - use the validationQuery parameter to specify the sql query.
Below is an example of the working configuration.
<Resource name="jdbc/mydatabase" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/mydatabase">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>org.gjt.mm.mysql.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://db:3306:mydatabaseauto?Reconnect=true</value>
</parameter>
<parameter>
<name>username</name>
<value>myusername</value>
</parameter>
<parameter>
<name>password</name>
<value>mypassword</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>20</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>10</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>-1</value>
</parameter>
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select 1</value>
</parameter>
</ResourceParams>