Custom - ConstraintViolationException - SQL Exception

Question

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?

Aldo Inácio da Silva · Accepted Answer · 2021-10-05T19:18:12.263

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

score 0 · Answer 2 · answered Feb 01 '23 at 18:36

Uou should use SQLIntegrityConstraintViolationException.class instead of DataIntegrityViolationException.class

@ExceptionHandler(SQLIntegrityConstraintViolationException.class)
public ResponseEntity<Object> handleSQLIntegrityConstraintViolationException(SQLIntegrityConstraintViolationException exception, WebRequest request) {
    ResponseDTO<?> responseBody = ResponseDTO.builder().message(exception.getMessage()).code(HttpStatus.BAD_REQUEST.value()).build();
    return new ResponseEntity<Object>(responseBody, HttpStatus.BAD_REQUEST);
}

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ResponseDTO<T> {
    @Builder.Default
    private int code = HttpStatus.OK.value();

    @Builder.Default
    private LocalDateTime timestamp = LocalDateTime.now();

    @JsonInclude(JsonInclude.Include.NON_NULL)
    private String message;

    @JsonInclude(JsonInclude.Include.NON_NULL)
    private Long totalElements;

    @JsonInclude(JsonInclude.Include.NON_NULL)
    private Integer numberOfElements;

    @JsonInclude(JsonInclude.Include.NON_NULL)
    private Integer totalPages;

    @JsonInclude(JsonInclude.Include.NON_NULL)
    private T data;
}

Custom - ConstraintViolationException - SQL Exception

2 Answers2