Efficient way to validate a DB connection

Question

The DB driver I am working with (for a sybase DB) does not implement a Connection.isValid(). What is the most efficient way I can validate a db connection using a query (or otherwise) in Java?

Answer 1

Libraries such as C3P0 and DBCP allow you to provide a validation query which is typically something very simple such as "select 1". Hence, you could take the same approach or simply use either of these libraries (my recommendation would be C3P0).

However, rather than testing the connection prior to executing your query you could simply attempt to execute and then retry the operation if it fails because the connection is invalid. Alternatively you could consider a non-pooled connection approach where the connection is created on-the-fly each time (e.g. such as Spring's DriverManagerDataSource).

Answer 2

I mostly agree with Adamski's comment about using "select 1 from table" as an efficient way of checking connectivity and using connection pooling (e.g. commons-dbcp, C3PO).

Application servers (e.g. Websphere) allow you to configure this validation for you so your applciation code doesn't have to know about it. You have the choice of always having the connection checked prior to using it, or the connection pool being validated when a new connections is created. You can also purge connections periodically in case they get stale.

If you're not running in an application server you can use Common DBCP with the properties described here:

http://commons.apache.org/dbcp/configuration.html

Or C3PO and take a look at using the idleConnectionTestPeriod property. This link talks about use with Hibernate but its still relevant for C3PO generally

http://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool