mybatis批量insert 和 update 例子

批量更新：

<update id="updateExcel" parameterType="com.module.system.pojo.XXX">
		UPDATE *** 
		SET
			DATE =
			<foreach collection="updateList" index="index" separator=" " item="list" open="case ID" close="end">  
				when  #{list.id} then #{list.date}
			</foreach>
			,DATA_TIME =
			<foreach collection="updateList" index="index" separator=" " item="list" open="case ID" close="end">  
				when  #{list.id} then #{list.dataTime}
			</foreach>
			,COMPANY =
			<foreach collection="updateList" index="index" separator=" " item="list" open="case ID" close="end">  
				when  #{list.id} then #{list.company}
			</foreach>
			,DELETED =
			<foreach collection="updateList" index="index" separator=" " item="list" open="case ID" close="end">  
				when  #{list.id} then #{list.deleted}
			</foreach>
		WHERE ID in 
			<foreach collection="updateList" index="index" separator="," item="list" open="(" close=")">  
				  #{list.id}
			</foreach>
	</update>

批量插入：

<insert id="addExcelSeat" parameterType="com.module.system.pojo.XXX">
		INSERT INTO CBD_O2O_SEAT_CHECKING(
			ID,
			DATE,
			DATA_TIME,
			COMPANY,
			DELETED
		)VALUES
		<foreach collection ="insertList" item="list" separator =",">
			(
				#{list.id},
				#{list.Date},
				#{list.dataTime},
				#{list.company},
				#{list.deleted}
			)
		</foreach>
	</insert>

其中when...then...是sql中的"switch" 语法。这里借助mybatis的<foreach>语法来拼凑成了批量更新的sql，上面的意思就是批量更新id在updateBatch参数所传递List中的数据的status字段。如果当需要为某个字段设置默认值的时候可以使用else value

MySQL 有sql语句长度限制，大小不能超过制定值。可以通过修改配置文件，或分批次处理SQL