What is the best strategy to determine that the DBMS is up and that the Connections from my pool are still viable before handing them out?
It should be obvious that: A) Some statement that requires a database engine response must be executed. B) That any interaction with the DBMS engine will take some time and therefore slow down application responsiveness. C) That the validity of the Connection can only be guaranteed as of the time of the last check, meaning that the class using the method must still check for exceptions.
With these in mind, my own conclusions are that the application should handle any problems with invalid Connections using a standard exception handler for this case and that connection pools should have a returnInvalidConnection method to insure that the bad Connection is not checked out again.
In the end, I have concluded that, for certainty across drivers, a query probably must be issued. One possibility would be to create a table with no rows at pool initialization time and perform a SELECT COUNT(*) on it for validation. COUNT() is generally optimized for SELECTs without search criteria. jGuru would be happy to receive feedback from anyone who has tested timings on other methods guaranteed to force a response from the DBMS engine.