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.

Do you want to know if your app has a connection open or do you want to know if the DB has a connection open to anything. — sblundy, Nov 08 '08 at 15:38
check it out [Java JDBC connection status](http://stackoverflow.com/a/30888132/782535) — Madan Sapkota, Jun 17 '15 at 09:54
Since JRE 6 a method [`conn.isValid(timeout)`](http://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#isValid-int-) was introduced for this specific purpose and it is really implemented in many JDBC drivers. — mas.morozov, Sep 02 '15 at 21:01

score 8 · Answer 1 · edited Apr 30 '14 at 13:06

8

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

edited Apr 30 '14 at 13:06

bluish

26,356
27
122
180

answered Nov 08 '08 at 16:07

RealHowTo

34,977
11
70
85

Depending on your environment, your Connection Pool SHOULD be checking the connection. Otherwise if you lose connectivity to the DB you typically have to restart you application server to restart the pool. – Will Hartung Nov 08 '08 at 16:27
I strongly suspect that the executeQuery will throw an SQLException if the connection is closed. – Chris Vest Nov 08 '08 at 21:04

score 2 · Accepted Answer · answered Nov 19 '08 at 11:41

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.

score 1 · Answer 3 · answered Nov 19 '09 at 15:24

1

Use pingDatabase(int timeout) Implemented in OracleConnection since 9.0.1

answered Nov 19 '09 at 15:24

Jörgen

11
1

Note all pingDatabase(int) does in the Type IV driver is a "select 'x' from dual;" – leonigmig Jan 27 '12 at 09:23

score 1 · Answer 4 · edited May 23 '17 at 12:13

1

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

edited May 23 '17 at 12:13

Community

1
1

answered Jan 05 '09 at 18:16

Nicholas

15,916
4
42
66

Note all pingDatabase(int) does in the Type IV driver is a "select 'x' from dual;" – leonigmig Jan 27 '12 at 09:47

programmatically checking for open connection in JDBC

4 Answers4

Linked