Catching database resources leaks with Tomcat - howto

This it a solution of "Cannot get a connection, pool error Timeout waiting for idle object" error message. It guides how to find your resource leak.

1. Add the red properties to your context.xml in this way:
  <Resource name="jdbc/MyAppDS" auth="Container" type="javax.sql.DataSource"
            maxActive="10" maxIdle="10" maxWait="10000" defaultAutoCommit="false"
            username="user_dev" password="passssss" driverClassName="com.mysql.jdbc.Driver"
            removeAbandoned="true" removeAbandonedTimeout="25" logAbandoned="true"
            url="jdbc:mysql://my-mysqlserver.com:3306/databasename_dev?autoReconnect=true&amp;characterEncoding=UTF-8"/>

2. Reproduce your problem to full the active connection. In the above configuration, I have limited them to 10, to reproduce the problem sooner.

3. Watch the stdout output for log of the abandoned connection. It is something like this:
org.apache.tomcat.dbcp.dbcp.AbandonedTrace$AbandonedObjectException: DBCP object created 2012-05-03 15:51:35 by the following code was never closed:
    at org.apache.tomcat.dbcp.dbcp.AbandonedTrace.setStackTrace(AbandonedTrace.java:139)
    at org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:81)
    at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
    at com.yourcompany.getDataSource(XXX.java:55)
...
Caused by: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
        at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:104)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)           
        ... 19 more
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
        at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:825)
        at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)
        ... 23 more

3.1 If you do not have stdout.log file in Windows, check the link.
4. Correct the XXX.java file as you close the resource after its usage. It could not be easier!