Handling specific DataIntegrityViolationException in a transaction

Question

I have a very basic create user controller.

    @PostMapping(consumes = MediaType.APPLICATION_JSON_VALUE, produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<String> createUser(@RequestBody UserInput userInput) {
        userControllerService.createUser(userInput);
        return ResponseEntity.ok("success");
    }

The UserControllerService#createUser method is a transaction containing multiple SimpleJpaRepository#save calls. E.g.

    @Transactional
    @Override
    public void createUser(UserInput userInput) {
        userRepository.save(userInput);
        profileRepository.save(userInput.getProfile());
    }

I would like to be able to have the db handle unique constraint violations and be able to inform the client about a specific violation.

For example if I want to inform the client if and only if I get specific constraint violations.

    @PostMapping(consumes = MediaType.APPLICATION_JSON_VALUE, produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<String> createUser(@RequestBody UserInput userInput) {
        try {
            userControllerService.createUser(userInput);
        } catch (DuplicateUserNameException e) {
            return new ResponseEntity<>("", HttpStatus.BAD_REQUEST);
        } catch (DuplicateEmailException e) {
            return new ResponseEntity<>("", HttpStatus.BAD_REQUEST);
        } catch (Exception e) {
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }
        return ResponseEntity.ok("success");
    }

However any constraint violation throws a DataIntegrityViolationException at the end of UserControllerService#createUser. And DataIntegrityViolationException is too brittle to rely on. It only has the cause SQLState: 23505 (unique constraint violation) and an unstructured message, such as:

ERROR: duplicate key value violates unique constraint "unique_user" Detail: Key (email)=([email protected]) already exists.

Even if I add custom exception handling, it will never be run since the DataIntegrityViolationException isn't encounter until the end of the method when the db is actually called for the first time. E.g. this has no effect.

    @Transactional
    @Override
    public void createUser(UserInput userInput) {
        try
          userRepository.save(userInput);
        } catch (Exception e) {
          throw new DuplicateUserNameException();
        }
        try {
          profileRepository.save(userInput.getProfile());
        } catch (Exception e) {
          throw new DuplicateEmailException();
        }
    }

Am I going about this the wrong way? It seems like this is very basic functionality that should be possible somehow.

The best way I can think of is adding some code to parse the message from DataIntegrityViolationException, but this has its limitations, for example, for two inserts into the same table have a different meaning for the application. One insert might be directly from the user and the second might be something the application generates. It may not be possible to distinguish the two from the end of the transaction by just parsing the detailed message.

Are there other implementations I should consider instead?

Answer 1

If I understand correctly , it sounds like you want to have a reliable way to determine when DataIntegrityViolationException is thrown , what is the exact reason that causes it such as whether it is due to the duplicated email or duplicated username for a particular use case or anything else.

The simplest way is not to rely on the thrown exception to determine but actively issue some SQL to validate it before the data is saved to DB such as :

@Transactional
@Override
public void createUser(UserInput userInput) {

    if(userRepository.existUsername(userInput.getUsername()){
       throw new DuplicateUserNameException();
    }

    if(userRepository.existEmail(userInput.getEmail())){
      throw new DuplicateEmailException();
    }

    userRepository.save(userInput);
    profileRepository.save(userInput.getProfile());
}