前言
今天在做一个小功能的时候，出现了一个令人抓狂的异常，一直以为是自己代码或者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/
这里我就不过多解释了，我找了一篇相对而言介绍的比较详细和通俗易懂的，大家可以看一下下面这篇文章：
JPA中自定义的插入、更新、删除方法为什么要添加@Modifying注解和@Transactional注解？
