java - Spring 启动: Handle Raised Exception from Database Trigger

Question

我需要阻止表中某些列的更新操作并显示消息。我正在使用 liquibase 来管理数据库架构。为了实现这一点，我使用了触发函数和触发器，效果很好。

函数

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:无法执行语句。

• 如何获取触发器中描述的消息(即PSQLException:不允许更新create_by 的值)
• 如果我删除 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?