异常“check the manual that corresponds to your MySQL server version for the right syntax to use near ”

古城微笑少年丶 2022-06-14 23:10 180阅读 0赞

## 异常“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 ‘’ at line xxx”的解决方式 ##

#### 本人mybatis批量新增数据时候语法如下： ####

<insert id="createCostList" parameterType="costList" useGeneratedKeys="true" keyProperty="id">
    		<selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
    			SELECT LAST_INSERT_ID()
    		</selectKey>
    		insert into table_name (
    		<include refid="column_sql"></include>
    		)values
    		<foreach collection="list" item="item" index="index" separator=",">
    			(#{item.field1},
    			#{item.field2},
    			...
    			...
    			...)
    		</foreach>
    	</insert>

#### java代码如下： ####

List<CostList> list = new ArrayList<CostList>();
    	list.addAll(firstList);
    	list.addAll(secondList);
    	list.addAll(thirdList);
    
    	int result = costListDao.createCostList(list);

#### 但是一直报如下异常： ####

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 '' at line 25
    ### The error may involve com.company.erp.pricefix.cost.dao.CostListDao.createCostList-Inline
    ### The error occurred while setting parameters
    ### SQL: insert into table_name (       column1,   column2,   ...      )values
    ### 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 '' at line 25
    ; 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 '' at line 25
    	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
    	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 com.sun.proxy.$Proxy17.insert(Unknown Source)
    	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:237)
    	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:46)
    	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
    	at com.sun.proxy.$Proxy51.createCostList(Unknown Source)
    	at com.company.erp.pricefix.cost.service.impl.CostListServiceImpl.saveCostList(CostListServiceImpl.java:68)
    	at com.company.erp.pricefix.service.cost.CostListServiceTest.testSaveCostList(CostListServiceTest.java:47)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:606)
    	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
    	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
    	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
    	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
    	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
    	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$3.run(ParentRunner.java:231)
    	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
    	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
    	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
    	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
    	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    	at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
    	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
    	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
    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 '' at line 25
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    	at com.mysql.jdbc.Util.getInstance(Util.java:382)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)
    	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1362)
    	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:476)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:606)
    	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)
    	at com.sun.proxy.$Proxy79.execute(Unknown Source)
    	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:57)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:606)
    	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59)
    	at com.sun.proxy.$Proxy77.update(Unknown Source)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:606)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:355)
    	... 35 more

### 原因是：java代码中的list是个空集合，应添加判断该集合在不为空时候才执行批量新增的sql，即： ###

List<CostList> list = new ArrayList<CostList>();
    	list.addAll(firstList);
    	list.addAll(secondList);
    	list.addAll(thirdList);
    	
    	if (!CollectionUtils.isEmpty(list))
    	{ 
    		int result = costListDao.createCostList(list);
    	}

异常“check the manual that corresponds to your MySQL server version for the right syntax to use near ”

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 check the manual that corresponds to your MySQL server version for the right syntax to use near ‘key

相关 check the manual that corresponds to your MySQL server version for the right syntax to use完美解决

相关 Mysql错误:check the manual that corresponds to your MySQL server version for the right syntax

相关 check the manual that corresponds to your MySQL server version for the right syntax的解决方法

相关异常“check the manual that corresponds to your MySQL server version for the right syntax to use near ”

相关 check the manual that corresponds to your MySQL server version for the right syntax错误

相关 check the manual that corresponds to your MySQL server version for the right syntax to use near

相关修改mysql密码错误：check the manual that corresponds to your MySQL server version for the right syntax

相关 check the manual that corresponds to your MySQL server version for the right syntax错误

相关 check the manual that corresponds to your MySQL server version for the right syntax to use near