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

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)

Answer 2

I may be out to lunch on this one, but could you simply execute some non-sense query, such as:

SELECT * FROM donkey_giraffe_87

I don't know very much about JDBC's error handling, but perhaps you could check to see if the database is at least telling you that the table does not exist. If JDBC's error codes are vendor-specific, the Spring Framework has some utilities for mapping these codes to more meaningful exceptions.

Answer 3

I'm not aware of a generic solution, either. For IBM's UDB on iSeries (and perhaps other DB2 systems) it would be

select 1 from SYSIBM.SYSDUMMY1;

Answer 4

Simply issuing the following query should be sufficient

SELECT 1

Answer 5

Can you not simply execute

SELECT 1

without a FROM clause against most databases?