java org.hibernate.exception.ConstraintViolationException 删除

org.hibernate.exception.ConstraintViolationException on delete

提问by eight

This is supposed to be basic stuff but i can't figure it out. I've got departament and worker. I can easily delete departament, even if there are workers on it but when i try to delete a worker i get the following error:

这应该是基本的东西，但我无法弄清楚。我有部门和工人。我可以轻松删除部门，即使上面有工作人员，但当我尝试删除工作人员时，我收到以下错误：

could not execute statement; SQL [n/a]; constraint ["FK_1LBUSQOSYF0O16356TFILYJPI: PUBLIC.DEPARTAMENT_WORKER FOREIGN KEY(WORKERS_ID) REFERENCES PUBLIC.WORKER(ID) (1)"; SQL statement: delete from Worker where id=? [23503-182]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

无法执行语句；SQL [不适用]；约束[“FK_1LBUSQOSYF0O16356TFILYJPI：PUBLIC.DEPARTAMENT_WORKER FOREIGN KEY（WORKERS_ID）REFERENCES PUBLIC.WORKER（ID）（1）”；SQL 语句：从 Worker where id=? [23503-182]]；嵌套异常是 org.hibernate.exception.ConstraintViolationException：无法执行语句

Full stack trace:

完整的堆栈跟踪：

    org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["FK_1LBUSQOSYF0O16356TFILYJPI:
PUBLIC.DEPARTAMENT_WORKER FOREIGN KEY(WORKERS_ID) REFERENCES PUBLIC.WORKER(ID) (1)"; SQL statement:
delete from Worker where id=? [23503-182]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:643)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:104)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:516)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:394)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)
at com.mypack.ui.DepartamentUI$$EnhancerByCGLIB$100ace.deleteWorker(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.el.parser.AstValue.invoke(AstValue.java:278)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:274)
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
at javax.faces.event.MethodExpressionActionListener.processAction(MethodExpressionActionListener.java:147)
at javax.faces.event.ActionEvent.processListener(ActionEvent.java:88)
at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:813)
at javax.faces.component.UICommand.broadcast(UICommand.java:300)
at javax.faces.component.UIData.broadcast(UIData.java:1108)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129)
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 org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:75)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:512)
... 44 more
Caused by: org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "FK_1LBUSQOSYF0O16356TFILYJPI: PUBLIC.DEPARTAMENT_WORKER
FOREIGN KEY(WORKERS_ID) REFERENCES PUBLIC.WORKER(ID) (1)"; SQL statement:
delete from Worker where id=? [23503-182]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:426)
at org.h2.constraint.ConstraintReferential.checkRowRefTable(ConstraintReferential.java:443)
at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:318)
at org.h2.table.Table.fireConstraints(Table.java:908)
at org.h2.table.Table.fireAfterRow(Table.java:926)
at org.h2.command.dml.Delete.update(Delete.java:100)
at org.h2.command.CommandContainer.update(CommandContainer.java:78)
at org.h2.command.Command.executeUpdate(Command.java:254)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:143)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)
... 59 more

The error seems to indicate bad database design? How do i do this right?

该错误似乎表明数据库设计不佳？我该怎么做？

Excerpt from main.xhtml (this works):

摘自 main.xhtml（这有效）：

<h:commandLink action="#{departamentUI.deleteCurrentDepartament1(departament.id)}" value="Delete"/>

<h:commandLink action="#{departamentUI.deleteCurrentDepartament1(departament.id)}" value="Delete"/>

Excerpt from worker.xhtml (this doesn't work):

摘自 worker.xhtml（这不起作用）：

<h:commandLink actionListener="#{departamentUI.deleteWorker(worker.id)}" value="Delete" />

<h:commandLink actionListener="#{departamentUI.deleteWorker(worker.id)}" value="Delete" />

Departament.java:

部门.java：

@Entity
public class Departament implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;

@OneToMany(fetch = FetchType.EAGER)
private Set<Worker> workers;

-- Getters & Setters & ID generation--

Worker.java:

工人.java：

@Entity
public class Worker implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
private String lastName;
-- Getters & Setters & ID generation--

DepartamentUI.java:

部门UI.java：

public class DepartamentUI implements Serializable {

@PersistenceContext
private EntityManager entityManager;
private Departament currentDepartament1;
private Worker currentWorker;
private String newWorkerName;
private String newWorkerLastName; 
private String newDepartament1Name;
   @Transactional
public void deleteWorker(Long id) {
    currentWorker = entityManager.find(Worker.class, id);
    entityManager.remove(currentWorker);    
}

   @Transactional 
   public void deleteCurrentDepartament1(Long id){
   currentDepartament1 = entityManager.find(Departament.class, id);
   entityManager.remove(currentDepartament1);
   }
etc. etc.

回答by geert3

You have a many-to-many relation between Workerand Department. In your database this is reflected by the additional table DEPARTMENT_WORKER. This is why you can't simply delete Workerobjects: you also need to delete all references to it in this join table. If you set up proper many-to-many relationship in your Java Entities, then Hibernate will take care of deleting the entries in the join table. PS: The joining table has no explicit Java class equivalent. This is what you should add to express the many-to-many relationship (and the getters/setters):

Worker和之间存在多对多关系Department。在您的数据库中，这由附加表反映出来DEPARTMENT_WORKER。这就是您不能简单地删除Worker对象的原因：您还需要删除此连接表中对它的所有引用。如果您在 Java 实体中设置了正确的多对多关系，那么 Hibernate 将负责删除连接表中的条目。PS：连接表没有显式的 Java 类等效项。这是您应该添加以表达多对多关系（以及 getter/setter）的内容：

In Worker.java:

在 Worker.java 中：

@ManyToMany(fetch = FetchType.EAGER, mappedBy="worker") 
private List<Department> departments = new ArrayList<Department>();

in Department.java:

在 Department.java 中：

@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(name = "department_worker", 
           joinColumns = @JoinColumn(name="department_id", referencedColumnName="id"), 
           inverseJoinColumns = @JoinColumn(name = "worker_id", referencedColumnName="id")
)
private List<Worker> workers = new ArrayList<Worker>();

Before deleting a Worker, you should remove that Worker from each of its Departments' worker-list:

在删除 Worker 之前，您应该从其每个部门的 worker 列表中删除该 Worker：

for (Department d: myWorker.getDepartments())
    d.getWorkers().remove(myWorker);
entityManager.remove(myWorker);

Same when deleting a Department: you first remove the Department from each of its Workers' Department-list:

删除部门时相同：您首先从其每个工人部门列表中删除该部门：

for (Worker w: myDepartment.getWorkers())
    w.getDepartments().remove(myDepartment);
entityManager.remove(myDepartment);

HTH.

哈。