batch-file之MyBatis 批量更新异常

sanshao 2024年06月20日编程语言 22 0

我正在使用 mybatis更新 mysql数据库，但是当我运行我的测试程序时，我发现了一个 BadSqlGrammarException 我不知道如何解决它。谁能看看它并给我一些有用的建议？

以下是我的问题详细信息:

Mybatis SQL 文件:

<update id="updateTestcaseNodeBatch" parameterType="List"> 
  <foreach collection="list" item="nodeVO" separator=";"> 
    UPDATE testcase_node 
     <set> 
       name=#{nodeVO.name}, 
       version=#{nodeVO.version}, 
       description=#{nodeVO.description}, 
       last_modify_user=#{nodeVO.createUser}, 
       last_modify_time=#{nodeVO.createTime} 
     </set> 
     <where> 
       object_id=#{nodeVO.objectId} AND root_id=#{nodeVO.rootId} 
     </where> 
  </foreach> 
</update>

Java 方法块:

@Override 
public int[] parseImportTestcaseData(List<TestcaseNodeVO> nodeList) { 
 
 
            int[] result=new int[3]; 
            int ignoreNum=0; 
            List<TestcaseNodeVO> addList=new ArrayList<TestcaseNodeVO>(); 
            List<TestcaseNodeVO> updateList=new ArrayList<TestcaseNodeVO>(); 
 
            TestcaseNodeModel tempNode=null; 
            for(TestcaseNodeVO nodeVO:nodeList){ 
                tempNode=testcaseNodeDao.queryNodeByObjectId(nodeVO.getObjectId(),nodeVO.getRootId()); 
 
                if(tempNode==null){ 
                    addList.add(nodeVO); 
                }else{ 
                    if(tempNode.getVersion()<nodeVO.getVersion()){ 
                        updateList.add(nodeVO); 
                    }else{ 
                        ignoreNum++; 
                    } 
                } 
 
                tempNode=null; 
            } 
 
            if(addList.size()>0){ 
                testcaseNodeDao.addTestcaseNodeBatch(addList);   
            } 
            if(updateList.size()>0){ 
                testcaseNodeDao.updateTestcaseNodeBatch(updateList); 
            } 
 
 
            result[0]=addList.size(); 
            result[1]=updateList.size(); 
            result[2]=ignoreNum; 
 
            return result; 
}

* 最后是我的异常堆栈跟踪:

org.springframework.jdbc.BadSqlGrammarException:  
### Error updating database.  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 ';  
    UPDATE testcase_node 
      SET name='TÃ¼rstatus', 
       version=4, 
     ' at line 8 
### The error may involve com.hirain.testmanagement.mapper.TestcaseNodeMapper.updateTestcaseNodeBatch-Inline 
### The error occurred while setting parameters 
### SQL: UPDATE testcase_node       SET name=?,        version=?,        description=?,        last_modify_user=?,        last_modify_time=?        WHERE object_id=? AND root_id=?     ;      UPDATE testcase_node       SET name=?,        version=?,        description=?,        last_modify_user=?,        last_modify_time=?        WHERE object_id=? AND root_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 ';  
    UPDATE testcase_node 
      SET name='TÃ¼rstatus', 
       version=4, 
     ' at line 8 
