器件分类删除代码走读

最新推荐文章于 2024-04-19 22:06:19 发布

iteye_14867

最新推荐文章于 2024-04-19 22:06:19 发布

阅读量89

点赞数

分类专栏： ORM 文章标签： Java Hibernate SQL SQL Server Apache

ORM 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

问题

日前小何在器件管理2模块中删除一个器件分类（同时删除器件分类审批团队）时，系统抛出异常；表现为，审批团队成功删除了，器件分类删除不成功。

为了描述清楚该问题，我们先描述器件分类与审批团队的关系（hibernate映射关系）：
Hibernate映射关系
```
审批团队--many-to-one-->器件分类
器件分类--one-to-one-->审批团队
```

删除器件分类的manager代码块：

com.kedacom.ksoa3.apparatus2.domain.manager.ApparatusTypeManagerImpl.removeApparatusType

	ApparatusTypePO type = typeDao.getById(typeId);
	ApparatusTypeTeamPO team = type.getApparatusTypeTeam();
	if (team != null) {
		teamDao.remove(team);
	}

	if (!this.removeApparatusChildrenType(typeId)) {
		return Message.getMessage(false, "无法删除，请联系管理员！");
	}

删除时发生异常的信息

2009-09-11 11:11:58 [ERROR]  Duplicate key or integrity constraint violation message from server: "Cannot delete or update a parent row: a foreign key constraint fails (`ksoa3/apparatus2_type_team`, CONSTRAINT `FK78347D72721385C6` FOREIGN KEY (`type_id`) REFERENCES `apparatus2_type` (`id`))"

 <org.hibernate.util.JDBCExceptionReporter> 
org.hibernate.exception.ConstraintViolationException: could not execute update query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
	at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
	at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
	at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
	at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
	at com.kedacom.common.base.domain.dao.BaseDaoHibernateImpl$1.doInHibernate(BaseDaoHibernateImpl.java:271)
	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
	at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
	at com.kedacom.common.base.domain.dao.BaseDaoHibernateImpl.executeHql(BaseDaoHibernateImpl.java:264)
	at com.kedacom.components.tree.manager.impl.TreeNodeManagerImpl.removeTreeNodeAndDescendants(TreeNodeManagerImpl.java:623)
	at com.kedacom.components.tree.manager.impl.TreeNodeManagerImpl.removeLeaf(TreeNodeManagerImpl.java:596)
	at com.kedacom.ksoa3.apparatus2.domain.manager.ApparatusTypeManagerImpl.removeApparatusChildrenType(ApparatusTypeManagerImpl.java:103)
	at com.kedacom.ksoa3.apparatus2.domain.manager.ApparatusTypeManagerImpl.removeApparatusType(ApparatusTypeManagerImpl.java:216)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy73.removeApparatusType(Unknown Source)
	at com.kedacom.ksoa3.apparatus2.web.controller.ApparatusTypeController.delete(ApparatusTypeController.java:118)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:421)
	at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:136)
	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:326)
	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:313)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at com.automatedlogic.domino.sso.DominoLoginFilter.doFilter(DominoLoginFilter.java:87)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
	at java.lang.Thread

.run(Thread

.java:595)
Caused by: java.sql.SQLException: Duplicate key or integrity constraint violation message from server: "Cannot delete or update a parent row: a foreign key constraint fails (`ksoa3/apparatus2_type_team`, CONSTRAINT `FK78347D72721385C6` FOREIGN KEY (`type_id`) REFERENCES `apparatus2_type` (`id`))"


	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2001)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1168)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1279)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2281)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1825)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1667)
	at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
	at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
	... 57 more

异常发生时的SQL信息

// 这里省略与tree_node、tree的关联


select type.*, team.* from apparatus2_type type left outer

 join apparatus2_type_team team on team.type_id=type.id where type.id=?

select team.* from apparatus2_type_team team where team.type_id=?

delete from apparatus2_type where tree_id='3' and left_node>=2 and right_node<=3
2009-09-11 11:11:58 [WARN]  SQL Error: 1451, SQLState: 23000 <org.hibernate.util.JDBCExceptionReporter>
2009-09-11 11:11:58 [ERROR]  Duplicate key or integrity constraint violation message from server: "Cannot delete or update a parent row: a foreign key constraint fails (`ksoa3/apparatus2_type_team`, CONSTRAINT `FK78347D72721385C6` FOREIGN KEY (`type_id`) REFERENCES `apparatus2_type` (`id`))"

 <org.hibernate.util.JDBCExceptionReporter> 
org.hibernate.exception.ConstraintViolationException: could not execute update query
...

delete from apparatus2_type_team where id=?

分析与调试

整个过程是否在Spring事务控制下？
1. 经查，整个方法是在Spring事务控制下的。
打开Hibernate SQL打印
1. 发现了上面列出的"异常发生时的SQL信息"，其中删除team和删除type的顺序恰好与我们指定的相反。为什么会这样呢？我们在最后给出答案。
尝试使用Hibernate的级联删除，在删除type的同时级联删除team
1. 没有效果，因为我们是采用sql语句删除type的，这个sql语句不会引发级联删除的；只有对对象的操作，才可能引发对其属性的级联操作。
hibernate改变了SQL执行的顺序，是否为inverse问题？
1. （后来发现也不是inverse的问题，而是hiberanate清理缓存的机制引起的）根据这个猜测，我们认为只要在删除team时手工清理一下 session，hibernate就不会改变SQL执行的顺序。于是，我们就改变了 ApparatusTypeTeamDaoHibernateImpl删除team的行为（更改代码后，问题解决）：
  com.kedacom.ksoa3.apparatus2.domain.dao.ApparatusTypeTeamDaoHibernateImpl.remove
```
	public boolean remove(ApparatusTypeTeamPO team){
		super.remove(team);
		super.getHibernateTemplate().flush();
		return true;
	} 
```

最后说明为什么Hibernate会改变我们sql的执行顺序——Hibernate Session清理时，执行sql的顺序：

Hibernate Session在清理缓存时，会按照什么顺序来执行Sql语句?

http://www.wangchao.net.cn/bbsdetail_854253.html

按照应用程序调用Session.Save()方法的先后顺序，执行所有对实体进行插入的insert语句。
执行所有对实体进行更新的update语句。
执行所有对集合进行删除的delete语句。
执行所有对集合元素进行更新、删除、插入的sql语句。
执行所有对集合进行插入的insert语句。
按照应用程序调用Sesson.delete()方法的先后顺序，执行所有对实体进行删除的delete语句。

根据上面的说明，在我们的代码里首先由两个get生成两条select语句；
然后我们的代码delete一个team对象；
再执行一个delete type的sql语句（我们的removeApparatusChildrenType实际上是通过sql语句来删除type对象的）；

由于type同时也是team的一个属性，所以会依据第4条"执行所有对集合元素进行更新、删除、插入的sql语句"先执行delete type的sql语句，然后才会根据第6条"按照应用程序调用Sesson.delete()方法的先后顺序，执行所有对实体进行删除的delete语句"来执行delete team对象的语句。