Identify constraint name that trigger DataIntegrityViolationException

Question

I am stuck with a issue of identify which constraint triggers DataIntegrityViolationException. I have two unique constraints: username and email but I have no luck trying to figure it out.

I have tried to get the root cause exception but I got this message

Unique index or primary key violation: "UK_6DOTKOTT2KJSP8VW4D0M25FB7_INDEX_4 ON PUBLIC.USERS(EMAIL) VALUES ('copeland@yahoo.com', 21)"; SQL statement: insert into users (id, created_at, updated_at, country, email, last_name, name, password, phone, sex, username) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-193]

Reading the error I know email constraint triggers the validation but I want to return to the user something like: {type: ERROR, message: "The email already exist"}

I have read in other post and people handle it looking for a constraint name into the exception(eg, users_unique_username_idx) and display a proper message to the user. But I couldn't get that type of constraint name

Maybe I am missing a configuration. I am using:

Spring Boot 1.5.1.RELEASE, JPA, Hibernate and H2

My application.properties

spring.jpa.generate-ddl=true

User.class:

@Entity(name = "users")
public class User extends BaseEntity {
    private static final Logger LOGGER = LoggerFactory.getLogger(User.class);

    public enum Sex { MALE, FEMALE }

    @Id
    @GeneratedValue
    private Long id;

    @Column(name = "name", length = 100)
    @NotNull(message = "error.name.notnull")
    private String name;

    @Column(name = "lastName", length = 100)
    @NotNull(message = "error.lastName.notnull")
    private String lastName;

    @Column(name = "email", unique = true, length = 100)
    @NotNull(message = "error.email.notnull")
    private String email;

    @Column(name = "username", unique = true, length = 100)
    @NotNull(message = "error.username.notnull")
    private String username;

    @Column(name = "password", length = 100)
    @NotNull(message = "error.password.notnull")
    @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
    private String password;

    @Enumerated(EnumType.STRING)
    private Sex sex;

    @Column(name = "phone", length = 50)
    private String phone;

    @Column(name = "country", length = 100)
    @NotNull(message = "error.country.notnull")
    private String country;

    public User() {}

    // Getters and setters

}

ControllerValidationHandler.class

@ControllerAdvice
public class ControllerValidationHandler {
    private final Logger LOGGER = LoggerFactory.getLogger(ControllerValidationHandler.class);

    @Autowired
    private MessageSource msgSource;

    private static Map<String, String> constraintCodeMap = new HashMap<String, String>() {
        {
            put("users_unique_username_idx", "exception.users.duplicate_username");
            put("users_unique_email_idx", "exception.users.duplicate_email");
        }
    };

    // This solution I see in another stackoverflow answer but not work
    // for me. This is the closest solution to solve my problem that I found
    @ResponseStatus(value = HttpStatus.CONFLICT)  // 409
    @ExceptionHandler(DataIntegrityViolationException.class)
    @ResponseBody
    public ErrorInfo conflict(HttpServletRequest req, DataIntegrityViolationException e) {
        String rootMsg = ValidationUtil.getRootCause(e).getMessage();
        LOGGER.info("rootMessage" + rootMsg);
        if (rootMsg != null) {
            Optional<Map.Entry<String, String>> entry = constraintCodeMap.entrySet().stream()
                    .filter((it) -> rootMsg.contains(it.getKey()))
                    .findAny();
            LOGGER.info("Has entries: " + entry.isPresent()); // false
            if (entry.isPresent()) {
                LOGGER.info("Value: " + entry.get().getValue());
                e=new DataIntegrityViolationException(
                        msgSource.getMessage(entry.get().getValue(), null, LocaleContextHolder.getLocale()));
            }
        }
        return new ErrorInfo(req, e);
    }

The response at this moment is:

{"timestamp":1488063801557,"status":500,"error":"Internal Server Error","exception":"org.springframework.dao.DataIntegrityViolationException","message":"could not execute statement; SQL [n/a]; constraint [\"UK_6DOTKOTT2KJSP8VW4D0M25FB7_INDEX_4 ON PUBLIC.USERS(EMAIL) VALUES ('copeland@yahoo.com', 21)\"; SQL statement:\ninsert into users (id, created_at, updated_at, country, email, last_name, name, password, phone, sex, username) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-193]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement","path":"/users"}

UPDATE

This is my service layer that handle my persistence operations

MysqlService.class

@Service
@Qualifier("mysql")
class MysqlUserService implements UserService {
    private UserRepository userRepository;

