连接断开的 Java 应用程序处理

Question

由于连接断开，我的应用程序似乎在一夜之间挂起(我认为这就是问题所在。)我如何构建我的应用程序以便它可以尝试建立新连接？

自事件发生后，我更新了我的应用程序使用的 getConnection() 方法:

private Connection getConnection() {
    boolean failed = false;
    try{
        failed = !connection.isValid(1000);
    }catch(SQLException ex){
        System.out.println("WARNING: Connection not valid!");
    }
    try{
        failed = connection.isClosed();
    }catch(SQLException ex){
        System.out.println("WARNING: Connection is closed!");
    }
    if(failed){
        System.out.println("Renewing connection");
        this.initializeConnection();
    }
    return connection;
}

这是输出:

** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: The last packet successfully received from the server was54521 seconds
ago.The last packet sent successfully to the server was 54521 seconds ago, which
  is longer than the server configured value of 'wait_timeout'. You should consi
der either expiring and/or testing connection validity before use in your applic
ation, increasing the server configured values for client timeouts, or using the
 Connector/J connection property 'autoReconnect=true' to avoid this problem.

STACKTRACE:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet success
fully received from the server was54521 seconds ago.The last packet sent success
fully to the server was 54521 seconds ago, which  is longer than the server conf
igured value of 'wait_timeout'. You should consider either expiring and/or testi
ng connection validity before use in your application, increasing the server con
figured values for client timeouts, or using the Connector/J connection property
 'autoReconnect=true' to avoid this problem.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1
074)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3246)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1917)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
        at com.mysql.jdbc.ConnectionImpl.setCatalog(ConnectionImpl.java:4962)
        at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMet
aData.java:1506)
        at com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(Databas
eMetaData.java:4120)
        at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.
java:4057)
        at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStat
ement.java:809)
        at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:609)
        at com.mysql.jdbc.JDBC4CallableStatement.<init>(JDBC4CallableStatement.j
ava:46)
        at sun.reflect.GeneratedConstructorAccessor6.newInstance(Unknown Source)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:5
05)
        at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.j
ava:3881)
        at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3965)
        at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3939)
        at com.protocase.hmiclient.db.HMIDatabaseAdapter.getAvailableBatchesForW
orkstation(HMIDatabaseAdapter.java:471)
        at com.protocase.hmiclient.views.WorkstationContainer.getBatches(Worksta
tionContainer.java:74)
        at com.protocase.hmiclient.views.BatchList.<init>(BatchList.java:55)
        at com.protocase.hmiclient.views.WorkstationContainer.goToBatchList(Work
stationContainer.java:56)
        at com.protocase.hmiclient.views.forms.BatchListControlPanel.refreshButt
onActionPerformed(BatchListControlPanel.java:118)
        at com.protocase.hmiclient.views.forms.BatchListControlPanel.access$200(
BatchListControlPanel.java:16)
        at com.protocase.hmiclient.views.forms.BatchListControlPanel$3.actionPer
formed(BatchListControlPanel.java:64)
        at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
        at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
        at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
        at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Sour
ce)
        at java.awt.Component.processMouseEvent(Unknown Source)
        at javax.swing.JComponent.processMouseEvent(Unknown Source)
        at java.awt.Component.processEvent(Unknown Source)
        at java.awt.Container.processEvent(Unknown Source)
        at java.awt.Component.dispatchEventImpl(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
        at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
        at java.awt.Container.dispatchEventImpl(Unknown Source)
        at java.awt.Window.dispatchEventImpl(Unknown Source)
        at java.awt.Component.dispatchEvent(Unknown Source)
        at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
        at java.awt.EventQueue.access$000(Unknown Source)
        at java.awt.EventQueue$1.run(Unknown Source)
        at java.awt.EventQueue$1.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown
Source)
        at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown
Source)
        at java.awt.EventQueue$2.run(Unknown Source)
        at java.awt.EventQueue$2.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown
Source)
        at java.awt.EventQueue.dispatchEvent(Unknown Source)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
        at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.net.SocketException: Software caused connection abort: socket wr
ite error
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(Unknown Source)
        at java.net.SocketOutputStream.write(Unknown Source)
        at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
        at java.io.BufferedOutputStream.flush(Unknown Source)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3227)
        ... 61 more


** END NESTED EXCEPTION **

Answer 1

此异常表明您只在应用程序启动期间打开连接一次，并在应用程序的生命周期内永远保持打开状态。这不好。数据库迟早会回收连接，因为它打开的时间太长了。您应该在 try block 的 finally block 中正确关闭连接，因为您正在打开它并对其执行查询。

例如

public Entity find(Long id) throws SQLException {
    Connection connection = null;
    // ...

    try {
        connection = database.getConnection();
        // ...
    } finally {
        // ...
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return entity;
}

如果您对此有性能担忧(这是非常合理的，因为连接是最昂贵的任务)，那么您应该使用连接池。它还透明地处理这种“连接丢失”问题。例如，BoneCP .请注意，同样在连接池的情况下，您仍应按照上述 JDBC 代码习惯用法关闭 finally block 中的连接。这将使它们可供重用。