Tomcat JDBC Connection Pool: testOnBorrow vs testWhileIdle : <p>For various reasons connections in a pool can become invalid: server connection timeout, network issues...</p>

<p>My understanding is that a Tomcat JDBC Connection Pool does not provide any guaranty about the validity of the connections it provides to the application.</p>

<p>To prevent (actually only lower the risk) getting an invalid connection from the pool a solution seems to be the configuration of connections validation. Validating a connection means to run a very basic query on the database (e.g. <code>SELECT 1;</code> on MySQL).</p>

<p>Tomcat JDBC Connection Pool offers several options to test the connection. The two I find the more interesting are <code>testOnBorrow</code> and <code>testWhileIdle</code>.</p>

<p>First I was thinking that <code>testOnBorrow</code> is the best option because it basically validate the connection before providing it to the application (with a max frequency defined by <code>validationInterval</code>).</p>

<p>But after a second though I realized that testing the connection right before using it might impact the responsiveness of the application. So I though that using <code>testWhileIdle</code> can be more efficient as it test connections while they are not used.</p>

<p>No matter which option I choose it seems that they only lower the risk from getting an invalid connection but this risk still exist.  </p>

<p>So I end up asking: should I use <code>testOnBorrow</code> or <code>testWhileIdle</code> or a mix of both?</p>

<p>On a side note, I'm surprised that <code>validationInterval</code> does not apply to <code>testOnReturn</code> and I don't really get the purpose of <code>testOnConnect</code>.</p>