; 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 ';  
    UPDATE testcase_node 
      SET name='TÃ¼rstatus', 
       version=4, 
     ' at line 8 
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233) 
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) 
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71) 
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:365) 
    at $Proxy17.update(Unknown Source) 
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:251) 
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82) 
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40) 
    at $Proxy27.updateTestcaseNodeBatch(Unknown Source) 
    at com.hirain.testmanagement.dao.impl.TestcaseNodeDaoImpl.updateTestcaseNodeBatch(TestcaseNodeDaoImpl.java:63) 
    at com.hirain.testmanagement.service.impl.TestcaseNodeServiceImpl.parseImportTestcaseData(TestcaseNodeServiceImpl.java:587) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) 
    at java.lang.reflect.Method.invoke(Method.java:597) 
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) 
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) 
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) 
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) 
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) 
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91) 
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) 
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) 
    at $Proxy59.parseImportTestcaseData(Unknown Source) 
    at com.hirain.testmanagement.service.test.TestcaseNodeServiceTest.testImportDoorsXML(TestcaseNodeServiceTest.java:28) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) 
    at java.lang.reflect.Method.invoke(Method.java:597) 
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44) 
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) 
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41) 
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) 
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) 
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74) 
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31) 
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83) 
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72) 
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231) 
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88) 
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:180) 
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:41) 
    at org.junit.runners.ParentRunner$1.evaluate(ParentRunner.java:173) 
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) 
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) 
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31) 
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71) 
    at org.junit.runners.ParentRunner.run(ParentRunner.java:220) 
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174) 
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46) 
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) 
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) 
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) 
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390) 
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) 
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 ';  
    UPDATE testcase_node 
      SET name='TÃ¼rstatus', 
       version=4, 
     ' at line 8 
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) 
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) 
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) 
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513) 
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) 
    at com.mysql.jdbc.Util.getInstance(Util.java:384) 
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) 
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562) 
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494) 
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960) 
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114) 
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696) 
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105) 
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1367) 
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41) 
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) 
    at java.lang.reflect.Method.invoke(Method.java:597) 
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59) 
    at $Proxy77.update(Unknown Source) 
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45) 
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:108) 
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75) 
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) 
    at java.lang.reflect.Method.invoke(Method.java:597) 
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:355) 
    ... 51 more

通过sql异常堆栈跟踪，我发现这是由于我的语法错误，因此我尝试了我的Mybatis XML块如下(删除分隔符属性并在每个sql的末尾添加一个分号)，但仍然失败:

<update id="updateTestcaseNodeBatch" parameterType="List"> 
      <foreach collection="list" item="nodeVO"> 
        UPDATE testcase_node 
         <set> 
           name=#{nodeVO.name}, 
           version=#{nodeVO.version}, 
           description=#{nodeVO.description}, 
           last_modify_user=#{nodeVO.createUser}, 
           last_modify_time=#{nodeVO.createTime} 
         </set> 
         <where> 
           object_id=#{nodeVO.objectId} AND root_id=#{nodeVO.rootId}; 
         </where> 
      </foreach> 
</update>

我已经尝试了很多方法来做到这一点，但仍然找不到解决方案，有人能帮我解决吗？提前致谢!

请您参考如下方法:

我认为问题是 mysql 默认不支持执行多条 sql，但在您的情况下，批量更新需要这样做。所以您必须添加
参数“ allowMultiQueries ”来启用它。
像下面这样:
jdbc:mysql://10.255.10.105:3306/PB_MANAGEMENT_PLATFORM?allowMultiQueries=true

IT序号网 MyBatis

微信公众号号：IT虾米（左侧二维码扫一扫）欢迎添加！

Q : 823053937

热贴推荐

batch-file之MyBatis 批量更新异常

Q : 823053937

热贴推荐

batch-file之MyBatis 批量更新异常

ibatis之在 MyBatis 中通过参数设置 FROM 子句

sql-server之在 MyBatis 中调用带有更新和结果集的过程

oracle之Mybatis BindingException 参数 '__frch_e_0' 未找到

MyBatis 第二个嵌套列表返回 null

mybatis之MyBatis Insert Query中如何手动插入null

spring之Spring 4.x 是否支持 Mybatis

mybatis之myBatis 中 ArrayList 的类型处理程序

mybatis foreach 但提交单独的插入

automation之是否可以使用 MyBatis Generator 一次为所有表生成工件

c#之mybatis .NET 可以直接和Oracle ODP.NET 客户端一起使用吗

java之Mybatis迁移工具在一个事务中运行多条mysql语句

java之MyBatis之与 MySQL 的连接随机断开