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

I am actually trying to implement a Connection Pool. And I am interested in knowing how it happens in an Application Server.

As you mentioned:

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.,

I am interested to know what that mechanism is?

Answer 3

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.

Answer 4

See this posting.

The referenced solutions are similar to the one posted here (quick query against DUAL to validate) but there is also an interesting solution provided by JBoss specific to Oracle using the proprietary PING method in the Oracle JDBC Connection class. See the code here.

//Nicholas

Answer 5

Use pingDatabase(int timeout) Implemented in OracleConnection since 9.0.1