最近在项目中需要使用oracle+mybatis批量插入数据,因为自增主键,遇到问题,现记录如下:
一、常用的两种sql写法报错
1、insert ... values ...
1 <insert id="batchInsert1" parameterType="java.util.List" useGeneratedKeys="false"> 2 insert all 3 <foreach collection="list" item="item" index="index"> 4 into TableName(id, name) values(TableName_sequence.nextval, #{item.name}) 5 </foreach> 6 <!-- 必须加下面的查询 --> 7 SELECT 1 FROM DUAL 8 </insert>
报错结果:java.sql.SQLException: ORA-00001: 违反唯一约束条件...
2、insert ... select ...
1 <insert id="batchInsert2" parameterType="java.util.List" useGeneratedKeys="false"> 2 insert into TableName(id, name) 3 <foreach collection="list" item="item" separator="union all"> 4 select tableName_sequence.nextval, #{item.name} from dual 5 </foreach> 6 </insert>
报错结果:java.sql.SQLException: ORA-02287: 此处不允许序号
二、解决办法:
1、使用函数包装序列
1 create or replace function table_getSeq return number is 2 Result number; 3 begin 4 select TableName_sequence.nextval into Result from dual; 5 return(Result); 6 end table_getSeq ;
将两种sql中的“tableName_sequence.nextval”替换为函数名“table_getSeq”
2、使用触发器
1 create or replace trigger table_insert 2 before insert on tableName 3 for each row 4 begin 5 select TableName_sequence.nextval into :new.id from dual; 6 end;
将两种sql中关于id的字段去掉即可