访问已断开连接的远程数据库的数据库链接后,是否可以恢复JDBC数据库连接？
我们有一个使用单个连接到(本地)oracle数据库的应用程序,但偶尔通过数据库链接(REMOTE_DB)从远程数据库读取数据.
问题是如果远程数据库由于某种原因(网络断开连接)而脱机,则在访问数据库链接后,jdbc连接将变得不可用.
我执行以下三个SQL语句：

1. SELECT 1 FROM DUAL@REMOTE_DB => ok
<<Network failure>>
2. SELECT 1 FROM DUAL@REMOTE_DB => SQLException. 
3. SELECT 1 FROM DUAL => SQLException. 

使用JDBC驱动程序ojdbc6.jar的特定Java异常与语句2和3一起出现

    java.sql.SQLRecoverableException: No more data to read from socket
    at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1185)

我认为这种行为不是“按设计”的原因是当我使用SQLPlus或Perl DBI执行相同的序列时,不会发生同样的问题. Oracle 11与多个版本的Oracle瘦JDBC驱动程序一起出现问题.以下java程序可用于重现该问题.

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class TestJdbc {
    private static Connection connect() throws Exception {
        String jdbcURL = "jdbc:oracle:thin:@localhost:1521:TNSNAME";
        String user = "scott" ;
        String passwd ="tiger";

        Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
        return DriverManager.getConnection(jdbcURL,user,passwd);
    }

    public static void main(String[] args) throws Exception {
        Connection conn = connect();
        PreparedStatement stServer = conn.prepareStatement("SELECT 'server' FROM DUAL@REMOTE_DB");
        PreparedStatement stClient = conn.prepareStatement("SELECT 'client' FROM DUAL");
        ResultSet resultSet;

        try {
            stServer.execute();
            resultSet = stServer.getResultSet();
            if (resultSet.next()) {
                System.out.println("server: " + resultSet.getString(1));
            }
        } catch (SQLException e) {
            System.out.println("exception on server link: " + e);
        }
        // force network disconnect here and press enter
        BufferedReader lineOfText = new BufferedReader(new InputStreamReader(System.in));
        lineOfText.readLine();

        try {
            stServer.execute();
            resultSet = stServer.getResultSet();
            if (resultSet.next()) {
                System.out.println("server: " + resultSet.getString(1));
            }
        } catch (SQLException e) {
            //SQLRecoverableException occurs here
            System.out.println("exception on server link: " + e);
        }
        // press enter again
        lineOfText.readLine();

        try {
            stClient.execute();
            resultSet = stClient.getResultSet();
            if (resultSet.next()) {
                System.out.println("client: " + resultSet.getString(1));
            }
        } catch (SQLException e) {
            System.out.println("exception on client connection: " + e);
        }

        stServer.close();
        stClient.close();
    }

}

关闭并重新打开连接将解决问题,但最好不要这样做,因为我们可能在发生错误时处于事务的中间.

编辑：请注意,使用SQLPlus我可以执行以下操作,使用JDBC连接池的问题无法解决：

SQL> update my_table set ...;

1 row updated.

SQL> select * from dual@REMOTE_DB;

D
-
X

<<Network failure>>

SQL> select * from dual@REMOTE_DB;
select * from dual@REMOTE_DB
               *
ERROR at line 1:
ORA-12545: Connect failed because target host or object does not exist


SQL> update my_table set ...;

1 row updated.

SQL> commit;

Commit complete.

SQL> 

解决方法:

使用连接池,例如Apache DBCP http://commons.apache.org/proper/commons-dbcp/,它们会自动恢复失败的连接.它也是使用数据库连接的首选方式.