oracle在mybatis中批量插入数据报错java.sql.SQLException: ORA-00933: SQL 命令未正确结束

在MyBatis中使用Oracle数据库进行批量数据插入时,需显式设置useGeneratedKeys=false来避免SQL命令未正确结束的错误。本文介绍如何正确配置MyBatis的XML映射文件。

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

useGeneratedKeys属性默认值为false,但是oracle在mybatis中进行批量插入数据时必须显式的定义useGeneratedKeys="false",否则会报错。
useGeneratedKeys作用是设置是否使用JDBC的getGenereatedKeys方法获取主键并赋值到keyProperty设置的领域模型属性中(自动将自增长值赋予由keyProperty设置的指定属性)

原报错SQL语句:

<insert id="insertList" parameterType="com.zjhcsoft.monitor.fault.bean.UserFaultAnalyse">
        INSERT ALL
        <foreach collection="list" item="item" index="index" >
            INTO USERFAULTANALYSE(FAULTDATE,AREA,NOONCOUNT,NIGHTCOUNT,ALLCOUNT)
            VALUES (#{item.faultDate},#{item.area},#{item.noonCount},#{item.nightCount},#{item.allCount})
        </foreach>
        SELECT 1 FROM DUAL
</insert>

错误:

Caused by: java.sql.SQLException: ORA-00933: SQL 命令未正确结束

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3422)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
	at sun.reflect.GeneratedMethodAccessor117.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:58)
	at com.sun.proxy.$Proxy109.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:108)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:134)
	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:355)
	... 49 more

修改后SQL语句:

<insert id="insertList" parameterType="com.zjhcsoft.monitor.fault.bean.UserFaultAnalyse" useGeneratedKeys="false">
        INSERT ALL
        <foreach collection="list" item="item" index="index" >
            INTO USERFAULTANALYSE(FAULTDATE,AREA,NOONCOUNT,NIGHTCOUNT,ALLCOUNT)
            VALUES (#{item.faultDate},#{item.area},#{item.noonCount},#{item.nightCount},#{item.allCount})
        </foreach>
        SELECT 1 FROM DUAL
</insert>

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值