在Mysql上使用ddl模式生成时，不会生成ON DELETE CASCADE选项 [英] ON DELETE CASCADE option not in generated when using ddl schema generation on Mysql
问题描述
在运行于Tomcat Web应用程序的Maven-Spring-Hibernate-MySql中，我使用hibernate ddl来生成带有MySQL5InnoDBDialect的数据库模式。
生成模式除了用于外键的级联选项之外，这很好。例如，我有这样的结构：
保存用户详细信息对象的用户对象，它们共享相同的键：
@Entity
@Table（name =Users）
public class User实现Serializable {
private static final long serialVersionUID = -359364426541408141L;
/ * ---成员--- * /
/ **
*实体唯一生成的ID。
* /
@Id
@GeneratedValue（strategy = GenerationType.IDENTITY）
@Column（name =User_Id）
保护长ID;
@Getter
@OneToOne（cascade = CascadeType.ALL，fetch = FetchType.EAGER，mappedBy =user，optional = true）
protected UserDetails userDetails;
...
}
用户详细信息：
@Entity
@Table（name =UserDetails）
public class UserDetails实现Serializable {
private static final long serialVersionUID = 957231221603878419L;
$ b $ / * ---成员--- * /
/ **
*共享密钥
* /
@Id $
@GenericGenerator（name =User-Primary-Key，strategy =foreign，parameters = {@Parameter（name =property ，value =user）}）
@Column（name =User_Id）
保护长ID;
@Getter
@Setter
@OneToOne（可选= false，fetch = FetchType.LAZY）
@PrimaryKeyJoinColumn
私人用户用户;
...
}
当生成模式，从users-details表到用户表的外键缺少级联。
以下是用户详细信息的模式创建：
CREATE TABLE`userdetails`（
`User_Id` bigint（20）NOT NULL，
`Creation_Time` bigint 20）NOT NULL，
`EMail` varchar（128）DEFAULT NULL，
`Enabled` bit（1）NOT NULL，
`First_Name` varchar（15）DEFAULT NULL，
'Last_Name'varchar（25）DEFAULT NULL，
`Password` varchar（64）NOT NULL，
`User_Name` varchar（15）NOT NULL，
PRIMARY KEY（`User_Id`） ，
UNIQUE KEY`User_Name`（`User_Name`），
UNIQUE KEY`EMail`（`EMail`），
KEY`FKAE447BD7BF9006F5`（`User_Id`），
CONSTRAINT `FKAE447BD7BF9006F5` FOREIGN KEY（`User_Id`）REFERENCES`users`（`User_Id`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8 $$
正如你所看到的，没有任何ON DELETE CASCADE写在那里的FOREIGN KEY部分。
这个问题也被描述），但由于某种原因，它没有为OneToMany关系工作。为了解决这个问题，我使用了Spring的或者 。
我希望这可以帮助别人..
In a Maven-Spring-Hibernate-MySql running on Tomcat web app I'm using hibernate ddl to generate my DB schema with MySQL5InnoDBDialect.
The schema is generated just fine except the cascade option for foreign-keys. For example I have this structure:
A user object that holds user-details object, both sharing the same key:
@Entity
@Table(name = "Users")
public class User implements Serializable {
private static final long serialVersionUID = -359364426541408141L;
/*--- Members ---*/
/**
* The unique generated ID of the entity.
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "User_Id")
protected long id;
@Getter
@OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "user", optional = true)
protected UserDetails userDetails;
...
}
And the user-details:
@Entity
@Table(name = "UserDetails")
public class UserDetails implements Serializable {
private static final long serialVersionUID = 957231221603878419L;
/*--- Members ---*/
/**
* Shared Key
*/
@Id
@GeneratedValue(generator = "User-Primary-Key")
@GenericGenerator(name = "User-Primary-Key", strategy = "foreign", parameters = { @Parameter(name = "property", value = "user") })
@Column(name = "User_Id")
protected long id;
@Getter
@Setter
@OneToOne(optional = false, fetch = FetchType.LAZY)
@PrimaryKeyJoinColumn
private User user;
...
}
When generating the schema, the foreign-key from users-details table to users table is missing the cascading.
Here is the schema creation of the user-details:
CREATE TABLE `userdetails` (
`User_Id` bigint(20) NOT NULL,
`Creation_Time` bigint(20) NOT NULL,
`EMail` varchar(128) DEFAULT NULL,
`Enabled` bit(1) NOT NULL,
`First_Name` varchar(15) DEFAULT NULL,
`Last_Name` varchar(25) DEFAULT NULL,
`Password` varchar(64) NOT NULL,
`User_Name` varchar(15) NOT NULL,
PRIMARY KEY (`User_Id`),
UNIQUE KEY `User_Name` (`User_Name`),
UNIQUE KEY `EMail` (`EMail`),
KEY `FKAE447BD7BF9006F5` (`User_Id`),
CONSTRAINT `FKAE447BD7BF9006F5` FOREIGN KEY (`User_Id`) REFERENCES `users` (`User_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
As you can see there isn't any "ON DELETE CASCADE" written there in the "FOREIGN KEY" section.
This issue is also described here and here as well.
So I tried adding the @OnDelete annotation above the userDetails member with no luck..
I then created my own dialect overriding the supportsCascadeDelete:
public class MySql5Dialect extends MySQL5InnoDBDialect {
public MySql5Dialect() {
super();
}
@Override
public String getTableTypeString() {
return " ENGINE=InnoDB DEFAULT CHARSET=utf8";
}
@Override
public boolean supportsCascadeDelete() {
return true;
}
}
But still with no change. My foreign key cascading option still set to "RESTRICT" after generating the schema:
Is there a way to resolve this issue (non-manually of course)?
UPDATE
Following Angel Villalain's suggestion I put the @OnDelete annotation above the "user" member of the UserDetails class and this did the trick for the OneToOne relation, the delete is cascaded, but the OnUpdate is set to restrict (still), which leads me to my first question - what is the meaning of that? I mean "OnDelete" is pretty straight forward - when I delete the parent delete the child as well, but what is the meaning of the "OnUpdate" option? How does it affect my app when it set to restrict/cascade?
My second question refers to cascading with a OneToMany relation. My User class hols many UserProviders. The following code is from the User class:
@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
@JoinTable(name = "Users_Providers", joinColumns = @JoinColumn(name = "User_Id"), inverseJoinColumns = @JoinColumn(name = "Provider_Id"))
protected Set<UserProvider> userProviders = new HashSet<>(0);
And this is the inverse relation, from the UserProvider class:
@ManyToOne(fetch = FetchType.LAZY)
@JoinTable(name = "Users_Providers", joinColumns = @JoinColumn(name = "Provider_Id", insertable = false, updatable = false), inverseJoinColumns = @JoinColumn(name = "User_Id"))
@OnDelete(action = OnDeleteAction.CASCADE)
protected User user;
So after using the @OnDelete annotation I expected to see the onDelete option with cascade in the join table, but it isn't :( Have I used it correctly?
Last question - What about unidirectional relation such as @ElementCollection? My UserDetails class holds an ElementCollection of roles (each user can be assigned with one or more roles):
@ElementCollection(fetch = FetchType.EAGER, targetClass = Role.class)
@CollectionTable(name = "Users_Roles", joinColumns = @JoinColumn(name = "User_Id", referencedColumnName = "User_Id"))
@Column(name = "Role")
protected Set<Role> roles = new HashSet<Enums.Role>(0);
A Role is just an enum, not an entity hence I cannot point back from a role to the parent entity. In this case, is there a way to cascade the onDelete?
After investigating the issue I came up with the following methods to deal with DB schema generation (assuming you are using Hibernate as your JPA provider):
- Using the ddl schema generation, you can generate your DB schema. Using this option the schema will be created/updated while you start your web-server. If you use this method, in order to make sure your onDelete option is set to cascade you can use the OnDelete annotation. This worked for me just fine for a OneToOne relation (thanks to Angel Villalain) but for some reason it didn't work for a OneToMany relation. To workaround this gap I used Spring's ResourceDatabasePopulator:
The db-additions.sql file contains the queries that adapt my DB, in my case create the Ondelete Cascade. For instance:
ALTER TABLE `buysmartdb`.`users_providers` DROP FOREIGN KEY `FKB4152EEBBF9006F5` ;
ALTER TABLE `buysmartdb`.`users_providers`
ADD CONSTRAINT `FKB4152EEBBF9006F5`
FOREIGN KEY (`User_Id` )
REFERENCES `buysmartdb`.`users` (`User_Id` )
ON DELETE CASCADE
ON UPDATE CASCADE;
Notice that the scripts triggered by the ResourceDatabasePopulator applies after the schema is generated by Hibernate ddl, which is good. I know that sinply due to the final result, I couldn't really make sure it is guaranteed.
- A second method is to generate the schema using maven, in compile time. There are a few ways of doing that, such as this one or that one.
I hope this will help someone..
这篇关于在Mysql上使用ddl模式生成时，不会生成ON DELETE CASCADE选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
