06-Mybatis-执行批量插入

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>