page contents
首页 问答
正文

mybatis 批量update 出现error

Pack 发布于 2020-02-27 15:54
阅读 706
收藏 0
答案 1
分类：Java开发

问题如下：

传入list集合到动态sql中 进行遍历出现错误如下


   ### SQL: UPDATE imb_spares_manage.SPARES_ECO_NEW_MANAGE_STOCK_T               SET FIRST_CHECK_USERCODE=? , FIRST_CHOOSE_STATUS = ?               WHERE where  ID = ?       ;          UPDATE imb_spares_manage.SPARES_ECO_NEW_MANAGE_STOCK_T               SET FIRST_CHECK_USERCODE=? , FIRST_CHOOSE_STATUS = ?               WHERE where  ID = ?       ;              UPDATE imb_spares_manage.SPARES_ECO_NEW_MANAGE_STOCK_T               SET FIRST_CHECK_USERCODE=? , FIRST_CHOOSE_STATUS = ?               WHERE where  ID = ?       ;              UPDATE imb_spares_manage.SPARES_ECO_NEW_MANAGE_STOCK_T               SET FIRST_CHECK_USERCODE=? , FIRST_CHOOSE_STATUS = ?               WHERE where  ID = ?       ;              UPDATE imb_spares_manage.SPARES_ECO_NEW_MANAGE_STOCK_T               SET FIRST_CHECK_USERCODE=? , FIRST_CHOOSE_STATUS = ?               WHERE where  ID = ?       ;              UPDATE imb_spares_manage.SPARES_ECO_NEW_MANAGE_STOCK_T               SET FIRST_CHECK_USERCODE=? , FIRST_CHOOSE_STATUS = ?               WHERE where  ID = ?       ;              UPDATE imb_spares_manage.SPARES_ECO_NEW_MANAGE_STOCK_T               SET FIRST_CHECK_USERCODE=? , FIRST_CHOOSE_STATUS = ?               WHERE where  ID = ?       ;              UPDATE imb_spares_manage.SPARES_ECO_NEW_MANAGE_STOCK_T               SET FIRST_CHECK_USERCODE=? , FIRST_CHOOSE_STATUS = ?               WHERE where  ID = ?

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where  ID = '176427' 

     ; 

            UPDATE imb_spares_manage.SPARES_ECO_NE' at line 3

; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where  ID = '176427' 

     ; 

            UPDATE imb_spares_manage.SPARES_ECO_NE' at line 3

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364)

at com.sun.proxy.$Proxy15.update(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:250)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:49)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)

at com.sun.proxy.$Proxy79.updateByPrimaryKeySelectivePlus(Unknown Source)

at com.foxconn.spares.service.impl.EcoNewManageStockServiceImpl.operateChooseStockInfosplus(EcoNewManageStockServiceImpl.java:471)

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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

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

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

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

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

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

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

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

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

at com.sun.proxy.$Proxy81.operateChooseStockInfosplus(Unknown Source)

at com.foxconn.spares.controller.EcoManageStockController.operateChooseStockInfos(EcoManageStockController.java:307)

at com.foxconn.spares.controller.EcoManageStockController$$FastClassBySpringCGLIB$$478d1abc.invoke(<generated>)

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

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

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

at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)

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

at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:44)

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

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

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

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

at com.foxconn.spares.controller.EcoManageStockController$$EnhancerBySpringCGLIB$$62d50c60.operateChooseStockInfos(<generated>)

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.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)

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

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

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

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

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

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

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

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

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:870)

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

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

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.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)

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

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:493)

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

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

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:800)

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

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

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

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: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where  ID = '176427' 

     ; 

            UPDATE imb_spares_manage.SPARES_ECO_NE' at line 3

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:1052)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2683)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2144)

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)

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.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)

at com.sun.proxy.$Proxy119.update(Unknown Source)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)

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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)

... 77 more


传入参数如下：

{id=331914, usercode=admincdemd, status=1}

