I am developing an application that must gracefully recover from
network failures, however I noticed that Statement.executeQuery never
throws an exception for a time out when the network goes down (which
I simulated by unplugging my ethernet cable). The TCP connections of
all my pooled database connections remain open and dead even after
plugging the cable back in. Calling Statement.setQueryTimeout explicitly has no
effect, Statement.executeQuery stays blocked on network IO forever. Is there a
way to time out Statement.executeQuery, or some way to stop it from never
returning?
I'm using version 10.2.0.1.0 of Oracle's JDBC driver with the 1.5 JDK.
I've simulated querying the database during network failure with the
following code:
import java.util.*;
import java.sql.*;
import oracle.jdbc.pool.*;
public class Oracle {
private OracleDataSource ods;
public Oracle() throws Exception {
ods = new OracleDataSource();
ods.setURL("jdbc:oracle:oci:@dev");
ods.setUser("dbp");
ods.setPassword("dbp");
ods.setConnectionCachingEnabled(true);
}
public void validate() {
Connection c = null;
Statement s = null;
ResultSet r = null;
try {
System.err.printf("connecting...\n");
c = ods.getConnection();
System.err.printf("connected\n");
s = c.createStatement();
s.setQueryTimeout(1);
for (;;) {
System.err.printf("executing...\n");
r = s.executeQuery("select 1 from dual");
System.err.printf("executed\n");
r.close();
try { Thread.sleep(1000); } catch (Exception e) { }
}
} catch (Exception e) {
e.printStackTrace();
try { r.close(); } catch (Exception ee) { }
try { s.close(); } catch (Exception ee) { }
try { c.close(); } catch (Exception ee) { }
}
}
public static void main(String[] args) {
Oracle oracle = null;
try {
oracle = new Oracle();
oracle.validate();
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
}
}
}
Here is the output:
javac -cp $ORACLE_HOME/jdbc/lib/ojdbc14.jar Oracle.java && java -cp .:$ORACLE_HOME/jdbc/lib/ojdbc14.jar Oracle
connecting...
connected
executing...
executed
(ethernet cable unplugged...)
executing...
(i waited a very long time, and nothing happened...)