Hibernate OnDelete Cascade不适用于MySql，但适用于postgres和Ms-Sql [英] Hibernate OnDelete Cascade not working for MySql but Works on postgres and Ms-Sql

问题描述

我有2个实体。使用来自 Post-> Thread的OnetoOne映射线程实体和发布实体。

主题实体包含众多帖子。我知道我应该使用OnetoMany而不是OnetoOne，但为了避免所有的集合问题，我正在使用OnetoOne

现在问题是，当我删除线程，所有与其关联的发布也必须移除。
我通过使用

  @OnDelete（action = OnDeleteAction.CASCADE）
  

但它仅适用于Postgres和Ms-SQl，但不适用于MySql（也试过InnoDb）。在模式生成查询中不会生成删除级联。

以下是代码

  //线程实体
 @Id 
 @GeneratedValue 
 @Column（name =thread_id）
 private int ThreadID; 
  

---

  //发布实体
 @Id 
 @GeneratedValue（strategy = GenerationType.AUTO）
 @Column（name =post_id）
 private int PostID; 
 
 @OneToOne（）
 @OnDelete（action = OnDeleteAction.CASCADE）
私有线程ThreadID; 
  

我收到以下错误从主题实体中删除项目以下查询

  session.delete（session.load（thread.class，1））; 
  

2014年9月2日8:33:51 org.hibernate。 engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN：SQL错误：1451，SQLState：23000
2014年9月2日下午8:33:51 org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
错误：无法删除或更新父行：外键约束失败（ forum 。 post_tbl ，CONSTRAINT FK_bfbv5nknqj7ppd5630scimhtb FOREIGN KEY（ ThreadID_thread_id ）参考 thread_tbl （<$ c
org.hibernate.exception.ConstraintViolationException：无法执行语句
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert（SQLExceptionTypeDelegate.java： 74）
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert（StandardSQLExceptionConverter.java:49）
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert（SqlExceptionHelper.java:125）
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert（SqlExceptionHelper.java:110）
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate（ResultSetReturnImpl.java:136）
在org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch（NonBatchingBatch.java:58）
at org.hibernate.persister.entity.AbstractEntityPersister.delete（AbstractEntityPersister.java:3343）
在org.hibernate.persister.entity.AbstractEntityPersister.delete（AbstractEntityPersister.java:3546）
在org.hibernate.action.internal.EntityDeleteAction.execute（EntityDeleteAction.java:100）
在org.hibernate .engine.spi.ActionQueue.execute（ActionQueue.java:377）
at org.hibernate.engine.spi.ActionQueue.executeActions（ActionQueue.java:369）
at org.hibernate.engine.spi .ActionQueue.executeActions（ActionQueue.java:293）
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions（AbstractFlushi ngEventListener.java:339）
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush（DefaultFlushEventListener.java:52）
at org.hibernate.internal.SessionImpl.flush（SessionImpl.java:1234）
at org.hibernate.internal.SessionImpl.managedFlush（SessionImpl.java:404）
at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit（JdbcTransaction.java:101）
at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit（AbstractTransactionImpl.java:175）
at test.main（test.java:84）
引起：com.mysql.jdbc .exceptions.jdbc4.MySQLIntegrityConstraintViolationException：无法删除或更新父行：外键约束失败（ forum 。 post_tbl ，CONSTRAINT FK_bfbv5nknqj7ppd5630scimhtb FOREIGN KEY（ ThreadID_thread_id ）REFERENCES thread_tbl （ thread_id ））
HibernateException：无法执行状态nt
at sun.reflect.NativeConstructorAccessorImpl.newInstance0（Native Method）
at sun.reflect.NativeConstructorAccessorImpl.newInstance（NativeConstructorAccessorImpl.java:62）
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance（DelegatingConstructorAccessorImpl .java：45）
at java.lang.reflect.Constructor.newInstance（Constructor.java:408）
at com.mysql.jdbc.Util.handleNewInstance（Util.java:411）
在com.mysql.jdbc.Util.getInstance（Util.java:386）
在com.mysql.jdbc.SQLError.createSQLException（SQLError.java:1040）
在com.mysql.jdbc。 MysqlIO.checkErrorPacket（MysqlIO.java:4120）
at com.mysql.jdbc.MysqlIO.checkErrorPacket（MysqlIO.java:4052）
at com.mysql.jdbc.MysqlIO.sendCommand（MysqlIO.java： 2503）
在com.mysql.jdbc.MysqlIO.sqlQueryDirect（MysqlIO.java:2664）
在com.mysql.jdbc.ConnectionImpl.execSQL（ConnectionImpl.java:2794）
在com .mysql.jdbc.PreparedStatemen tCommandInternal（PreparedStatement.java:2155）
at com.mysql.jdbc.PreparedStatement.executeUpdate（PreparedStatement.java:2458）
at com.mysql.jdbc.PreparedStatement.executeUpdate（PreparedStatement.java： 2375）
at com.mysql.jdbc.PreparedStatement.executeUpdate（PreparedStatement.java:2359）
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate（ResultSetReturnImpl.java:133）
... 14 more
BUILD SUCCESSFUL（总时间：2秒）

