Spring Boot未捕获PostgreSQL触发器SQLState异常：Angular收500，Postman收400

我正在开发一个Spring Boot + PostgreSQL项目，通过PostgreSQL触发器函数结合RAISE EXCEPTION和自定义SQLState码抛出自定义异常，同时用Spring全局异常处理器捕获并返回对应提示。

PostgreSQL触发器函数 #

CREATE OR REPLACE FUNCTION iot.f_bd_reset_mepo_references()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    haveRule integer := 0;
    haveEchart integer := 0;
    haveAlarm boolean := false;
BEGIN
    SELECT COUNT(*), cond_is_active INTO haveRule, haveAlarm
    FROM condition 
    WHERE cond_mepo_code = OLD.mepo_code AND client_code = OLD.client_code 
    GROUP BY cond_is_active;

    SELECT COUNT(*) INTO haveEchart
    FROM echartconfig
    WHERE echart_pk_mepo = OLD.pk_measure_point AND client_code = OLD.client_code
    GROUP BY echart_code;

    IF haveAlarm THEN
        RAISE EXCEPTION SQLSTATE 'P0001'; -- Measure point has an alarm in progress
    ELSIF haveRule > 0 THEN
        RAISE EXCEPTION SQLSTATE 'P0002'; -- Referenced to a rule
    ELSIF haveEchart > 0 THEN
        RAISE EXCEPTION SQLSTATE 'P0003'; -- Referenced to a graphic
    ELSE
        DELETE FROM alarm WHERE alarm_mepo_code = OLD.pk_measure_point AND client_code = OLD.client_code;
        DELETE FROM echartconfig WHERE echart_pk_mepo = OLD.pk_measure_point AND client_code = OLD.client_code;    
        DELETE FROM feedback WHERE sens_code = OLD.mepo_code AND client_code = OLD.client_code;
        RETURN OLD;
    END IF;
END;
$BODY$;

Spring服务代码 #

@Transactional(rollbackFor = {SQLException.class})
public void deleteById(Integer id) {
    repository.deleteById(id); // Triggers the DB function
}

全局异常处理器 #

@ControllerAdvice
public class GlobalExceptionHandler {

    @ExceptionHandler(Exception.class)
    public ResponseEntity<?> handleAnyException(Exception ex, WebRequest request) {
        Throwable root = findSQLException(ex);
        if (root instanceof SQLException) {
            String sqlState = ((SQLException) root).getSQLState();
            String customMessage = mapErrorMessage(sqlState);
            return new ResponseEntity<>(customMessage, HttpStatus.BAD_REQUEST);
        }

        return new ResponseEntity<>("Internal Server Error: " + ex.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
    }

    private Throwable findSQLException(Throwable ex) {
        while (ex != null) {
            if (ex instanceof SQLException) return ex;
            ex = ex.getCause();
        }
        return null;
    }

    private String mapErrorMessage(String sqlState) {
        switch (sqlState) {
            case "P0001":
                return "Unable to delete this measurement point because there is an alarm in progress.";
            case "P0002":
                return "Referenced to a rule.";
            case "P0003":
                return "Referenced to a graph.";
            case "P0004":
                return "Alarm in progress for this rule.";
            default:
                return "Unknown SQL error.";
        }
    }
}

遇到的问题 #

• Postman测试DELETE请求时，返回400 Bad Request及正确提示信息，符合预期
• Angular前端调用同一接口时，收到500 Internal Server Error，错误信息："Error while committing the transaction; nested exception is javax.persistence.RollbackException: Error while committing the transaction"

日志信息 #

[2025-04-24 15:09:02.693] - 22900 WARN [http-nio-127.0.0.1-8090-exec-2] --- org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: P0003
[2025-04-24 15:09:02.694] - 22900 ERROR [http-nio-127.0.0.1-8090-exec-2] --- org.hibernate.engine.jdbc.spi.SqlExceptionHelper: ERRORE: P0003
  Dove: funzione PL/pgSQL f_bd_reset_mepo_references() riga 22 a RAISE
[2025-04-24 15:09:02.698] - 22900 INFO [http-nio-127.0.0.1-8090-exec-2] --- org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl: HHH000010: On release of batch it still contained JDBC statements
[http-nio-127.0.0.1-8090-exec-2] WARN org.springframework.web.servlet.mvc.method.annotation.ExceptionHandlerExceptionResolver - Resolved [org.springframework.orm.jpa.JpaSystemException: Error while committing the transaction; nested exception is javax.persistence.RollbackException: Error while committing the transaction]

问题出在事务提交阶段抛出的RollbackException被Spring封装为JpaSystemException，原有全局异常处理器的逻辑无法穿透这些封装异常找到底层的SQLException，导致返回500错误。以下是修复步骤：

1. 升级异常查找逻辑 #

更新findSQLException方法，确保能穿透JpaSystemException和RollbackException找到底层的SQLException：

private Throwable findSQLException(Throwable ex) {
    while (ex != null) {
        if (ex instanceof SQLException) {
            return ex;
        }
        // 处理JPA封装的系统异常和事务回滚异常
        if (ex instanceof JpaSystemException || ex instanceof RollbackException) {
            ex = ex.getCause();
            continue;
        }
        ex = ex.getCause();
    }
    return null;
}

2. 新增针对性异常处理方法 #

在全局异常处理器中单独添加JpaSystemException的处理逻辑，提升异常捕获的精准度：

@ControllerAdvice
public class GlobalExceptionHandler {

