Hibernate: delete from t_bbs_article where article_id=? or parent_id=?
2007-09-23 15:26:41,046 [WARN ] [http-8080-Processor24] org.hibernate.util.JDBCExceptionReporter SQL Error: 0, SQLState: 23503
2007-09-23 15:26:41,046 [ERROR] [http-8080-Processor24] org.hibernate.util.JDBCExceptionReporter ERROR: update or delete on "t_bbs_article" violates foreign key constraint "t_bbs_file_upload_article_id_fkey" on "t_bbs_file_upload"
Detail: Key (article_id)=(715630811400827186615633296858) is still referenced from table "t_bbs_file_upload".
2007-09-23 15:26:41,046 [ERROR] [http-8080-Processor24] com.geong.centurypark.bbs.dao.imp.BbsArticleDaoImp BbsArticleDao is executing to deleteArticle articleID=715630811400827186615633296858 occurs error!
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:334)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:209)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1126)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at com.geong.centurypark.bbs.dao.imp.BbsArticleDaoImp.deleteTopic(BbsArticleDaoImp.java:111)
at com.geong.centurypark.bbs.service.impl.ArticleServiceImp.deleteTopic(ArticleServiceImp.java:97)
at com.geong.centurypark.bbs.service.proxy.ForumArticleServiceProxy.deleteTopic(ForumArticleServiceProxy.java:156)
at com.geong.centurypark.bbs.struts.action.ManagerAction.deleteTopicByID(ManagerAction.java:445)
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.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
at org.apache.struts.actions.MappingDispatchAction.execute(MappingDispatchAction.java:169)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.geong.centurypark.bbs.struts.filter.SessionCloseFilter.doFilter(SessionCloseFilter.java:46)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at common.SetEncodingFilter.doFilter(SetEncodingFilter.java:62)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:595)
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on "t_bbs_article" violates foreign key constraint "t_bbs_file_upload_article_id_fkey" on "t_bbs_file_upload"
Detail: Key (article_id)=(715630811400827186615633296858) is still referenced from table "t_bbs_file_upload".
从输出的SQL语句可知,Hibernate每有先执行删除t_bbs_file_upload关联的记录所以导致错误
DAO层代码如下:
try {
Session session = HibernateSessionFactory.currentSession();
Query query = session
.createQuery(" from TBbsArticle where articleId=:articleId or parentId=:parentId");
// 考虑安全性
query.setString("articleId", topicId);
query.setString("parentId", topicId);
log.debug("SQL:" + query.getQueryString());
return query.executeUpdate();
} catch (Exception e) {
log.error("BbsArticleDao is executing to deleteArticle articleID="
+ topicId + " occurs error!");
e.printStackTrace();
throw new DataBaseAccessException(e);
}
即使在映射文件中配置的需要的属性这种方式貌似也不会批处理去执行级联删除(本人尝试了各种属性组合都删除失败,原因还是没找出来),于是改了一中思路,遍码实现逐个删除
修改后代码如下:
try {
Session session = HibernateSessionFactory.currentSession();
Query query = session
.createQuery(" from TBbsArticle where articleId=:articleId or parentId=:parentId");
// 考虑安全性
query.setString("articleId", topicId);
query.setString("parentId", topicId);
List list = query.list();
Iterator iterator = list.iterator();
while (iterator.hasNext()) {
TBbsArticle article = (TBbsArticle) iterator.next();
session.delete(article);
}
// log.debug("SQL:" + query.getQueryString());
return list.size();
} catch (Exception e) {
log.error("BbsArticleDao is executing to deleteArticle articleID="
+ topicId + " occurs error!");
e.printStackTrace();
throw new DataBaseAccessException(e);
}
映射文件 one方只需要配<set name="TBbsFileUploadSet" inverse="true" cascade="all-delete-orphan"
>
<key column="article_id" />
<one-to-many class="TBbsFileUpload" />
</set>即可
如级联删除的数据量过大可以考虑配置 适当的 batch-size="5" 属性值
如果不配置 cascad 属性 会抛出如下异常信息:
2007-09-23 15:49:04,000 [WARN ] [http-8080-Processor20] org.hibernate.util.JDBCExceptionReporter SQL Error: 0, SQLState: null
2007-09-23 15:49:04,000 [ERROR] [http-8080-Processor20] org.hibernate.util.JDBCExceptionReporter 批次处理0delete from t_bbs_article where article_id=692028301833894908261516348167失败,调用getNextException查看原因。
2007-09-23 15:49:04,000 [WARN ] [http-8080-Processor20] org.hibernate.util.JDBCExceptionReporter SQL Error: 0, SQLState: 23503
2007-09-23 15:49:04,000 [ERROR] [http-8080-Processor20] org.hibernate.util.JDBCExceptionReporter ERROR: update or delete on "t_bbs_article" violates foreign key constraint "t_bbs_file_upload_article_id_fkey" on "t_bbs_file_upload"
Detail: Key (article_id)=(692028301833894908261516348167) is still referenced from table "t_bbs_file_upload".
2007-09-23 15:49:04,015 [ERROR] [http-8080-Processor20] org.hibernate.event.def.AbstractFlushingEventListener Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
问题解决。。。