Hi, I would like to know how to determine whether a connection to database is valid or not.

Mohit

you can use connection.isClosed(), this will return a boolean based on the connection status.

Hi Senthil

The method Connection.isClosed is guaranteed to return true only when it is called after the Connection.close() method has been called. As a result, i cannot depend on this method to indicate whether a connection is valid or not. Instead, a typical JDBC client can determine that a connection is invalid by catching the exception that is thrown when a JDBC operation is attempted.

But i don't want that exception to be thrown. I am able to check whether my connection is expired or not by this logic If (maxIdleTimeoutSecs + lastTimeConnectionUsed >= new Date().getTime()) i expire the connection and reagain establish that. But If i already had the connection which is not expired then how do i validate that?

Thanks,
Mohit

A lightweight "validation query" is often used to test connections. The Apache commons db connection pool for example has a "validation query" property you can set to define the lightweight SQL call to be made.

For oracle use:
Select 1 from dual

For SQL Server:
Select 1

MySQL db connections dont need this- the jdbc driver can automatically attempt reconnects if the jdbc url parameter "autoReconnect" is set to true.

Cheers,
Mark

Hi Mark,

Is that is the only possible way ?
nway i have tried that and it works out.
Thanks a lot.

--Mohit

It's the only way I've seen it done.
This is typically done centrally by connection pools upon request for a connection from the pool. This avoids client code from having to worry about identifying any disconnects, implementing a policy to attempt to re-establish connectivity and logging any warnings.

Cheers
Mark

can anyone tell me where to set the property for connection validation in dbcp? I just tried out the ManualPoolingDriverExample.java from apache dbcp, but does not know if the connection validation is turned on or not. can anyone drop me a line? thanks/