MyBatis Batch Update Exception

Question

I am using mybatis to update mysql database,but when I run my test program I found a BadSqlGrammarException and I do not know how to solve it.Could anyone have a look at it and give me some useful advices?

Below is my issue detail:

Mybatis SQL file:

<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 method block:

@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;
}

* The last is my exception stacktrace:

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

Through the sql exception stacktrace,I could found it was due to my wrong syntax,and thus I have tried to my Mybatis XML block as below(remove seperator property and add a semicolon at the end of each sql),but still failed:

<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>

I have tried many ways to do it but still can not find a solution for it,could anyone help me to solve it?Thanks in advance!

score 4 · Answer 1 · answered Oct 20 '14 at 04:56

4

I think the problem is mysql doesn't support executing multi sqls by default, but in your case the batch update need that.So you have to add parameter "allowMultiQueries" to enable that. like below: jdbc:mysql://10.255.10.105:3306/PB_MANAGEMENT_PLATFORM?allowMultiQueries=true

answered Oct 20 '14 at 04:56

TonyArcher

91
7

what I want to do is not for support multiple query but for multiple updates. – flyingfox Oct 20 '14 at 09:17
@flyFox: Perhaps "Queries" in the name of the parameter is used in a wider sense, synonymous to "Statements". Have you tried this suggestion anyway? The link by the older, downvoted, answer also suggests using this parameter and illustrates it with an example where only one statement is a "query" in your sense, i.e. a SELECT statement. The other statement in the same batch is INSERT, and `allowMultiQueries` is shown as a solution to run the two in one go. – Andriy M Oct 21 '14 at 06:47
@flyFox: But in your case, if you want to use "batch update" via "foreach" in the *mapper.xml, you actually splice multi query and send it to mysql which doesn't support by default. What you send may like: 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=?; – TonyArcher Oct 21 '14 at 07:24

score 0 · Answer 2 · answered May 04 '15 at 04:38

The same error occurred while I used the multiple update on mybatis, then I appended the parameter allowMultiQueries=true to the URL of JDBC, and it works well now.

and are the item and index together?

<foreach collection="list" item="nodeVO" index="index" separator=";">

but the result of multiple update is 1 when I update a list of object , and the number of data changed in mysql equals the size of that list . It makes me confused .

score -1 · Answer 3 · edited May 23 '17 at 11:51

-1

See if this reference help?

Multiple queries executed in java in single statement

And if you are using Spring-Batch, this might comes in handy

<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactory" />
    <constructor-arg index="1" value="BATCH" />
</bean>

edited May 23 '17 at 11:51

Community

1
1

answered Apr 03 '14 at 20:01

Ian Lim

4,164
3
29
43

This is not what the question was about – jny Sep 12 '14 at 13:42

MyBatis Batch Update Exception

3 Answers3