java - 如何检测SQ​​LException的SQL错误状态

Question

我正在尝试制作JDBC连接池组件，但遇到了问题。即:如何检测java.sql.SQLException的错误状态。据说国家应该遵循JDK文档中的SQL2003约定和XOPEN SQL约定。但是我找不到任何关于这两个约定的文档。有人可以给我提供一下吗？

我想知道每个状态代表什么，以便我可以决定何时完全关闭连接或重新连接。

我引用了BoneCP的源代码。这是当 SQLException 发生时将被激活的部分:

    ImmutableSet<String> sqlStateDBFailureCodes = ImmutableSet.of("08001", "08007", "08S01", "57P01", "HY000"); 
    String state = e.getSQLState();
        ConnectionState connectionState = this.getConnectionHook() != null ? this.getConnectionHook().onMarkPossiblyBroken(this, state, e) : ConnectionState.NOP; 
        if (state == null){ // safety;
            state = "08999"; 
        }

        if (((sqlStateDBFailureCodes.contains(state) || connectionState.equals(ConnectionState.TERMINATE_ALL_CONNECTIONS)) && this.pool != null) && this.pool.getDbIsDown().compareAndSet(false, true) ){
            logger.error("Database access problem. Killing off all remaining connections in the connection pool. SQL State = " + state);
            this.pool.connectionStrategy.terminateAllConnections();
            this.pool.poisonAndRepopulatePartitions();
        }
char firstChar = state.charAt(0);
        if (connectionState.equals(ConnectionState.CONNECTION_POSSIBLY_BROKEN) || state.equals("40001") || 
                state.startsWith("08") ||  (firstChar >= '5' && firstChar <='9') /*|| (firstChar >='I' && firstChar <= 'Z')*/){
            this.possiblyBroken = true;
        }

        // Notify anyone who's interested
        if (this.possiblyBroken  && (this.getConnectionHook() != null)){
            this.possiblyBroken = this.getConnectionHook().onConnectionException(this, state, e);
        }

根据这些代码，当 SQLException 的状态等于“08001”、“08007”、“08S01”、“57P01”、“HY000”之一时，boneCP 将其视为数据库服务器崩溃.

但是为什么，这些状态代表什么？

Answer 1

前 2 个字符在 SQL92 specification 的“Table_23-SQLSTATE_class_and_subclass_values”中指定。

以下是相关内容的摘录:

00  success completion
01  warning
02  no data
07  dynamic SQL error
08  connection exception
0A  feature not supported
21  cardinality violation
22  data exception
23  integrity constraint violation
24  invalid cursor state
25  invalid transaction state
26  invalid SQL statement name
27  triggered data change violation
28  invalid authorization specification
2A  direct SQL syntax error or access rule violation
2B  dependent privilege descriptors still exist
2C  invalid character set name
2D  invalid transaction termination
2E  invalid connection name
33  invalid SQL descriptor name
34  invalid cursor name
35  invalid condition number
37  dynamic SQL syntax error or access rule violation
3C  ambiguous cursor name
3D  invalid catalog name
3F  invalid schema name
40  transaction rollback
42  syntax error or access rule violation
44  with check option violation
HZ  remote database access

其余字符取决于数据库供应商。因此，通常建议仅执行 startsWith() 检查。