我尝试将数据添加到表USERS时遇到问题。首先是我的数据库和类。
数据库结构：
CREATE TABLE IF NOT EXISTS `admins` (
`ADMIN_ID` int(10) unsigned NOT NULL AUTO_INCREMENT ,
`USERNAME` varchar(45) NOT NULL,
`PASSWORD` varchar(45) NOT NULL,
`AUTHORITY` varchar(45) NOT NULL,
PRIMARY KEY (`ADMIN_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `lecturers` (
`LECTURER_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar(45) NOT NULL,
`SURNAME` varchar(45) NOT NULL,
`TITLES` varchar(45) NOT NULL,
`USERNAME` varchar(45) NOT NULL,
`PASSWORD` varchar(45) NOT NULL,
`AUTHORITY` varchar(45) NOT NULL,
`LEC_DESCRIPTION` varchar(100) NOT NULL,
PRIMARY KEY (`LECTURER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=40;
CREATE TABLE IF NOT EXISTS `roles_name` (
`role_id` int(1) NOT NULL,
`authority` varchar(45) NOT NULL,
UNIQUE KEY `role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `roles_name` (`role_id`, `authority`) VALUES
(1, 'ROLE_ADMIN'),
(2, 'ROLE_USER'),
(3, 'ROLE_LECTURER');
CREATE TABLE IF NOT EXISTS `users` (
`USER_ID` int(10) unsigned NOT NULL AUTO_INCREMENT ,
`NAME` varchar(45) NOT NULL,
`SURNAME` varchar(45) NOT NULL,
`username` varchar(45) NOT NULL,
`PASSWORD` varchar(45) NOT NULL,
`USER_DESCRIPTION` varchar(100) NOT NULL,
`AUTHORITY` varchar(45) NOT NULL,
`ENABLED` tinyint(1) NOT NULL,
PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=50000;
CREATE TABLE IF NOT EXISTS `roles_map` (
`rm_id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned,
`admin_id` int(10) unsigned,
`lecturer_id` int(10) unsigned,
`username` varchar(45) NOT NULL,
`password` varchar(45) NOT NULL,
`role_id` int(1) NOT NULL,
PRIMARY KEY (`rm_id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
FOREIGN KEY (`lecturer_id`) REFERENCES `lecturers` (`lecturer_id`),
FOREIGN KEY (`admin_id`) REFERENCES `admins` (`admin_id`),
FOREIGN KEY (`role_id`) REFERENCES `roles_name` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
RolesMap.java
@Entity
@Table(name = "roles_map")
public class RolesMap {
@Id
@Column(name = "RM_ID", unique = true, nullable = false)
private int rm_id;
//@Column(name = "USER_ID", unique = true)
//private int user_id;
@Column(name = "ADMIN_ID", unique = true)
private int admin_id;
@Column(name = "LECTURER_ID", unique = true)
private int lecturer_id;
@Column(name = "USERNAME", unique = true, nullable = false)
private String username;
@Column(name = "PASSWORD", unique = true, nullable = false)
private String password;
@Column(name = "ROLE_ID", unique = true, nullable = false)
private int role_id;
@ManyToOne(cascade={CascadeType.ALL})
@JoinColumn(name = "user_id")
private User user;
//getters and setters
}
User.java
@Entity
@Table(name = "users")
public class User {
@Id
@Column(name = "USER_ID", unique = true, nullable = false)
private int user_id;
@Column(name = "NAME", nullable = false)
private String name;
@Column(name = "SURNAME", unique = true, nullable = false)
private String surname;
@Column(name = "USERNAME", unique = true, nullable = false)
private String username; // zamiast username
@Column(name = "PASSWORD", unique = true, nullable = false)
private String password;
@Column(name = "USER_DESCRIPTION", nullable = false)
private String userDescription;
@Column(name = "AUTHORITY", nullable = false)
private String authority = "ROLE_USER";
@Column(name = "ENABLED", nullable = false)
private int enabled;
@OneToMany(mappedBy="user", cascade=CascadeType.ALL)
private List <RolesMap> rolesMap;
//getters and setters
}
加法
public String addUser() {
Session sess = null;
try {
sess = UserDao.getSessionFactory().openSession();
sess.beginTransaction();
RolesMap roles = new RolesMap();
//roles.setrUser(user);
User user = new User();
roles.setPassword(getPassword());
roles.setRole_id(2);
roles.setUsername(getUsername());
user.setName(getName());
user.setSurname(getSurname());
user.setUsername(getUsername());
user.setPassword(getPassword());
user.setUserDescription(getUserDescription());
user.setAuthority(getAuthority());
user.setEnabled(getEnabled());
user.setRolesMap(new ArrayList<RolesMap>());
user.getRolesMap().add(roles);
sess.save(user);
sess.getTransaction().commit();
//getUserService().addUser(user);
return SUCCESS;
} catch (DataAccessException e) {
e.printStackTrace();
}
return ERROR;
}
当我尝试使用此方法时，出现错误：
无法添加或更新子行：外键约束失败（
soeOne2。roles_map，CONSTRAINT roles_map_ibfk_2外国关键字（lecturer_id）参考lecturers（LECTURER_ID））但是我不知道为什么，因为我正在尝试将数据添加到USER和ROLESMAP表中。我认为将数据添加到表中时，ROLESMAP表中的ROLESMAP / USER字段讲者ID和admin_id应该为NULL。谁能告诉我我在做什么错？
最佳答案
这是因为lecturer_id=0不是lecturer_id=null。
您的应用程序尝试保存lecturer_id=0，并且数据库认为0是表lecturers中项目的ID。
因此，第一个工作环境是将int替换为Integer，以便您可以分配lecturer_id = null
@Column(name = "LECTURER_ID", unique = true)
private Integer lecturer_id;
而且我认为您需要对所有其他以相同方式工作的引用进行此修改。
第二点是，您在实体之间映射引用的方式不是jpa（休眠）方式。我强烈建议您阅读一些有关映射关系的信息（OneToOne，OneToMany，ManyToOne，ManyToMany），例如Hibernate Reference Chapter 7. Collection mapping和8. Association Mappings
关于java - 外键约束失败- hibernate ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20020881/