programmatically checking for open connection in JDBC

Question

How do I check for an open connection in jdbc for oracle database?

Note: conn.isClosed() cannot be used for this.

Answer 1

Something like:

Statement stmt = null;
ResultSet rs =null;
try {
   stmt = conn.createStatement();
   // oracle
   rs = stmt.executeQuery("SELECT 1 FROM Dual");
   // others
   // rs = stmt.executeQuery("SELECT 1");
   if (rs.next())
      return true; // connection is valid
}
catch (SQLException e) {
   // TODO : log the exception ...
   return false;
}
finally {
   if (stmt != null) stmt.close();
   if (rs != null) rs.close();
} 

Note that if the connection is coming from a Connection Pool (in a Application Server for example) then the Pool may have a mechanism to check if a connection is valid or not. With BEA, you specify the SELECT in the "test-on-reserve" property.

If you are developing your own pool then you may want to take a look at how others are doing it (ex. Proxool).

Answer 2

Usually a Connection Pool will also use the Connection.isClosed() method to check if the Connection is still valid. The problem is that not all JDBC drivers will handle this call correctly. So I assume that there are some simple check statements just like RealHowTo said. For Oracle he already mentioned the "SELECT 1 FROM Dual" which should succeed always for Oracle databases. I think that there are similar queries for the different database. I can remember that in a previous project we also implemented an own Connection Pool which used such validation queries.