[已解决]SpringDataJPA+Hibernate在执行executeUpdate()的时候报错 Executing an update/delete query
[已解决]SpringDataJPA+Hibernate在执行executeUpdate()的时候报错 Executing an update/delete query
小小鱼儿小小林
发布于 2020-06-24 12:06:24
发布于 2020-06-24 12:06:24
前言
今天在做一个小功能的时候，出现了一个令人抓狂的异常，一直以为是自己代码或者sql 的问题，讲道理，这么简单的一个SQL我都会写错吗，不可能？讲道理这么简单的一个功能，我逻辑会写错吗，也不可能？总之检查了一遍又一遍，最终才找到解决方法。
一、问题出现
咱们来回顾一下这个异常
首先我写了一个简单的SQL语句，如下
public int updateVoiceLinePhoneNo(String lineId,String totalPhoneNo) {
String sql="UPDATE yjltable SET name='"+totalPhoneNo+"' WHERE id='111'";
System.out.println(sql.toString());
int i = em.createNativeQuery(sql).executeUpdate();
return i;
}然后service和controller层我就省略了，很简单的三层架构例子
当时抓狂的异常就在下面了，部分敏感部分我就用*号替换了，也不重要
17:21:42.209 [grp0#CsfServerRequestHandleThread-3630ad242aa24fea9f2c2f6a5ea35435] DEBUG com.****************.executor.request.filter.RecordFilter - 请求 [requestId=3630ad242aa24fea9f2c2f6a5ea35435, serviceCode=MinTools_**************_updateVoiceLinePhoneNo, version=1.0, systemParams={Accept=*/*, User-Agent=Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:67.0) Gecko/20100101 Firefox/67.0, Connection=keep-alive, Host=localhost:36102, Accept-Encoding=gzip, deflate, serialize-type=json, uuid=3630ad242aa24fea9f2c2f6a5ea35435, csf.app.ip=0:0:0:0:0:0:0:1, csf.app.name=null, csf_version=1.0, Accept-Language=zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2, Content-Length=38, Content-Type=application/json}, businessParams={lineId=111, totalPhoneNo=aaaaaaaa}, calleeProtocol=RESTFUL, isDevelopPattern=false, timeoutByApi=-1]
UPDATE yjltable SET name='aaaaaaaa' WHERE id='111'
17:21:45.552 [grp0#CsfServerRequestHandleThread-3630ad242aa24fea9f2c2f6a5ea35435] DEBUG org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler - Creating new EntityManager for shared EntityManager invocation
17:21:45.640 [grp0#CsfServerRequestHandleThread-3630ad242aa24fea9f2c2f6a5ea35435] DEBUG org.hibernate.stat.internal.StatisticsInitiator - Statistics initialized [enabled=false]
17:21:45.745 [grp0#CsfServerRequestHandleThread-3630ad242aa24fea9f2c2f6a5ea35435] DEBUG org.hibernate.engine.transaction.internal.TransactionImpl - On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
17:21:45.746 [grp0#CsfServerRequestHandleThread-3630ad242aa24fea9f2c2f6a5ea35435] DEBUG org.springframework.orm.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
17:21:45.752 [grp0#CsfServerRequestHandleThread-3630ad242aa24fea9f2c2f6a5ea35435] DEBUG com.****************.common.exception.adapter.ServerExceptionAdapter - 服务端异常处理的实现类为：com.****************.common.exception.handler.DefaultServerExceptionHandler
17:21:45.957 [grp0#CsfServerRequestHandleThread-3630ad242aa24fea9f2c2f6a5ea35435] ERROR com.****************.executor.request.worker.AsyncRequestTask - 业务执行线程抛出异常:{date=2019-06-17 17:21:45, exceptionStack=javax.persistence.TransactionRequiredException: Executing an update/delete query
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1586)
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.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:402)
at com.sun.proxy.$Proxy88.executeUpdate(Unknown Source)
at com.*************.dao.VoiceLineDaoImpl.updateVoiceLinePhoneNo(VoiceLineDaoImpl.java:65)
at com.*************.msservice.service.impl.VoiceLineServiceImplCSVImpl.updateVoiceLinePhoneNo(VoiceLineServiceImplCSVImpl.java:206)
at com.****************.msservice.controller.impl.VoiceLineControllerCSVImpl.updateVoiceLinePhoneNo(VoiceLineControllerCSVImpl.java:48)
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 com.****************.executor.request.worker.ServiceExecutor.invoke(ServiceExecutor.java:33)
at com.****************.executor.request.worker.ServiceExecutorAdapter.invoke(ServiceExecutorAdapter.java:59)
at com.****************.executor.request.filterchain.FilterChainImpl.doFilter(FilterChainImpl.java:179)
at com.****************.executor.request.filter.params.mapping.TransportParamsMappingFilter.doFilter(TransportParamsMappingFilter.java:82)
at com.****************.executor.request.filter.ParamsMappingWrapperFilter.doFilter(ParamsMappingWrapperFilter.java:114)
at com.****************.executor.request.filterchain.FilterChainImpl.doFilter(FilterChainImpl.java:173)
at com.****************.executor.request.filter.ProcessServiceFilter.doFilter(ProcessServiceFilter.java:38)
at com.****************.executor.request.filterchain.FilterChainImpl.doFilter(FilterChainImpl.java:173)
at com.****************.executor.request.filter.ServiceValidateFilter.doFilter(ServiceValidateFilter.java:61)
at com.****************.executor.request.filter.DevelopNoHandleWrapperFilter.doFilter(DevelopNoHandleWrapperFilter.java:28)
at com.****************.executor.request.filterchain.FilterChainImpl.doFilter(FilterChainImpl.java:173)
at com.****************.executor.request.filter.ThrouputControlFilter.doFilter(ThrouputControlFilter.java:23)
at com.****************.executor.request.filter.DevelopNoHandleWrapperFilter.doFilter(DevelopNoHandleWrapperFilter.java:28)
at com.****************.executor.request.filterchain.FilterChainImpl.doFilter(FilterChainImpl.java:173)
at com.****************.executor.request.filter.RecordFilter.doFilter(RecordFilter.java:27)
at com.****************.executor.request.filterchain.FilterChainImpl.doFilter(FilterChainImpl.java:173)
at com.****************.executor.request.filter.SystemParamsHandleFilter.doFilter(SystemParamsHandleFilter.java:41)
at com.****************.executor.request.filterchain.FilterChainImpl.doFilter(FilterChainImpl.java:173)
at com.****************.executor.request.worker.FilterAndExecute.filterAndExecute(FilterAndExecute.java:37)
at com.****************.executor.request.worker.AsyncRequestTask.executeCall(AsyncRequestTask.java:167)
at com.****************.thread.pool.MuiltPoolTask$AbstractMuiltPoolTask.call(MuiltPoolTask.java:159)
at com.****************.thread.pool.MuiltPoolTask$AbstractMuiltPoolTask.call(MuiltPoolTask.java:100)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
, serviceCode=MinTools_IVoiceLineControllerCSV_updateVoiceLinePhoneNo, processName=DEFAULT_APPFRAME_SERVER_NAME1560763302186, requestId=3630ad242aa24fea9f2c2f6a5ea35435, resultCode=csf1045, exceptionMessage=调用服务MinTools_***********************_updateVoiceLinePhoneNo的业务代码发生异常}
17:21:46.017 [grp0#CsfServerRequestHandleThread-3630ad242aa24fea9f2c2f6a5ea35435] DEBUG com.****************.thread.pool.MuiltPoolTask$AbstractMuiltPoolTask - 任务MinTools_***************************_updateVoiceLinePhoneNo销毁完成，状态变更为FINISH_RUN,耗时3976ms异常截图如下，截图可以看的更清楚一些
主要是，在执行select （em.createNativeQuery(sql).getResultList()）语句是可以的，但是执行DML等sql语句的时候，比如update（em.createNativeQuery(sql).executeUpdate）就会报这种错。反正是检查了一遍自己所有的代码，确认不是自己的问题后，才开始寻找大神们的解决方法，最后的最后，翻阅了各种“没有用、或者不相关”的内容后，在小小的一个评论里发现了某大神的身影。
二、问题解决
解决方法：
大家在Dao层上，有DML语句（update、delete、insert）上，加上这三个注解@Transactional@Modifying@Query 就可以了，如图所示
@Transactional
@Modifying
@Query 
import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.Modifying;这样的话，再次运行测试，就不会再报错了，
至于为什么要加上 这几个注解呢？
这就涉及到官方的问题了：jpa官方文档：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/
参考文章
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2019-12-21 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录