mysqlspring-bootexceptionspring-data-jpacontroller-advice

Custom - ConstraintViolationException - SQL Exception


I have a custom advice to handle exceptions from controllers copied from REST API Error Handling and a method to handle DataIntegrityViolationException:

@ExceptionHandler(DataIntegrityViolationException.class)
    protected ResponseEntity<Object> handleDataIntegrityViolation(DataIntegrityViolationException ex,
        WebRequest request) {
    if (ex.getCause() instanceof ConstraintViolationException) {
        return buildResponseEntity(new ApiError(HttpStatus.CONFLICT, "Database error", ex));
    }
    return buildResponseEntity(new ApiError(HttpStatus.INTERNAL_SERVER_ERROR, ex));
}

ApiError - Constructor

public ApiError(HttpStatus status, String message, Throwable ex) {
        this();
        this.status = status;
        this.message = message;
        this.debugMessage = ex.getLocalizedMessage();
    }

When an error happens the response shows a message like this one:

  "apierror": {
    "status": "CONFLICT",
    "message": "Database error",
    "debugMessage": "could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement",
    "constraintName": null
  }

The problem is that the message doesn't show a real problem to the consumer, because the real problem is inside (ex.getCause().getCause().getMessage()):

Cannot delete or update a parent row: a foreign key constraint fails 
(`cup_orchestrator`.`cpo_production_cycle`, CONSTRAINT `fk_cpo_production_cycle_cpo_cycle_type1` 
FOREIGN KEY (`cycle_type_id`) REFERENCES `cpo_cycle_type` (`cycle_type_id`))

I'd like to handle to put a message like: "Record still have reference from other table";

Is there any custom handler exception to handle sql exceptions in a more specific way?


Solution

  • I've changed the DataIntegrityViolationException handle to be more specific:

    @ExceptionHandler(DataIntegrityViolationException.class)
    protected ResponseEntity<Object> handleDataIntegrityViolation(DataIntegrityViolationException ex,
                                                                      WebRequest request) {
            Throwable cause = ex.getRootCause();
            if (cause instanceof SQLIntegrityConstraintViolationException) {
                SQLIntegrityConstraintViolationException consEx = (SQLIntegrityConstraintViolationException) cause;
                String message = "";
                String constraint = "";
                HttpStatus httpStatus = null;
                if (consEx.getMessage().contains("UNIQUE")) {
                    message = "Cannot enter the same record twice";
                    constraint = "DUPLICATED_RECORD";
                    httpStatus = HttpStatus.CONFLICT;
                } else if (consEx.getMessage().contains("foreign key constraint")) {
                    message = "Record still have reference from other table";
                    constraint = "USED_RECORD";
                    httpStatus = HttpStatus.UNPROCESSABLE_ENTITY;
                }
                return buildResponseEntity(new ApiError(httpStatus, message, consEx.getMessage(), constraint));
            } else if (ex.getCause() instanceof ConstraintViolationException) {
                return buildResponseEntity(new ApiError(HttpStatus.CONFLICT, "Database error", ex));
            }
            return buildResponseEntity(new ApiError(HttpStatus.INTERNAL_SERVER_ERROR, ex));
        }
    

    And I've created a constraint attribute on ApiError class and a constructor to receive:

    public ApiError(HttpStatus status, String message, String debugMessage, String constraint) {
            this.status = status;
            this.message = message;
            this.debugMessage = debugMessage;
            this.constraint = constraint;
        }
    

    On react application will be created a bundle to this constraints:
    DUPLICATED_RECORD
    USED_RECORD