{id=331915, usercode=admincdemd, status=1}

{id=331916, usercode=admincdemd, status=1}

{id=331917, usercode=admincdemd, status=1}

{id=331918, usercode=admincdemd, status=1}

{id=331919, usercode=admincdemd, status=1}

{id=331920, usercode=admincdemd, status=1}

{id=331921, usercode=admincdemd, status=1}

{id=331922, usercode=admincdemd, status=1}

{id=331923, usercode=admincdemd, status=1}

{id=331924, usercode=admincdemd, status=1}

{id=331925, usercode=admincdemd, status=1}

{id=331926, usercode=admincdemd, status=1}

{id=331927, usercode=admincdemd, status=1}

{id=331928, usercode=admincdemd, status=1}

{id=331929, usercode=admincdemd, status=1}

{id=331930, usercode=admincdemd, status=1}

{id=331931, usercode=admincdemd, status=1}

{id=331932, usercode=admincdemd, status=1}

{id=331933, usercode=admincdemd, status=1}

{id=331934, usercode=admincdemd, status=1}

{id=331935, usercode=admincdemd, status=1}

{id=331936, usercode=admincdemd, status=1}

{id=331937, usercode=admincdemd, status=1}

{id=331938, usercode=admincdemd, status=1}

{id=331939, usercode=admincdemd, status=1}

{id=331940, usercode=admincdemd, status=1}

{id=331941, usercode=admincdemd, status=1}

{id=331942, usercode=admincdemd, status=1}

{id=331943, usercode=admincdemd, status=1}

{id=331944, usercode=admincdemd, status=1}

{id=331945, usercode=admincdemd, status=1}

{id=331946, usercode=admincdemd, status=1}

{id=331947, usercode=admincdemd, status=1}

{id=331948, usercode=admincdemd, status=1}

{id=331949, usercode=admincdemd, status=1}

{id=331950, usercode=admincdemd, status=1}


Controller 代码：


List<Map<String,Object> > temptist=mlist.subList(startindex, endindex);

sparesEcoNewManageStockTMapper.updateByPrimaryKeySelectivePlus(temptist);

动态xml如下：


<update id="updateByPrimaryKeySelectivePlus" parameterType="java.util.List" >  

   <foreach collection="list" item="bean" index="index" open="" close="" separator=";">

            UPDATE imb_spares_manage.SPARES_ECO_NEW_MANAGE_STOCK_T 

            <set>

                FIRST_CHECK_USERCODE=#{bean.usercode,jdbcType=VARCHAR} , FIRST_CHOOSE_STATUS = #{bean.status,jdbcType=TINYINT}

            </set>

            <where>

                 where  ID = #{bean.id,jdbcType=VARCHAR}   

            </where>

    </foreach>

</update>


尝试使用连接数据库的字符串中添加allowMultiQueries=true这条语句仍出现错误。

默认排序 时间排序
465
Pack
Pack

有两个错误吧，确实是语法错误，比如




<where>
                 where  ID = #{bean.id,jdbcType=VARCHAR}   
            </where>




where多了一个



另外一个list赋值的时候，最好和xml里面的名字一样

                                                        

                                                        

                                                            
                                                                                                                                                                                    

    

            

            

                请先 登录 后评论
            

        

    

                                                                                                                    

                                                    

                                                

                                            

                                                                            

                                

                                

                                    
                                

                            

                                                            


                                                                            
                                                                    

                                                    

                        

                            

                            

                            

                                

                                    

                                        
                                            

                                                Pack
                                            

                                        
                                    

                                    

                                                                                    
                                                                                1 关注
                                    

                                

                                

                                     
                                

                                

                                                                            Pack
                                                                        

                                        提出于 2020-02-27 15:54
                                    

                                

                            

                            

                                

                                    

                                        

                                            全部
                                        

                                        
Pack 的其他提问

                                    

                                

                            


                            

                                
类似问题