java - 无法清除已检查异常上的 java.sql.Statement

Question

在下面显示的我的方法中，发现错误指定无法清除已检查异常的 java.sql.Statement

    public int updateSecurityCodeHistoryForMessage(String phone, String securityCodeHistoryId, String messageState, String messageId, String parentMessageId)
    {
            CaptivePortalLogger.appLog.error(MODULE+"Start : updateSecurityCodeHistoryForMessage::"+messageState);
            int result=-1;

        String query=null;
        Connection con = null;
        PreparedStatement pstmt =null;
        try
        {
            CaptivePortalLogger.sysOut.debug(MODULE + " (Method : isSecurityCodeUsed) Available Connection : "+ CaptivePortalDBConnection.getNumIdleConnections());
            CaptivePortalLogger.sysOut.debug(MODULE + " (Method : isSecurityCodeUsed) Active Connection : "+ CaptivePortalDBConnection.getNumActiveConnections() );

            con = CaptivePortalDBConnection.getDataSource().getConnection();
            CaptivePortalLogger.appLog.error(MODULE+" Before updateSecurityCodeHistoryForMessage into SendMessageAndReceiveReport: ");              

            query="UPDATE tblsecuritycodehistory SET messagestate = ?,messageid = ? WHERE securitycodehistoryid = ? AND mobileno = ?";

            CaptivePortalLogger.appLog.debug(MODULE + "for updateSecurityCodeHistoryForMessage in SendMessageAndReceiveReport Query : "+ query);

            pstmt = con.prepareStatement(query);
            pstmt.setString(1,messageState); //<b>line 556</b>
            pstmt.setString(2,messageId);
            pstmt.setString(3,securityCodeHistoryId);
            pstmt.setString(4,phone);
            result = pstmt.executeUpdate();

            CaptivePortalLogger.appLog.error(MODULE+" After updateSecurityCodeHistoryForMessage into SendMessageAndReceiveReport: result::"+result);
        }
        catch (Exception e) {
            result = -1;
            CaptivePortalLogger.traceLog.debug("Got an exception while updateSecurityCodeHistoryForMessage in SendMessageAndReceiveReport: ",e);
        }
        finally
        {
            CaptivePortalLogger.appLog.debug(MODULE+"Finally Start");
            try
            {
                if(pstmt!=null)
                    pstmt.close();
                if(con !=null)
                    con.close();

                CaptivePortalLogger.sysOut.debug(MODULE + " (Method : updateSecurityCodeHistoryForMessage) Closing connections done ....");

            }
            catch(Exception e)
            {
                CaptivePortalLogger.traceLog.debug("Error in closing sqlReader.",e);
            }
        }
    CaptivePortalLogger.appLog.error(MODULE+"End : updateSecurityCodeHistoryForMessage");
    return result;
}

我在堆栈上找到了很多链接，但没有一个能够解决我的问题(可能是我无法正确理解它们)。任何帮助将不胜感激。

提前致谢......

使用@Mark指定的解决方案更新我的finally block 后，问题仍然存在

finally
{
    CaptivePortalLogger.appLog.debug(MODULE+"Finally Start");
    try {
        if(pstmt!=null)
            pstmt.close();
    } catch (Exception ex) {
        // Log, ignore, etc
    }
    try {
        if(con !=null)
            con.close();
    } catch (Exception ex) {
        // Log, ignore, etc
    }
    CaptivePortalLogger.sysOut.debug(MODULE + " (Method : updateSecurityCodeHistoryForMessage) Closing connections done ....");
}

使用@Jon建议后，我的问题得到解决。最终解决的代码是::

public int updateSecurityCodeHistoryForMessage(String phone, String securityCodeHistoryId, String messageState, String messageId, String parentMessageId)
    {
            CaptivePortalLogger.appLog.error(MODULE+"Start : updateSecurityCodeHistoryForMessage::"+messageState);
            int result=-1;
            String query=null;
            Connection con = null;
            PreparedStatement pstmt =null;
            try
            {
                CaptivePortalLogger.sysOut.debug(MODULE + " (Method : isSecurityCodeUsed) Available Connection : "+ CaptivePortalDBConnection.getNumIdleConnections());
                CaptivePortalLogger.sysOut.debug(MODULE + " (Method : isSecurityCodeUsed) Active Connection : "+ CaptivePortalDBConnection.getNumActiveConnections() );

                con = CaptivePortalDBConnection.getDataSource().getConnection();
                CaptivePortalLogger.appLog.error(MODULE+" Before updateSecurityCodeHistoryForMessage into SendMessageAndReceiveReport: ");              

                query="UPDATE tblsecuritycodehistory SET messagestate = ?,messageid = ? WHERE securitycodehistoryid = ? AND mobileno = ?";

                CaptivePortalLogger.appLog.debug(MODULE + "for updateSecurityCodeHistoryForMessage in SendMessageAndReceiveReport Query : "+ query);
                try
                {
                    pstmt = con.prepareStatement(query);
                    pstmt.setString(1,messageState);
                    pstmt.setString(2,messageId);
                    pstmt.setString(3,securityCodeHistoryId);
                    pstmt.setString(4,phone);
                    result = pstmt.executeUpdate();
                }
                catch(SQLException e1)
                {
                    CaptivePortalLogger.traceLog.debug("Error in closing sqlReader.",e1);
                }
                finally{
                    if(pstmt!=null)
                    pstmt.close();
                }

                CaptivePortalLogger.appLog.error(MODULE+" After updateSecurityCodeHistoryForMessage into SendMessageAndReceiveReport: result::"+result);
            }
            catch (SQLException e2) {
                result = -1;
                CaptivePortalLogger.traceLog.debug("Got an exception while updateSecurityCodeHistoryForMessage in SendMessageAndReceiveReport: ",e2);
            }
            finally
            {
                CaptivePortalLogger.appLog.debug(MODULE+"Finally Start");
                try
                {
                    if(con !=null)
                        con.close();

                    CaptivePortalLogger.sysOut.debug(MODULE + " (Method : updateSecurityCodeHistoryForMessage) Closing connections done ....");

                }
                catch(SQLException e)
                {
                    CaptivePortalLogger.traceLog.debug("Error in closing sqlReader.",e);
                }
            }
        CaptivePortalLogger.appLog.error(MODULE+"End : updateSecurityCodeHistoryForMessage");
        return result;
    }

Answer 1

看看这段代码:

if(pstmt!=null)
    pstmt.close();
if(con !=null)
    con.close();

现在考虑 pstmt.close() 可以抛出异常...这意味着 con.close() 不会被调用。

如果您使用的是 Java 7，请改用 try-with-resources 语句，否则您应该为每个资源使用单独的 try/finally block 。

try {
   connection = ...;
   try {
      statement = ...;
   } finally {
      // Clean up statement
   }
} finally {
   // Clean up connection
}

我还强烈建议不要捕获一揽子异常 - 最好捕获您可以实际处理的特定异常，并让其他人异常在堆栈中向上传播。另外，您似乎使用整数值来表示方法的成功或失败 - 这不是惯用的 Java；一般来说，异常是错误处理的首选。