确定触发DataIntegrityViolationException的约束名称 [英] Identify constraint name that trigger DataIntegrityViolationException

问题描述

我遇到了确定哪个约束触发DataIntegrityViolationException的问题.我有两个独特的限制条件:用户名和电子邮件，但我想尝试一下没有运气.

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 ('[e-mail]', 21)"; SQL statement: insert into users (id, created_at, updated_at, country, email, last_name, name, password, phone, sex, username) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-193]

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

读取错误，我知道电子邮件约束会触发验证，但是我想返回给用户类似的内容: {type: ERROR, message: "The email already exist"}

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"}

我读过其他文章，人们进行处理以在异常中寻找约束名称(例如，users_unique_username_idx)，并向用户显示正确的消息.但是我无法获得这种类型的约束名称

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

我的 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);
    }

此刻的响应是:

{"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 ('[e-mail]', 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"}

更新

这是我处理持久性操作的服务层

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;
    }
}

更新#2

回购以重现此问题 https://github.com/LTroya/boot-users.我在ValidationExceptionHandler.class上评论了我的处理程序，以查看异常.

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

在 Json上发送两次json，以在Readme.md上测试到POST /users/

推荐答案

您可以单独指定唯一约束，但是您需要像

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 { ... }

这篇关于确定触发DataIntegrityViolationException的约束名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！