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 ?
