Hibernate双向ManyToMany映射DDL异常求助

嘿，我来帮你排查这个问题！从错误日志来看，核心问题是MySQL的外键约束创建失败（errno:150），咱们一步步来分析和修复。

问题回顾 #

你有User、Role、EmployeeRole和AccessPermission四个实体，在配置User与EmployeeRole的双向ManyToMany映射（User作为关联所有者）时，运行代码抛出了DDL执行异常。

相关实体代码 #

User实体

@Entity @Table(name="user")
public class User{
    @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "idgenerator")
    @SequenceGenerator(initialValue = 1, name = "idgenerator", sequenceName = "usersSeq")
    private long id;
    @Column(name="company_id") private String companyId;
    @Column(name = "password") private String password;
    @Column(name= "firstname") private String firstName;
    @Column(name = "lastname") private String lastName;
    @Column(name = "email") private String email;
    @Column(name = "active") private int active;
    @ManyToMany(cascade = CascadeType.PERSIST)
    @JoinTable(name = "user_role", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles;
    @ManyToMany(cascade = CascadeType.PERSIST)
    @JoinTable(name = "user_has_role", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "emp_role_id"))
    private Set<EmployeeRole> empRoles = new HashSet<>();
    // getters and setters.
}

EmployeeRole实体

@Entity @Table(name="employeeRole")
public class EmployeeRole extends AbstractData {
    @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "idgenerator")
    @SequenceGenerator(initialValue = 1, name = "idgenerator", sequenceName = "employeeRoleSeq")
    @Column(name = "id", updatable = false, nullable = false)
    private long id;
    @NotBlank @Size(min = 1, max = 255)
    @Column(name="roleName") protected String roleName;
    @Column(name="roleDescription") protected String roleDescription;
    @ManyToMany(mappedBy="empRoles")
    private Set<User> user = new HashSet<>();
    // getters and setters.
}

错误日志 #

alter table user_has_role add constraint FKdtkvc2iy3ph1rkvd67yl2t13m foreign key (user_id) references user (id)
2019-07-19 14:05:38.758 WARN 11756 --- [ main] o.h.t.s.i.ExceptionHandlerLoggedImpl : GenerationTarget encountered exception accepting command : Error executing DDL " alter table user_has_role add constraint FKdtkvc2iy3ph1rkvd67yl2t13m foreign key (user_id) references user (id)" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL " alter table user_has_role add constraint FKdtkvc2iy3ph1rkvd67yl2t13m foreign key (user_id) references user (id)" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    ...
Caused by: java.sql.SQLException: Can't create table `possystem`.`#sql-4a0_17c` (errno: 150 "Foreign key constraint is incorrectly formed")
    ...

问题原因分析 #

这个错误的核心原因是你把User实体的表名设为了user，而user是MySQL的保留关键字。当Hibernate尝试生成外键约束引用user表时，MySQL会把它当成关键字解析，导致SQL语法错误，最终外键约束创建失败。

解决方案 #

1. 修改User实体的表名，规避保留关键字 #

把User实体上的@Table(name="user")改成非保留字的名称，比如users或者app_user，这样就能避免MySQL的语法解析冲突。

修改后的User实体开头代码：

@Entity @Table(name="users") // 替换原有的"user"表名
public class User{
    @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "idgenerator")
    @SequenceGenerator(initialValue = 1, name = "idgenerator", sequenceName = "usersSeq")
    private long id;
    // 其余字段和关联关系保持不变
}

2. 清理并重新生成表结构（可选，针对已有错误表的情况） #

如果你的数据库中已经生成了错误的表结构，建议先删除user、user_has_role这些相关表，或者在Hibernate配置中设置hibernate.hbm2ddl.auto=create（仅测试环境使用），让Hibernate重新生成正确的表和外键约束。

3. 优化EmployeeRole的集合命名（代码可读性提升） #

EmployeeRole中的集合字段user语义上是多个用户，建议改成users，让代码更易读：

@ManyToMany(mappedBy="empRoles")
private Set<User> users = new HashSet<>(); // 把user改为users

验证修复 #

完成以上修改后，重新运行你的应用，Hibernate应该能顺利生成所有表和外键约束，不会再出现errno:150的异常了。

内容的提问来源于stack exchange，提问作者Habibur Rahman