mybatis使用错误记录

最新推荐文章于 2022-12-12 18:00:17 发布

凌晨4点de洛杉机

最新推荐文章于 2022-12-12 18:00:17 发布

阅读量290

点赞数

分类专栏：错误记录 java后端文章标签： java 后端

本文链接：https://blog.youkuaiyun.com/weixin_41841149/article/details/117035393

版权

错误记录同时被 2 个专栏收录

3 篇文章

订阅专栏

java后端

2 篇文章

订阅专栏

本文介绍了一个关于MyBatis批量插入数据时出现的SQL语法错误案例，并详细解释了如何通过正确设置分隔符来解决该问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mybatis使用错误记录

1 错误信息

mybatis foreach用法错误

<insert id="insertList" >
        insert into orders values
        <foreach collection="orders" item="o" >
            (null, #{o.gid}, #{o.create_time}, #{o.sale})
        </foreach>
    </insert>
</mapper>

Exception in thread "Thread-11" org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: 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 '(null, 7, '2021-05-19 16:41:02', 1)' at line 5
### The error may exist in file [E:\workSpace\idea_workspace\kill_test\target\classes\mapper\OrdersMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: insert into orders values                        (null, ?, ?, ?)                        (null, ?, ?, ?)
### Cause: java.sql.SQLSyntaxErrorException: 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 '(null, 7, '2021-05-19 16:41:02', 1)' at line 5
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 '(null, 7, '2021-05-19 16:41:02', 1)' at line 5
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy56.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
	at com.sun.proxy.$Proxy57.insertList(Unknown Source)
	at com.tx.kill_test.service.impl.ThreadDataSaveServiceImpl.threadDataBase(ThreadDataSaveServiceImpl.java:58)
	at com.tx.kill_test.service.impl.MiaoShaRedisServiceImpl.lambda$miaosha$0(MiaoShaRedisServiceImpl.java:86)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: 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 '(null, 7, '2021-05-19 16:41:02', 1)' at line 5
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
	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:427)
	... 9 more

定位错误：SQL: insert into orders values (null, ?, ?, ?) (null, ?, ?, ?)，即缺少逗号

2 错误纠正

mybatis 默认分隔符为空格，批量插入时缺少逗号，不符合语法。

<insert id="insertList" >
        insert into orders values
        <foreach collection="orders" item="o" separator=",">
            (null, #{o.gid}, #{o.create_time}, #{o.sale})
        </foreach>
    </insert>
</mapper>