The DB driver I'm dealing with (for any sybase DB) doesn't implement an association.isValid(). What's the most effective way I'm able to validate a db connection utilizing a query (or else) in Java?
Libraries for example C3P0 and DBCP permit you to give a validation query that is typically something quite simple for example "choose 1". Hence, you might take exactly the same approach or just use either of those libraries (my recommendation could be C3P0).
However, instead of testing the bond just before performing your query you can simply make an effort to execute after which retry the operation whether it fails since the connection is invalid. Alternatively you could look at a non-put connection approach in which the connection is produced on-the-fly every time (e.g. for example Spring's
I mostly accept Adamski's comment about using "choose 1 from table" being an efficient method of checking connectivity and taking advantage of connection pooling (e.g. commons-dbcp, C3PO).
Application servers (e.g. Websphere) permit you to configure this validation for you personally so that your applciation code does not need to know about this. You will find the selection of always getting the bond checked just before utilizing it, or even the connection pool being validated whenever a new connections is produced. You may also purge connections periodically just in case they get stale.
If you are not running within an application server you should use Common DBCP using the qualities referred to here:
Or C3PO and have a look at while using idleConnectionTestPeriod property. This link discusses use with Hibernate nevertheless its still relevant for C3PO generally