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?

Joe Sam Shirah

There were a number of responses to this question. I am including a distillation of these plus some additional information to consider.

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.

For those that still want to validate a Connection from within the pool, I did some testing. One often sees Connection.getMetaData() used for validation. However, some production quality drivers report zero time spent in this method, even over a 56K modem. This implies to me that those drivers just check for a DatabaseMetaData object existence and return; clearly there is not a trip to the DBMS server and back in zero time. I also considered just issuing a Connection.commit(), but I suspect that there are drivers smart enough to know when no transactions are pending and therefore just swallow the call for efficiency.

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.

swarraj kulkarni, Modha Kumar, Harikrishna Neerkaje and Ari Manninen also contributed to this answer.