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.

Answer 2

Yes, that would be Oracle-only, but there is no generic way to do this in JDBC.

Most connection pool implementations have a configuration parameter where you can specify the SQL that will be used for ping, thus pushing the responsiblity to figure out how to do it to the user.

That seems like the best approach unless someone comes up with a little helper tool for this (of course, it precludes using potentially even faster non-SQL-based methods like Oracle's internal ping function)