Our java web application makes heavy use of the database. A given HTTP request may retrieve 4-5 connections to the database to complete its work. This current mechanism to perform a "select 1" or a "select 1 from dual" is FAR too expensive a request to validate that the connection is valid. So I am looking to use something that is lighter weight but still will perform some validation. My first thought was to change the validateObject method to use the Connection.isClosed method and if a SQLException is thrown, then the connection is no longer valid. For the JDBC driver I am using (inetsoftware - SQL Server - Merlia) this will at least verify that at least the socket is still open. I understand that in some cases, this would not throw an error, but the database is not available. However, in my testing the Connection.isClosed method call catches 'most' occurrences of failure. I am also thinking that the validation call when testOnIdle is enabled should do a full test using "select 1..." . So this would be two types of validation.
Has anyone run into these kind of performance problems and what solutions did you come up with? I do NOT want to turn off testOnBorrow because we are getting occasional socket errors in the application. Thanks for your input. ToddC Concur Technologies ICU Developer Lead
