MyBatis的Oracle批量添加报错记录

Climbing-pit

已于 2023-03-08 16:13:17 修改

阅读量740

点赞数

文章标签： mybatis java

于 2023-02-21 16:06:09 首次发布

本文链接：https://blog.csdn.net/qq_37507261/article/details/129144545

版权

2023-02-21 14:25:00.202 [pool-1-thread-1] ERROR o.s.s.s.TaskUtils$LoggingErrorHandler - [handleError,95] - Unexpected error occurred in scheduled task
org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

### The error may exist in file [E:\work\wtonew\jm\wto-jm-web\jm-dao\target\classes\mappers\VisitsCountNewMapper.xml]
### The error may involve com.wto.jm.dao.VisitsCountNewMapper.insertBatch-Inline
### The error occurred while setting parameters
### SQL: insert into VISITSCOUNTNEW (ID, VISITIP, USERID,         ICOUNT, TYPE, IYEAR,         IMONTH, IDAY, ITIME         )         values                        (SEQ_VISITSCOUNTNEW.nextval, ?, ?,             ?, ?, ?,             ?, ?, ?             )          ,              (SEQ_VISITSCOUNTNEW.nextval, ?, ?,             ?, ?, ?,             ?, ?, ?             )
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy97.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.$Proxy98.insertBatch(Unknown Source)
	at com.wto.jm.visitscountnew.impl.VisitisCountNewServiceImpl.addBatch(VisitisCountNewServiceImpl.java:39)
	at com.wto.jm.code.task.VisitisScheduleTask.saveVisitisTask(VisitisScheduleTask.java:110)
	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.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
	at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
	at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:95)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:266)
	at java.util.concurrent.FutureTask.run(FutureTask.java)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
	at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at sun.reflect.GeneratedMethodAccessor334.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy186.execute(Unknown Source)
	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)
	... 23 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00933: SQL 命令未正确结束

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
	... 55 common frames omitted

以上是错误信息

由于SQL语法造成的原因，所以修正SQL写法

MySQL写法：

        insert into VISITSCOUNTNEW (ID, VISITIP, USERID,
        ICOUNT, TYPE, IYEAR,
        IMONTH, IDAY, ITIME
        )
        values
        <foreach collection="visitsCountNewList" item="item" separator=",">
            (SEQ_VISITSCOUNTNEW.nextval, #{item.visitip,jdbcType=VARCHAR}, #{item.userid,jdbcType=VARCHAR},
            #{item.icount,jdbcType=VARCHAR}, #{item.type,jdbcType=VARCHAR}, #{item.iyear,jdbcType=VARCHAR},
            #{item.imonth,jdbcType=VARCHAR}, #{item.iday,jdbcType=VARCHAR}, #{item.itime,jdbcType=TIMESTAMP}
            )
        </foreach>

Oracle写法：

        insert into VISITSCOUNTNEW (ID, VISITIP, USERID,
        ICOUNT, TYPE, IYEAR,
        IMONTH, IDAY, ITIME
        )
        <foreach collection="visitsCountNewList" item="item" separator="union all">
            select SEQ_VISITSCOUNTNEW.nextval, #{item.visitip,jdbcType=VARCHAR}, #{item.userid,jdbcType=VARCHAR},
            #{item.icount,jdbcType=VARCHAR}, #{item.type,jdbcType=VARCHAR}, #{item.iyear,jdbcType=VARCHAR},
            #{item.imonth,jdbcType=VARCHAR}, #{item.iday,jdbcType=VARCHAR}, #{item.itime,jdbcType=TIMESTAMP}
            from dual
        </foreach>

Oracle的这种写法后续出现了异常【此处不允许序号】，详见MyBatis的Oracle批量添加报错记录（2）_Climbing-pit的博客-CSDN博客