Connection validation ensures that connections aren’t assigned to your application after the connection has already gone stale.
Connection validation is always a trade-off between how sure you want to be that a connection is valid and the performance impact that the validation mechanism can exert into application that use the pool. There will be a negative performance impact by having to return an invalid connection by your application and borrow a new one, so finding the right balance is key.
Before using a connection from the pool a simple query is sent to test the connection. If there is an issue with the connection, it is removed from the pool and another one is used. If you have an issue such as the database being down and you have a large number of connections then each of those connections will be tested and removed.
In order to avoid this, you can set a connection validation so that if a connection fails, all remaining open connections are closed.
Set up the validation using the admin console
Click on the name of the connection pool
Select the Advanced tab
Scroll down to the Connection Validation section and select the following settings:
Connection Validation required
Validation method - custom-validation
Validation class name – a proper class name for the chosen JDBC driver, such as
org.glassfish.api.jdbc.validation.DerbyConnectionValidationfor a Derby DB
From the same screen, you can also set whether to close all connections on failure.
Set up the validation using administration commands
To turn on connection validation using
asadmin> set resources.jdbc-connection-pool.test-pool.connection-validation-method=custom-validation asadmin> set resources.jdbc-connection-pool.test-pool.validation-classname= org.glassfish.api.jdbc.validation.DerbyConnectionValidation asadmin> set resources.jdbc-connection-pool.test-pool.is-connection-validation-required=true
You can also set whether to close all connections on failure with the following command:
asadmin> set resources.jdbc-connection-pool.test-pool.fail-all-connections=true