Page History

...

...

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 According to the pooling implementation that underlies DBCP, objects in a pool are validated through a call to the interface method org.apache.commons.pool.PoolableObjectFactory.validateObject() whose . Its 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 PoolableConnectionFactoryorg.apache.dbcp.PoolableConnectionFactory implements that interface method:

    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?

...

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.

...

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 and the Connection has not been newly created, the Connection is
discarded and a new one is created in its place. However, if the Connections has
just been newly created, the Exception is left up the stack.
Note that if the database goes away, you will see Exceptions coming out of
borrowObject() as newly created Connections will fail their validation.

...

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.

Q: What if the pooled Connections are com.mysql.jdbc.Connection instances

This works perfectly well if you are using a GenericObjectPool that has 'testOnBorrow' set. Connections will be validated at borrow time. Any stale ones will be discarded, and new ones created in their place. The MySQL Connector/J Connection allows to set the parameter 'autoReconnect', but it is useless in this case: 'autoReconnect' just means that you will get an Exception when you run a query on a stale Connection, but that you can run the query again and the Connection will try to reconnect on this second try. As DBCP does not do second tries on the same Connection instance, the 'autoReconnect' case will not arise. Retrying twice before giving up is for example what a Tomcat 4 JDBCRealm does. (The above is not clear from the MySQL documentation and the behaviour one would intuitively give to 'autoReconnect' is misleading and so this is a potential headache.)

There is a 'autoReconnectForPools' starting from MySQL-Connector-J which I haven't looked into yet.

See \[http://dev.mysql.com/doc/connector/j/en/index.html#id2425656 Connector-J Connection Properties\] for additional details, and also the source for _com.mysql.jdbc.Connection.execSQL()_.