请帮我找出路。

解决方案

这是我为解决基于Spring Boot的这个问题所做的。

解决方案

来自

  // application.properties 
 spring.jpa.database-platform = org.hibernate.dialect.MySQLDialect 
  

到

  spring.jpa.database-platform = org.hibernate.dialect。 MySQL5InnoDBDialect 
  

Explnation

如下图所示，似乎MySQL5InnoDBDialect.java中CascadeDelete的默认值只支持Cascade。

  // MySQLDialect.java 
 @Override 
 public boolean supportsCascadeDelete（）{
 return false; 
} 
 
 // MySQL5InnoDBDialect.java 
 @Override 
 public boolean supportsCascadeDelete（）{
 return true; 
} 
  

I'm having 2 Entities. Thread entity and Post entity using OnetoOne mapping from Post->Thread.

A Thread entity contains Numerous Posts. I know i should have used OnetoMany instead of OnetoOne, but for avoiding all the Collections problems i'm using OnetoOne

Now the problem is, when i delete a Thread, all the post associated with it must also be removed. I'm successful in doing it by using

@OnDelete(action = OnDeleteAction.CASCADE)

But it works only on Postgres and Ms-SQl but not on MySql(Tried InnoDb as well). The on delete cascade is not generated in the schema generation query.

Following are the code

//Thread Entity
@Id
@GeneratedValue
@Column(name = "thread_id")
private int ThreadID;

---

//Post Entity
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "post_id")
private int PostID;

@OneToOne()
@OnDelete(action = OnDeleteAction.CASCADE)
private thread ThreadID;

I get the following error While deleting an item from Thread entity using the following query

session.delete(session.load(thread.class,1));

Sep 02, 2014 8:33:51 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions WARN: SQL Error: 1451, SQLState: 23000 Sep 02, 2014 8:33:51 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR: Cannot delete or update a parent row: a foreign key constraint fails (forum.post_tbl, CONSTRAINT FK_bfbv5nknqj7ppd5630scimhtb FOREIGN KEY (ThreadID_thread_id) REFERENCES thread_tbl (thread_id)) org.hibernate.exception.ConstraintViolationException: could not execute statement at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:74) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:136) at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:58) at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3343) at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3546) at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:100) at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:377) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:369) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:293) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:339) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1234) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:404) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175) at test.main(test.java:84) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (forum.post_tbl, CONSTRAINT FK_bfbv5nknqj7ppd5630scimhtb FOREIGN KEY (ThreadID_thread_id) REFERENCES thread_tbl (thread_id)) HibernateException : could not execute statement at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:408) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133) ... 14 more BUILD SUCCESSFUL (total time: 2 seconds)

Please help me find a way out of this.

解决方案

This is what I did to resolve this problem based on Spring Boot.

Solution

from

// application.properties  
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

to

spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

Explnation
As you can see below, it seems like the default value for CascadeDelete at MySQL5InnoDBDialect.java only support Cascade.

// MySQLDialect.java
@Override
    public boolean supportsCascadeDelete() {
        return false;
    }

// MySQL5InnoDBDialect.java
@Override
    public boolean supportsCascadeDelete() {
        return true;
    }

这篇关于Hibernate OnDelete Cascade不适用于MySql，但适用于postgres和Ms-Sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！