...

...

So if you are using one of the "default*" properties and the JDBC driver correctly reports the SQLExceptions on the "set*" methods then you don't need an extra validationQuery.

Notes on DBCP Connection Validation

The following links are of use for the discussion:

• Wiki Markup
  \[http://jakarta.apache.org/commons/pool/apidocs/org/apache/commons/pool/PoolableObjectFactory.html#validateObject(java.lang.Object) org.apache.commons.pool.PoolableObjectFactory.validateObject(java.lang.Object)\]

• Wiki Markup
  \[http://jakarta.apache.org/commons/dbcp/apidocs/org/apache/commons/dbcp/PoolableConnectionFactory.html org.apache.commons.dbcp.PoolableConnectionFactory\]

• Wiki Markup
  \[http://jakarta.apache.org/commons/dbcp/apidocs/org/apache/commons/dbcp/PoolableConnectionFactory.html#validateConnection(java.sql.Connection) org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(java.sql.Connection)\]

• Wiki Markup
  \[http://jakarta.apache.org/commons/pool/apidocs/org/apache/commons/pool/impl/StackObjectPool.html org.apache.commons.pool.StackObjectPool\]

• Wiki Markup
  \[http://jakarta.apache.org/commons/pool/apidocs/org/apache/commons/pool/impl/GenericObjectPool.html org.apache.commons.pool.GenericObjectPool\] \\

Q: What is "Validation of Connections"?

Before a new Connection is obtained from a pool or before an existing one is returned to a pool it may be validated: a check is run to see whether the Connection has become stale - for example because the database unilaterally decided to close the underlying socket.

To validate the Connection, a validationQuery (e.g. 'SELECT 1') is passed to the constructor of org.apache.dbcp.PoolableConnectionFactory.

It is used in the following way:

org.apache.dbcp.PoolableConnectionFactory implements the interface method org.apache.commons.pool.PoolableObjectFactory.validateObject() whose description is:

Ensures that the instance is safe to be returned by the pool. Returns false if this object should be destroyed.

Let's make clear what this means by taking a peek at the implementation in PoolableConnectionFactory:

    public boolean validateObject(Object obj) {
        if(obj instanceof Connection) {
            try {
                validateConnection((Connection) obj);
                return true;
            } catch(Exception e) {
                return false;
            }
        } else {
            return false;
        }
    }

Note the call to PoolableConnectionFactory.validateConnection().

PoolableConnectionFactory.validateConnection() tries to run the validationQuery passed in the constructor. If this fails, it throws an SQLException and validateObject() will return false.

Q: When are Connections validated? In other words, when you have a PoolableConnectionFactory, when is its validateObject() method called on a Connection?

A: This depends on the pool implementation that you are using

Case 1: Your Pool is a org.apache.commons.pool.StackObjectPool

Connections are not validated when you borrow them from the pool. They are only validated - unconditionally - in returnObject(), which is called by addObject(). In other words, they are only validated if they are put into the pool, either immediately after creation or when return them to the pool by a call to Connection.close().

The consequence of this is that you will get Exceptions if you borrow a stale Connection because immediately after the Object has been pop()-ed from the stack, the factory's activateObject() is called on the Connection and the first thing it does is: set the autocommit default value. This will throw on a stale Connection.

Case 2: Your Pool is a org.apache.commons.pool.GenericObjectPool

Connections are validated in the following three GenericObjectPool methods:
evict(), borrowObject() and addObjectToPool(). Thus, GenericObjectPool gives you the possibility to also validate the Connections at 'borrow' time.

GenericObjectPool.borrowObject()

Only calls validateObject() if 'testOnBorrow' has been set. This parameter
is passed in the pool's constructor.
If validation fails the Connection has not been newly created, Connection is
discarded and a new one is created in its place.
Note that if the database goes away, you will see Exceptions coming out of
borrowObject() as newly created Connections will fail their validation.

GenericObjectPool.evict()

This method is called by the 'Evictor' which clears out idle Objects in the
pool. It unconditionally runs validateObject() on a pooled Object. If that method
returns false, the pooled Object is removed.

GenericObjectPool.addObjectToPool()

Only calls validateObject() if 'testOnReturn' has been set. This parameter
is passed in the pool's constructor. If the (returned) Object fails validation,
it is immediately destroyed.