Tag Archives: DataIntegrityViolationException: Could not execute JDBC batch update

Hibernate Delete table exception DataIntegrityViolationException: Could not execute JDBC batch update

Recently, in a project, hibernate made a strange mistake when it physically deleted the delete method

For example

org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; SQL [update gwqmshop_goods_package_detail set goods_package_id=null where goods_package_id=?]; constraint [null]; nested exception is

In short, when deleting, the update error of other tables is exploded

For a moment, I was confused. Ask colleagues that it may be the concatenation of hibernate or the error associated with other tables

You can always use logical deletion to avoid this error, but you always feel a little dissatisfied. Is the difficulty really hibernate bug

Or why


For example, if you delete the table ShopCartPackage, the object has GoodsPackageDetail associated with it.
Here is where the association is made.

/**
	 * Goods Detail
	 */
	@OneToMany(fetch = FetchType.LAZY, cascade = {})
    @JoinColumn(name="goods_package_id", referencedColumnName="goods_package_id")
	@OrderBy("goodsThickness asc,goodsWidth asc")
	private List<GoodsPackageDetail> packageDetails;

The error is also to updae GoodsPackageDetail table to report an error

But why?  I remember that when I was writing the code to manage the detail table, the main table can be deleted directly ah!

So we tried to solve this problem again

There are main table goodshipackage and associated detail table goodshipackagedetail

That’s what they normally relate to

The main table looks like this
	 @OneToMany(mappedBy="goodsPackage",fetch=FetchType.LAZY)
	
	@OrderBy("goodsThickness asc,goodsWidth asc")
	private List<GoodsPackageDetail> details;


The detail object looks like this
@ManyToOne(fetch=FetchType.LAZY)
	@JoinColumn(name="goods_package_id",updatable=false,nullable=false)
	private GoodsPackage goodsPackage;


When I test this way, I can delete the main table goodsPackage directly without any problem. When I delete the main table, the corresponding detail table GoodsPackageDetail data is not deleted at the same time

So I refer to, another way to write, to delete the test

Main table:

	@OneToMany(fetch = FetchType.LAZY)
	@JoinColumn(name = "goods_package_id", referencedColumnName = "id")
	@OrderBy("goodsThickness asc,goodsWidth asc")
	private List<GoodsPackageDetail> details;

The breakdown is still the same.
@ManyToOne(fetch=FetchType.LAZY)
	@JoinColumn(name="goods_package_id",updatable=false,nullable=false)
	private GoodsPackage goodsPackage;


At this point, delete the main table will report an error, and the problem will be re

org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; SQL [update gwqmshop_goods_package_detail set goods_package_id=null where goods_package_id=?]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
	at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:643)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:103)
	at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:518)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:475)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:270)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:633)
	at com.gwqm.platform.goods.service.impl.GoodsPackageServiceImpl$$EnhancerBySpringCGLIB$$6dea54a.deleteDo(<generated>)
	at com.gwqm.ctrl.seller.action.goods.GoodsPackageSellerAction.delete(GoodsPackageSellerAction.java:387)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:440)
	at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:428)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:933)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:867)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:951)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:853)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:827)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:176)
	at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
	at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92)
	at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:394)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.gwqm.base.filter.PageCacheFiler.doFilter(PageCacheFiler.java:72)
	at net.sf.ehcache.constructs.web.filter.Filter.doFilter(Filter.java:86)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.gwqm.base.filter.SecondDomainFilter.doFilter(SecondDomainFilter.java:59)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
	at org.springframework.security.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
	at org.springframework.security.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at org.springframework.security.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
	at org.springframework.security.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at org.springframework.security.ui.SessionFixationProtectionFilter.doFilterHttp(SessionFixationProtectionFilter.java:67)
	at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at com.gwqm.base.security.support.ShopSecurityExceptionFilter.doFilterHttp(ShopSecurityExceptionFilter.java:88)
	at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at org.springframework.security.ui.ExceptionTranslationFilter.doFilterHttp(ExceptionTranslationFilter.java:101)
	at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at org.springframework.security.wrapper.SecurityContextHolderAwareRequestFilter.doFilterHttp(SecurityContextHolderAwareRequestFilter.java:91)
	at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at org.springframework.security.ui.AbstractProcessingFilter.doFilterHttp(AbstractProcessingFilter.java:277)
	at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at org.springframework.security.ui.logout.LogoutFilter.doFilterHttp(LogoutFilter.java:89)
	at com.gwqm.base.filter.NorLogoutFilter.doFilterHttp(NorLogoutFilter.java:76)
	at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at org.springframework.security.context.HttpSessionContextIntegrationFilter.doFilterHttp(HttpSessionContextIntegrationFilter.java:235)
	at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at org.springframework.security.concurrent.ConcurrentSessionFilter.doFilterHttp(ConcurrentSessionFilter.java:99)
	at org.springframework.security.ui.SpringSecurityFilter.doFilter(SpringSecurityFilter.java:53)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at org.springframework.security.util.FilterChainProxy.doFilter(FilterChainProxy.java:175)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:180)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.gwqm.base.filter.XSSFilter.doFilter(XSSFilter.java:80)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.gwqm.base.filter.PageUrlFilter.doFilter(PageUrlFilter.java:67)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1457)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:169)
	at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
	at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
	at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
	at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
	at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
	at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
	at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:514)
	... 111 more
Caused by: java.sql.BatchUpdateException: Column 'goods_package_id' cannot be null
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2054)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
	at com.p6spy.engine.wrapper.StatementWrapper.executeBatch(StatementWrapper.java:98)
	at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
	at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
	... 120 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'goods_package_id' cannot be null
	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:423)
	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.executeBatchSerially(PreparedStatement.java:2006)
	... 125 more

That is, without mappedby

Mappedby is used to specify which of the two entities with two-way relationship is associated. The party using mappedby must be the accused party. It has the following four characteristics

1. Mappedby attribute only exists on onetoone, onetomany and manytomany, but it does not exist in manytoone

2. @onetomany (mapped = “one party points to many party, and this attribute should be equal to the object name of one class attribute in many party, otherwise an error will occur”), that is, the object name of the accused defined in the main prosecution

3. The main controller of the relationship (that is, the Party of many) is responsible for the maintenance of the relationship. In the main controller, @ joincolumn is used to create a foreign key

4. Mappedby and joincolumn/jointable are always on the mutually exclusive side

The above is not difficult to understand, that is, when there is no mappedby, when the main table is deleted, goodspackage

Hibernate will disassociate the parts list from the goodshipackagedetail

So how to release it?That is, update the content of the corresponding main table field in the goodshipackagedetail of the detail table to null

In other words, SQL update will be sent: update gwqmshop_ goods_ package_ detail set goods_ package_ id=null where goods_ package_ id=?

However, in the parts list goodshipackagedetail, it is specified that the associated fields of the main table cannot be empty and cannot be updated: nullable = false, updatable = false

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name=”goods_ package_ id”,updatable=false,nullable=false)
private GoodsPackage goodsPackage;

Even if updatable = true and nullable = true are finally configured

It will still explode the same error because the final update is where goods_ package_ id=? The value is null. The value of the main table cannot be obtained

Why can’t I get it? Maybe it’s the cascade problem of hibernating. Field attribute: cascade, default is {} and there is no set value. I won’t go into it here

Maybe it’s written in a way that doesn’t follow hibernate’s rules

So in the end, it’s column ‘goods_package_id’ cannot be null

That is to prevent deletion, and the association of Hibernate is difficult to understand

It’s best not to physically delete it. Use logical delete. Or use mappedby to specify the maintainer of the relationship