Discussions

General J2EE: How to determine a connection to a database is Valid or Invalid

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

    Mohit
  2. you can use connection.isClosed(), this will return a boolean based on the connection status.
  3. 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
  4. 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
  5. Hi Mark,

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

    --Mohit
  6. 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
  7. 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/