Apache Tomcat: Connection Pool Manager - Quick Config to Self Recover After DB Restart

Product: Apache Tomcat
Version: 7, 8, 9

According to following Apache Tomcat documentation, testOnBorrow is off by default:

* Tomcat 7: https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

* Tomcat 8.5: https://tomcat.apache.org/tomcat-8.5-doc/jdbc-pool.html

* Tomcat 9.0: https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html

However, this document mentioned following that is confusing on how many DB connection pool managers it provides, quote:

Section: Introduction

The JDBC Connection Pool org.apache.tomcat.jdbc.pool is a replacement or an alternative to the Apache Commons DBCP connection pool

Section: How to Use

Usage of the Tomcat connection pool has been made to be as simple as possible, for those of you that are familiar with commons-dbcp, the transition will be very simple. Moving from other connection pools is also fairly straight forward

Section: Inside The Apache Tomcat Container

The Tomcat Connection pool is configured as a resource described in The Tomcat JDBC documentation With the only difference being that you have to specify the factory attribute and set the value to org.apache.tomcat.jdbc.pool.DataSourceFactory

What does this means?  Tomcat 7 onward offers 2 types of DB connection pool manager that you can choose:

1. Apache Common DBCP - full feature and heavy duty connection pool manager. For its default configuration values' documentation, refers to https://commons.apache.org/proper/commons-dbcp/configuration.html, but not Tomcat's documentation. Example of [Tomcat]\conf\context.xml"

<Resource name="jdbc/TestDB"
          auth="Container"
          type="javax.sql.DataSource"
          username="root"
          password="password"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/mysql"/>

2. org.apache.tomcat.jdbc.pool - Lightweight DB connection pool manager. context.xml will has additional factory=org.apache.tomcat.jdbc.pool.DataSourceFactory parameter. Example of [Tomcat]\conf\context.xml:

<Resource name="jdbc/TestDB"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          username="root"
          password="password"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/mysql"/>

A big different between them is that the default value for testOnBorrow is different, so DBCP will drop broken DB connections, while org.apache.tomcat.jdbc.pool.DataSourceFactory won't:

1. Apache Common DBCP - testOnBorrow=true
2. Tomcat org.apache.tomcat.jdbc.pool.DataSourceFactory - testOnBorrow=false

Out of curiosity, I spin up Tomcat 9.0.21 without specifying "factory" parameter, and used jconsole.exe (Java Console) to check folder "Catalina - DataSource" detail in MBeans tab as shown below:

Clicking on "Attributes" menu on the left, and I can see the actual value of the JNDI/JDBC data source configuration showing testOnBorrow=true by default:

In other word, to configure testOnBorrow in Apache Tomcat using its build-in DBCP JNDI/JDBC connection pool data source, you just need to configure parameter validationQuery into [Tomcat]/conf/context.xml or [Tomcat]/webapp/[app]/META-INF/context.xml <Resource> xml tag.

If you used org.apache.tomcat.jdbc.pool.DataSourceFactory, then jconsole.exe will display additional folder "tomcat.jdbc" at the bottom of MBeans tab as shown below: