ORACLE使用mybatis-plus自带的iservice.saveBatch方法时,会报DML Returing cannot be batch错误:
推测原因是oracle不支持insert into table_name (,) values (,),()的写法。且oracle不会自动生成自增ID。
解决方案:
1)创建oracle序列号,用于生成自增主键
CREATE SEQUENCE SYS_ROLE_PERMISSION_SEQ INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 NOCYCLE CACHE 20 NOORDER
2)创建触发器,在数据插入时,自动增加主键
CREATE OR REPLACE trigger ID_INCREAMENT
before insert on SYS_USER_ROLE
for each row
begin
select "SYS_USER_ROLE_SEQ"."NEXTVAL" into :new.id from dual;
end;
3)手动定义mapper,批量插入
<insert id="batchInsert" parameterType="java.util.List">
INSERT ALL
<foreach collection="sysUserRoles" item = "item" separator=" "
close="SELECT * FROM dual" index="index">
INTO SYS_USER_ROLE (USER_ID, ROLE_ID) values (#{item.userId}, #{item.roleId})
</foreach>
</insert>