    // 优先处理JPA系统异常
    @ExceptionHandler(JpaSystemException.class)
    public ResponseEntity<?> handleJpaSystemException(JpaSystemException ex) {
        Throwable root = findSQLException(ex);
        if (root instanceof SQLException) {
            String sqlState = ((SQLException) root).getSQLState();
            String customMessage = mapErrorMessage(sqlState);
            return new ResponseEntity<>(customMessage, HttpStatus.BAD_REQUEST);
        }
        return new ResponseEntity<>("Internal Server Error: " + ex.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
    }

    // 处理其他通用异常
    @ExceptionHandler(Exception.class)
    public ResponseEntity<?> handleAnyException(Exception ex, WebRequest request) {
        Throwable root = findSQLException(ex);
        if (root instanceof SQLException) {
            String sqlState = ((SQLException) root).getSQLState();
            String customMessage = mapErrorMessage(sqlState);
            return new ResponseEntity<>(customMessage, HttpStatus.BAD_REQUEST);
        }

        return new ResponseEntity<>("Internal Server Error: " + ex.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
    }

    // 升级后的异常查找方法
    private Throwable findSQLException(Throwable ex) {
        while (ex != null) {
            if (ex instanceof SQLException) {
                return ex;
            }
            if (ex instanceof JpaSystemException || ex instanceof RollbackException) {
                ex = ex.getCause();
                continue;
            }
            ex = ex.getCause();
        }
        return null;
    }

    // 保持原有的错误信息映射方法不变
    private String mapErrorMessage(String sqlState) {
        switch (sqlState) {
            case "P0001":
                return "Unable to delete this measurement point because there is an alarm in progress.";
            case "P0002":
                return "Referenced to a rule.";
            case "P0003":
                return "Referenced to a graph.";
            case "P0004":
                return "Alarm in progress for this rule.";
            default:
                return "Unknown SQL error.";
        }
    }
}

3. 优化事务配置 #

将事务的rollbackFor范围扩大到所有异常，确保任何错误都能触发回滚并被正确捕获：

@Transactional(rollbackFor = Exception.class)
public void deleteById(Integer id) {
    repository.deleteById(id);
}

原因说明 #

Postman测试时，事务可能在方法执行阶段就抛出异常并被直接处理；而Angular请求的上下文导致Spring在事务提交阶段才抛出异常，异常被封装为JpaSystemException。修改后的逻辑能正确解析嵌套异常，提取SQLState并返回对应的400状态码和提示信息。

内容的提问来源于stack exchange，提问作者Salvatore Montagna