英文:
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 <> 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();
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<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
.......
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("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
答案1
得分: 2
使用 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);
}
英文:
Using ExceptionUtils with getRootCause(throwable) gives the root cause message.
@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);
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论