mybatis批量插入数据，包括数据有null值

使用union all 来串连每个values,其中jdbcType的设置可以使null值也输入进去

<insert id="saveList" parameterType="java.util.List">
	INSERT INTO DDZHPT.CMS_SCHEDUAL_DETIAL
	(
		DEPT_ID,
		SCHEDUAL_DATE,
		CMS_SCHEDUAL_TYPE_ID,
		CMS_SCHEDUAL_TEAM_ID,
		CMS_SCHEDUAL_TYPE_PERIOD_ID,
		CMS_SCHEDUAL_TIME_ID,
		SYS_POST_ID,
		POINT_ID,
		PERSON_ID
   )
   <foreach collection="list" item="item" index="index" separator="union all">
   			SELECT
   			#{item.deptId,jdbcType=DECIMAL},
   			#{item.schedualDate,jdbcType=TIMESTAMP},
   			#{item.cmsSchedualTypeId,jdbcType=VARCHAR},
   			#{item.cmsSchedualTeamId,jdbcType=VARCHAR},
   			#{item.cmsSchedualTypePeriodId,jdbcType=VARCHAR},
   			#{item.cmsSchedualTimeId,jdbcType=VARCHAR},
   			#{item.sysPostId,jdbcType=VARCHAR},
   			#{item.pointId,jdbcType=VARCHAR},
   			#{item.personId,jdbcType=VARCHAR}
   			FROM DUAL
   </foreach>
</insert>

纯粹使用foreach

<insert id="saveList" parameterType="java.util.List">
	<foreach collection="list" item="item" index="index" separator="union all">
			INSERT INTO DDZHPT.CMS_SCHEDUAL_DETIAL
			(
				DEPT_ID,
				SCHEDUAL_DATE,
				CMS_SCHEDUAL_TYPE_ID,
				CMS_SCHEDUAL_TEAM_ID,
				CMS_SCHEDUAL_TYPE_PERIOD_ID,
				CMS_SCHEDUAL_TIME_ID,
				SYS_POST_ID,
				POINT_ID,
				PERSON_ID
		   )VALUES(
		   			#{item.deptId,jdbcType=DECIMAL},
		   			#{item.schedualDate,jdbcType=TIMESTAMP},
		   			#{item.cmsSchedualTypeId,jdbcType=VARCHAR},
		   			#{item.cmsSchedualTeamId,jdbcType=VARCHAR},
		   			#{item.cmsSchedualTypePeriodId,jdbcType=VARCHAR},
		   			#{item.cmsSchedualTimeId,jdbcType=VARCHAR},
		   			#{item.sysPostId,jdbcType=VARCHAR},
		   			#{item.pointId,jdbcType=VARCHAR},
		   			#{item.personId,jdbcType=VARCHAR}
		   			)
	</foreach>
</insert>

纯粹使用foreach

<insert id="insertBatch">
    INSERT INTO t_user
            (id, name, del_flag)
    VALUES
    <foreach collection ="list" item="user" separator =",">
         (#{user.id,jdbcType=DECIMAL}, #{user.name,jdbcType=VARCHAR}, #{user.delFlag,jdbcType=VARCHAR})
    </foreach >
</insert>

特别注意：mysql默认接受sql的大小是1048576(1M)，即第三种方式若数据量超过1M会报如下异常：（可通过调整MySQL安装目录下的my.ini文件中[mysqld]段的＂max_allowed_packet = 1M＂）

nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576).

You can change this value on the server by setting the max_allowed_packet’ variable.

个人一般使用第三种方法.

文章参考:坏坏的蛋,小勇DW3