Spring :处理从数据库触发器引发的异常

Question

我需要防止表中ceratin列的update操作并显示消息。我正在使用清算库来管理数据库架构。为了实现这一点，我使用了触发器函数和触发器，它们工作得很好。

函数

CREATE OR REPLACE FUNCTION FN_BU_CATEGORY() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF NEW.created_by <> OLD.created_by THEN
      RAISE EXCEPTION 'Not allowed to update the value of created_by';
   END IF;
   RETURN NEW;
END;$$;

触发器：

CREATE TRIGGER TR_BU_CATEGORY
BEFORE UPDATE ON category FOR EACH ROW
EXECUTE PROCEDURE FN_BU_CATEGORY();

我使用@ControllerAdvice和@ExceptionHandler与PSQLException.class, GenericJDBCException.class, JpaSystemException.class一起管理异常处理，并且能够处理异常。要验证该功能，当我按下API更新受限列的值时，触发器会引发异常，我可以在控制台中看到以下内容。

handleTriggerException

@ExceptionHandler({PSQLException.class, GenericJDBCException.class, JpaSystemException.class})
public ResponseEntity<Problem> handleTriggerException(Exception ex, NativeWebRequest request) {
    Problem problem = Problem.builder()
        .withStatus(Status.BAD_REQUEST)
        .withDetail("Test Message " + ex.getMessage())
        .build();
    return create(ex, problem, request);
}

控制台：

Hibernate: update category set created_by = 'abc' where id = 1234
19:37:49.126 [XNIO-1 task-9] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: P0001
19:37:49.127 [XNIO-1 task-9] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ERROR: Not allowed to update 
the value of created_by
  Where: PL/pgSQL function noupdate() line 3 at RAISE
.......
org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is 
org.hibernate.exception.GenericJDBCException: could not execute statement
Caused by: org.postgresql.util.PSQLException: ERROR: Not allowed to update the value of created_by
Where: PL/pgSQL function noupdate() line 3 at RAISE

问题是

当前，ex.getMessage()返回org.hibernate.exception.GenericJDBCException:未能执行语句。

如何获取触发器中描述的消息(即:不允许更新created_by)

• If的值，我删除了JpaSystemException，handleTriggerException不再工作，为什么？

环境：

框架: Spring Boot

ORM: Hibernate

数据库: Postgres 11

更新：

我尝试用以下方法获得一条消息，但不幸的是，它们都返回相同的消息。

System.out.println("1: " +ex.getCause());
System.out.println("2: " +ex.getMessage());
System.out.println("3: " +ex.getLocalizedMessage());
System.out.println("4: " +ex.fillInStackTrace());
System.out.println("5: " +ex.getStackTrace());

1: org.hibernate.exception.GenericJDBCException: could not execute statement
2: could not execute statement; nested exception is         org.hibernate.exception.GenericJDBCException: could not execute statement
3: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement
4: org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement    
5: [Ljava.lang.StackTraceElement;@6f3072be

Answer 1

使用ExceptionUtils与getRootCause(throwable)一起提供根本原因消息。

@ExceptionHandler({PSQLException.class, GenericJDBCException.class, JpaSystemException.class})
public ResponseEntity<Problem> handleTriggerException(Exception ex, NativeWebRequest request) {

        Problem problem = Problem.builder()
            .withStatus(Status.BAD_REQUEST)
            .withDetail(ExceptionUtils.getRootCause(ex).getMessage())
            .build();
        return create(ex, problem, request);
}

How to get root cause message?