问题描述
我遇到了确定哪个约束触发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 ('[email protected]', 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 ('[email protected]', 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 ('[email protected]', 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的约束名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！