1
\$\begingroup\$

This is about handling sql - foreign key constraint failure exceptions (SQL Error: 1452, SQLState: 23000) globally using Spring's controller advices and Hibernate - ConstraintViolationException.class.

I'm working on a small scale REST API which has 28 database tables and contains 32 foreign keys.

To handle exceptions on entire project, in my approach has two @ControllerAdvice classes, one for handle Servlet exceptions and the other for handle Hibernate sql exceptions.

In my RootDataAccessExceptionsHandler class (sql exceptionsare being handled here), has following @ExceptionHandler method.

@ExceptionHandler(value = {ConstraintViolationException.class})
protected ResponseEntity<Object> handleConstraintViolationException(
        ConstraintViolationException ex,
        WebRequest request
) {
    String cause = ex.getCause().toString();
    LOGGER.error("[ APP Exception ] : {}", ex.getCause().toString());
        switch (ex.getErrorCode()) {
            // catch Cannot add or update a child row: 
            // a foreign key constraint fails exceptions
            case 1452: {
                return handleExceptionInternal(
                        ex,
                        getSqlForeignKeyViolationExceptionsPreProcessor().generate1452SqlExceptionResponse(ex.getSQLException()),
                        new HttpHeaders(),
                        HttpStatus.NOT_FOUND,
                        request
                );
            }
            // catch other sql violations - to be developed
            default: {
                return handleExceptionInternal(
                        ex,
                        createDefaultExceptionErrorResponse(),
                        new HttpHeaders(),
                        HttpStatus.NOT_FOUND,
                        request
                );
            }
        }

    }

}

And generate1452SqlExceptionResp(SQLException) is responsible for generate appropriate error response as follows,

Simply this method scans for the key which is violated using getMessage() of SQLException class and then generate error response.

public ResponseEntity<?> generate1452SqlExceptionResponse(SQLException sqle) {
    LOGGER.error("[ APP Exception SQL ] : {}", sqle.getErrorCode());
    LOGGER.error("[ APP Exception SQL ] : {}", sqle.getSQLState());

    if (sqle.getMessage().contains("FK_ID_001_USER_ID")
            || sqle.getMessage().contains("FK_ID_006_USER_ID")) {
        return ResponseEntity
                .status(HttpStatus.NOT_FOUND)
                .body(
                        ErrorServiceMessage
                        .getBuilder()
                        .putCode("ERROR")
                        .putType("Unidentified user")
                        .putPayload("Given user cannot be found")
                        .build()
                );
    }
}

In here, FK_ID_001_USER_ID and FK_ID_006_USER_ID are two foreign keys, but like this, this method has bunch of conditional checks defined in order to process all 32 key violations.

Example

When an operation tries to do something with USER_ID - 10 (Now there's no user associated with this ID), an error will be generated and returned as JSON object asfollows,

{
    "headers": {},
    "body": {
        "code": "ERROR",
        "type": "Unidentified user",
        "payload": "Given user cannot be found"
    },
    "statusCode": "NOT_FOUND",
    "statusCodeValue": 404
}
  • Please review this approach and what are the drawbacks can be found on this.
  • Is there any other better approach ?
  • How can i improve this ?
\$\endgroup\$
2
\$\begingroup\$

Your approach seems well written and structured code-wise. However I feel that there is room to improve the structure of the application and the separation of concerns among the classes thus producing a more SOLID result.

My biggest concern is with the web-layer (The Controllers)having to deal with SQL related errors. You do not really describe how your application is structured, but I believe you have your logic implemented within the Controller methods.

If you enclose the application logic in a service class (e.g. UserService) there you could perform data validation and exception handling on the incoming request. For example you should check that the user exists before trying to run update queries that result in an Exception being thrown. You could then throw a custom ResourceNotFoundException with the custom message your ErrorServiceMessage generates and have your ControllerAdvice ExceptionHandler just wrap this to a ResponseEntity.

By hard coding your index names into your error handling classes. then you make it more error prone. What will happen if someone renames the Index? Additionally every time a new Index is being added you would then have to update the generate1452SqlExceptionResp method.

I hope you find my comments useful and thought provoking.

Best Regards

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ hello sir, thank you for your response and i'm gonna work through your recommendations, really appreciate them. \$\endgroup\$ – benjamin c Jul 10 '17 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.