Spring MVC – Best Practices for Handling Duplicate Entries in Database

designmvcspring

I am currently working a School Management project using Spring stack, Kotlin as programming language and Neo4j for storage solution.

Here is the scenario, I am creating a Class entry in the DB. Lets assume the properties of the Class entity object are Name: Nine, Section: A, Shift: Morning. When I am going to save in DB I have to make sure that there is no such Class that has same properties.

My question is, after duplication check how I suppose to propagate the massage to the other layer or to the client side ? Throw an Exception in the layer where it found the duplication or any other normal flow ?

Here is what I am currently handling this scenario. Providing Kotlin code.

Controller 

open fun post(@RequestBody cls: Course, request: HttpServletRequest): ResponseEntity<*> {        

    try {
        createdClass = classService.save(cls)
    }
    catch (ex: DuplicateCourseException) {            
        return responseConflict(cls)
    }catch (ex: DuplicateKeyException){
        return responseConflict(cls)
    }
    return responseOK(createdClass)
}

Service Layer

open fun save(course: Course): Course {
// Checking the DB 
    val foundDuplicate = classRepo.findListByOrganizationName(course.organization?.name)
            .filter {
                it.name == course.name
                it.section == course.section
                it.shift == course.shift
            }
            .isEmpty()
            .not()
    if (foundDuplicate) {
        // Custom Exception
        throw DuplicateCourseException("Duplicate Class Found")
    }
    return classRepo.save(course)
}

Or just checking from the Controller, like following code

open fun post(@RequestBody cls: Course, request: HttpServletRequest): ResponseEntity<*> {        

    if (classService.checkClassAlreadyExist(cls)) {
         return responseConflict(cls)
     }
   createdClass = classService.save(createdClass)         
   return responseOK(createdClass)
}

Or I could use some DTO to communicate between service layer and controller. And that DTO have some properties, that can tell the status of duplicate entry found. I am also aware that ControllerAdvice can handle Exception more elegantly.

So I would need suggestion what will be the best way to handle duplicate entry in DB ?

=======UPDATE=======

In my case apply uniqueness constraint in DB may not be possible. For example A class properties may be Name: Nine, Section: A, Shift: Morning and another class property Name: Nine, Section: A, Shift: Day. In this case constraint is the combination of the entity's values. Same combination should not be exist in DB. So I might have to check uniqueness from application layer.

Best Answer

Working with Spring worthwhile to stick to its features and capabilities. Unless they were incompatible with your requirements, in which case the usage of Spring would be questionable.

1. Error Handling

You already have mentioned the approach proposed by Spring. @ControllerAdvice. You can implement a dedicated component for error handling or use the annotations directly in the controllers.

This will reduce significantly the duplicity of try/catch blocks in all the Controllers.

2. Throwing errors

It's a common question: Do I delegate the data integrity control only to the DB?

I'm usually against. To me, the DB constraints are the last line of defence, not the only. Usually, the constraints reflect business rules so I'm in favour of making these rules explicit and readable along the code.

Don't get me wrong, I'm not suggesting to remove DB constraints. They must be. If there were more systems accessing to the DB, they will prevent you from unexpected data integrity violations.

Whether you use Spring's Data Integrity Violation Exceptions, business exceptions or domain model exceptions, if you have implemented #1, the error handling makes these differences irrelevant.

The place where the exceptions should be thrown depends on you. Due to these sort of exceptions are related to the business, I would not throw them from the Controllers. They should be as agnostic to the business as possible.

I would suggest doing the validations and the error throw from the domain layer. The layer closer to the DAL. One important thing is to be consistent with the implementation. Wherever you decide to do the validation, stick to such strategy.

3. The validation

Instead of retrieving all the collection

classRepo.findListByOrganizationName(course.organization?.name) 
  .filter { it.name == course.name 
       it.section == course.section 
       it.shift == course.shift }
   .isEmpty() .not()

Why don't you simply do a count of the rows?

classRepo.countByOrganizationName(...)

or 

classRepo.existByOrganizationName(...)

4. The question

how I suppose to propagate the massage to the other layer or to the client side ?

Depends. If upper layers are just going to catch it for throwing its own exception, then just let it go up to the @ControllerAdvice. Feeding the exception stacktrace with more traces won't make your code better.

But, if the upper layers can deal with it and there's a Plan B for the failed execution, then catch it.

You will find that Spring DAO's exceptions are RuntimeException for a good reason ;-).

Related Topic