java - What is the best way to 'ping' a database via JDBC?

Question

I'm trying to determine the best way to ping a database via JDBC. By 'best' I mean fast and low overhead. For example, I've considered executing this:

"SELECT 1 FROM DUAL"

but I believe the DUAL table is Oracle-specific, and I need something more generic.

Note that Connection has an isClosed() method, but the javadoc states that this cannot be used to test the validity of the connection.

Answer 1

With JDBC 4 you can use isValid(int) (JavaDoc) from the Connection Interface. This basically does the trial statement for you.

Some driver implement this by sending the correct dummy SQL to the database and some directly uses low level operations which reduces the parsing overhead.

However beware of the timeout, some drivers (DB/400 and Oracle Thin) do spawn a new time thread for each invocation, which is not really acceptable for most Pool validation scenarios). And Oracle also does not seem to use a prepared statement, so it’s kind of relying on the implicit cache.