    @Autowired
    public MysqlUserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @Override
    public List<User> findAll() {
        return userRepository.findAll();
    }

    @Override
    public Page<User> findAll(Pageable pageable) {
        return userRepository.findAll(pageable);
    }

    @Override
    public User findOne(Long id) {
        return userRepository.findOne(id);
    }

    @Override
    public User store(User user) {
        return userRepository.save(user);
    }

    @Override
    public User update(User usr) {
        User user = this.validateUser(usr);

        return userRepository.save(user);
    }

    @Override
    public void destroy(Long id) {
        this.validateUser(id);

        userRepository.delete(id);
    }

    private User validateUser(User usr) {
        return validateUser(usr.getId());
    }

    /**
     * Validate that an user exists
     *
     * @param id of the user
     * @return an existing User
     */
    private User validateUser(Long id) {
        User user = userRepository.findOne(id);
        if (user == null) {
            throw new UserNotFoundException();
        }
        return user;
    }
}

Update #2

Repo to reproduce the issue https://github.com/LTroya/boot-users. I commented my handler on ValidationExceptionHandler.class in order to see the exception.

Send twice json at Json to test on Readme.md to POST /users/

Answer 1

What you want to do is rather than specify the unique column requirement on the @Column annotation, you can actual define those with names on the @Table annotation that JPA provides to have further control of those constraints.

@Entity
@Table(uniqueConstraints = {
  @UniqueConstraint(name = "UC_email", columnNames = { "email" } ),
  @UniqueConstraint(name = "UC_username", columnNames = " { "userName" } )
})

There are now two ways for handling the exception:

In the controller

You could elect to place the parsing logic in your controller and simply catch the DataIntegrityException that spring throws and parse it there. Something like the following pseudo code:

public ResponseBody myFancyControllerMethod(...) {
  try {
    final User user = userService.myFactoryServiceMethod(...);
  }
  catch ( DataIntegrityException e ) {
    // handle exception parsing & setting the appropriate error here
  }
}

The ultimate crux with this approach for me is we've moved code to handle persistence problems up two layers rather than the layer immediately above the persistence tier. This means should we have multiple controllers which need to handle this scenario we either find ourselves doing one of the following

• Introduce some abstract base controller to place the logic.
• Introduce some helper class with static methods we call for reuse.
• Cut-n-paste the code - Yes this happens more than we think.

Placing the code in the presentation tier also introduces concerns when you need to share that service with other consumer types that may not be actually returning some type of html view.

This is why I recommend pushing the logic down 1 more level.

In the service

This is a cleaner approach because we push the validation of the constraint handling to the layer above the persistence layer, which is meant to ultimately be where we handle persistence failures. Not only that, our code actually documents the failure conditions and we can elect either to ignore or handle them based on context.

The caveat here is that I'd recommend you create specific exception classes that you throw from your service tier code in order to identify the unique constraint failures and throw those after you have parsed the ConstraintViolationException from Hibernate.

In your web controller, rest controller, or whatever other consumer that is calling into your service, you simply need to catch the appropriate exception class if necessary and branch accordingly. Here's some service pseudo code:

public User myFancyServiceMethod(...) {
  try {
    // do your stuff here
    return userRepository.save( user );
  }
  catch( ConstraintViolationException e ) { 
    if ( isExceptionUniqueConstraintFor( "UC_email" ) ) {
      throw new EmailAddressAlreadyExistsException();
    }
    else if ( isExceptionUniqueConstraintFor( "UC_username" ) ) {
      throw new UserNameAlreadyExistsException();
    }
  }
}

Answer 2

You can specify unique constraints separately but you'd need to that on the entity level like

@Entity(name = "users")
@Table(name = "users", uniqueConstraints = {
    @UniqueConstraint(name = "users_unique_username_idx", columnNames = "username"),
    @UniqueConstraint(name = "users_unique_email_idx", columnNames = "email")
})
public class User extends BaseEntity { ... }