Spring Boot:处理来自数据库触发器的抛出异常

huangapple go评论82阅读模式
英文:

Spring Boot: Handle Raised Exception from Database Trigger

问题

Function

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;$$;

Trigger:

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

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);
}

Console:

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

Question is

Currently, ex.getMessage() returns org.hibernate.exception.GenericJDBCException: could not execute statement.

  • How can I fetch the message described in the trigger (i.e PSQLException: Not allowed to update
    the value of created_by
    )
  • If I remove the JpaSystemException, handleTriggerException does not work anymore, why?

Environment:

Framework: Spring Boot
ORM: Hibernate
Database: Postgres 11

UPDATE:

I have tried to get a message with the following methods but unfortunately, they all return the same message.

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
英文:

I have a requirement to prevent the update action for ceratin columns in the table and display the message. I am using the liquibase to manage the database schema. To achieve this I used the Trigger Functions and Triggers which works fine.

Function

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

Trigger:

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

I am managing the exception handling using @ControllerAdvice and @ExceptionHandler with PSQLException.class, GenericJDBCException.class, JpaSystemException.class and I was able to handle the exception. To verify the functionality, when I make an API hit to update the values of restricted columns, trigger raised the exception and I can see the following in the console.

handleTriggerException

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

Console:

Hibernate: update category set created_by = &#39;abc&#39; 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

Question is

Currently, ex.getMessage() returns org.hibernate.exception.GenericJDBCException: could not execute statement.

  • How can I fetch the message described in the trigger (i.e PSQLException: Not allowed to update
    the value of created_by
    )
  • If I remove the JpaSystemException, handleTriggerException does not work anymore, why?

Environment:

Framework: Spring Boot <br>
ORM: Hibernate <br>
Database: Postgres 11<br>


UPDATE:

I have tried to get a message with the following methods but unfortunately, they all return the same message.

System.out.println(&quot;1: &quot; +ex.getCause());
System.out.println(&quot;2: &quot; +ex.getMessage());
System.out.println(&quot;3: &quot; +ex.getLocalizedMessage());
System.out.println(&quot;4: &quot; +ex.fillInStackTrace());
System.out.println(&quot;5: &quot; +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

答案1

得分: 2

使用 ExceptionUtilsgetRootCause(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);
}

如何获取根异常消息?

英文:

Using ExceptionUtils with getRootCause(throwable) gives the root cause message.

@ExceptionHandler({PSQLException.class, GenericJDBCException.class, JpaSystemException.class})
public ResponseEntity&lt;Problem&gt; 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?

huangapple
  • 本文由 发表于 2020年5月5日 12:27:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/61605778.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定