[spring, jpa] Can not issue data manipulation statements with executeQuery()

jpa에서 nativeQuery를 사용하고 있습니다.

이 nativeQuery를 통한 update시 나타날 수 있는 에러 메시지 입니다.

 

* 에러가 발생되는 jpa Repository의 update nativeQuery

@Query(value="UPDATE ITEM_OF_QUESTION"
            + " SET DEL_YN = 'Y'"
            + ", SHOW_YN = 'N'"
            + ", DEL_DT = SYSDATE()"
            + ", DEL_BY=:delBy"
            + " WHERE RSCH_SEQ=:rschSeq AND DEL_YN = 'N'", nativeQuery=true)
public void delByRschSeq(@Param("rschSeq") Integer rschSeq, @Param("delBy") Integer delBy);

 

* 에러 내용

java.sql.SQLException: Can not issue data manipulation statements with executeQuery().

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)

at com.mysql.cj.jdbc.StatementImpl.checkForDml(StatementImpl.java:365)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:962)

at net.sf.log4jdbc.sql.jdbcapi.PreparedStatementSpy.executeQuery(PreparedStatementSpy.java:780)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)

at org.hibernate.loader.Loader.getResultSet(Loader.java:2303)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2056)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2018)

at org.hibernate.loader.Loader.doQuery(Loader.java:948)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)

at org.hibernate.loader.Loader.doList(Loader.java:2849)

at org.hibernate.loader.Loader.doList(Loader.java:2831)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2663)

at org.hibernate.loader.Loader.list(Loader.java:2658)

at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)

at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2141)

at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1172)

at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:176)

at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1593)

at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1641)

at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:196)

at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)

at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)

at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)

at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)

at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)

at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)

at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)

at com.sun.proxy.$Proxy106.delByRschSeq(Unknown Source)

at packagename.admin.research.service.ResearchService.updateItem(ResearchService.java:199)

at packagename.admin.research.service.ResearchService$$FastClassBySpringCGLIB$$3dac1fd9.invoke()

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)

at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)

at packagename.admin.research.service.ResearchService$$EnhancerBySpringCGLIB$$9e7f11fa.updateItem()

at packagename.admin.research.web.ResearchController.updateItem(ResearchController.java:151)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:880)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)

at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)

at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)

at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684)

at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Unknown Source)

 

Repository의 update nativeQuery 위에 @Modifying를 아래와 같이 사용해 주면 됩니다.

@Modifying
@Query(value="UPDATE ITEM_OF_QUESTION"
            + " SET DEL_YN = 'Y'"
            + ", SHOW_YN = 'N'"
            + ", DEL_DT = SYSDATE()"
            + ", DEL_BY=:delBy"
            + " WHERE RSCH_SEQ=:rschSeq AND DEL_YN = 'N'", nativeQuery=true)
public void delByRschSeq(@Param("rschSeq") Integer rschSeq, @Param("delBy") Integer delBy);

 

 

 

* 관련글

[spring, jpa] Cannot add or update a child row: a foreign key constraint fails : https://deonggi.tistory.com/115
[spring, jpa] Use @Param for query method parameters, or when on Java 8+ use the javac flag -parameters. : https://deonggi.tistory.com/121