MySQL 执行SQL:
INSERT DELAYED INTO SYS_JOB_WORK (JNL_NO, WORK_DT )
VALUES
(1, 'aas'),
(2, 'ffff');
Mybatis对应sql:
<insert id="insertList" parameterType="java.util.List" useGeneratedKeys="false">
INSERT INTO SYS_JOB_WORK(
JNL_NO,
WORK_DT
) VALUES
<foreach collection="jobWorks" item="jobWork" index="index" separator=",">
(
#{jobWork.jnlNo, jdbcType=VARCHAR},
#{jobWork.workDate, jdbcType=VARCHAR}
)
</foreach>
SELECT 1 FROM DUAL
</insert>
Oracle执行SQL:
INSERT ALL
INTO SYS_JOB_WORK( JNL_NO, WORK_DT ) VALUES ('1','2019-04-21')
INTO SYS_JOB_WORK( JNL_NO, WORK_DT ) VALUES ('2','2019-04-21')
INTO SYS_JOB_WORK( JNL_NO, WORK_DT ) VALUES ('3','2019-04-21')
SELECT 1 FROM DUAL;
Mybatis对应sql:
方案1 : 下面这种方式在数据大于500条后效率很低,用这种方式最好数据不要大于500条
<insert id="insertList" parameterType="java.util.List" useGeneratedKeys="false">
INSERT ALL
<foreach collection="jobWorks" item="jobWork" index="index">
INTO SYS_JOB_WORK(
JNL_NO,
WORK_DT
) VALUES (
#{jobWork.jnlNo, jdbcType=VARCHAR},
#{jobWork.workDate, jdbcType=VARCHAR}
)
</foreach>
SELECT 1 FROM DUAL
</insert>
方案 2 利用标签,将入参的list集合通过UNION ALL生成虚拟数据,从而实现批量插
<insert id="insertList" parameterType="java.util.List">
INSERT INTO SYS_JOB_WORK( JNL_NO, WORK_DT)
select A.* from (
<foreach collection="jobWorks" item="jobWork" index="index" separator="UNION ALL">
select
#{jobWork.jnlNo, jdbcType=VARCHAR},
#{jobWork.workDate, jdbcType=VARCHAR}
from dual
</foreach>) A
</insert>
方案3: 利用存储过程实现批量插入
<insert id="insertList" parameterType="java.util.List">
begin
<foreach collection="jobWorks" item="jobWork" index="index">
INSERT INTO SYS_JOB_WORK( JNL_NO, WORK_DT)
values (
#{jobWork.jnlNo, jdbcType=VARCHAR},
#{jobWork.workDate, jdbcType=VARCHAR}
);
</foreach>
end;
</insert>
<insert id="insertList" parameterType="java.util.List">
<foreach collection="jobWorks" item="jobWork" index="index" open="begin" close="end;" separator=";">
INSERT INTO SYS_JOB_WORK( JNL_NO, WORK_DT)
values (
#{jobWork.jnlNo, jdbcType=VARCHAR},
#{jobWork.workDate, jdbcType=VARCHAR}
)
</foreach>
</insert>