java - 外键约束失败 - hibernate

Question

当我尝试向表 USERS 添加数据时遇到问题。首先是我的数据库和类(class)。

数据库结构:

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 FOREIGN KEY (lecturer_id code>) 引用讲师 (LECTURER_ID))

但我不知道为什么，因为我正在尝试将数据添加到 USER 和 ROLESMAP 表中。我认为当您向表 ROLESMAP/USER 添加数据时，ROLESMAP 表中的字段讲师_id 和管理_id 应该为 NULL。谁能告诉我我做错了什么？

Answer 1

这是因为 lecturer_id=0 是NOT 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(hibernate)方式。我强烈建议您阅读一些有关映射关系(OneToOne、OneToMany、ManyToOne、ManyToMany)的内容，例如 Hibernate 引用 Chapter 7. Collection mapping和 8. Association Mappings