MyBatis Batch Update Exception

instl 2015-09-09

展开全文

MyBatis Batch Update Exception

up vote 3 down vote favorite

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!

share|improve this question

edited Sep 14 '14 at 10:14

asked Apr 3 '14 at 6:29

lucumt
110210

add a comment

3 Answers

active oldest votes

up vote 2 down vote

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

share|improve this answer

answered Oct 20 '14 at 4:56

TonyArcher
365

what I want to do is not for support multiple query but for multiple updates. – lucumt Oct 20 '14 at 9: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 6: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 7:24

Thx that solved the issue for me. Upvoting. – isaac.hazan Mar 2 at 12:14

add a comment