如何避免JDBC连接关闭到Oracle DB（SQLRecoverableException关闭连接）？

Question

我编写了以下代码来连接到Oracle数据库。这用于REST API。我正在连接的数据库的IDLE_TIME参数设置为30分钟（这不能更改）。如果数据库连接空闲超过30分钟，则会抛出以下错误：“java.sql.SQLRecoverableException： ClosedConnection”。抛出此错误后，API将停止工作。我如何解决这个问题？

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.Properties;
import java.util.Queue;
import java.util.ResourceBundle;
import java.sql.SQLRecoverableException;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;


public class OrcConnector {

    private Queue<Connection> connections;

    private int maxConnections;

    private static final int MAX_CONNECTIONS_DEFAULT = 10;

    public OrcConnector() {

        connections = new LinkedList<Connection>();
        maxConnections = MAX_CONNECTIONS_DEFAULT ;

    }

    public synchronized int getMaxConnections(){
        return maxConnections;
    }

    public synchronized void setMaxConnection (int maxConnections) {
        this.maxConnections = maxConnections;
    }

    public synchronized ConnWrapper getConnection() throws SQLException, BusyConnections {
        Connection availConn;

        System.out.println("I am in OrcConnection");

            if (connections.size() > 0) {
                availConn = connections.poll();
            } else if (connections.size() < maxConnections) {
                Properties prop = new Properties();
                ResourceBundle resource = ResourceBundle.getBundle("database");
                String url = resource.getString("db.url");
                String user = resource.getString("db.user");
                String pass = resource.getString("db.password");
                prop.put("user", user);
                prop.put("password", pass);
                prop.put("autoReconnect", "true");
                prop.put("characterEncoding", "UTF-8");
                prop.put("useUnicode", "true");
                availConn = DriverManager.getConnection(url,user,pass);
                System.out.println("I am in OrcConnection");
            } else {
                throw new BusyConnections();
            }
            return new ConnWrapper(availConn, this);
    }

    public synchronized void putConnection(Connection conn) {
        if (conn != null) {
            if (connections.size() < maxConnections) {
                connections.add(conn);
            } else {
                try {
                    conn.close();
                } catch (SQLException e) {
                    System.out.println("connection could not be closed" + e.getMessage());
                }
            }
        }
    }


}

Answer 1

我建议您使用UCP（通用连接池），这是Oracle的连接池，可以在这里找到，然后使用借用时验证功能。这样，当您需要连接时，您可以让UCP测试连接的有效性，如果需要，它会重新连接。