示例mapper
public void insertBatchDlDevice(@Param("list") List<DlDevice> list);
映射文件中的 SQL 定义
在 MyBatis 映射文件中定义批量插入的 SQL 语句:
<insert id="insertBatchDlDevice" parameterType="com.cdtye.power.domain.DlDevice"> <foreach collection="list" item="item" separator=";"> INSERT INTO dl_device <trim prefix="(" suffix=")" suffixOverrides=","> <if test="item.id != null">id,</if> <if test="item.parentId != null">parent_id,</if> <if test="item.deviceClassId != null">device_class_id,</if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="item.id != null">#{item.id},</if> <if test="item.parentId != null">#{item.parentId},</if> <if test="item.deviceClassId != null">#{item.deviceClassId},</if> </trim> </foreach> </insert>
这里的关键点:allowMultiQueries=true 允许数据库直接批量操作,数据库默认是false
url: jdbc:mysql://172.26.1.110:3306/crscd_itps_prod?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true
批量更新:
public void updateBatchDlDevice(@Param("list") List<DlDevice> list);
<update id="updateBatchDlDevice" parameterType="com.cdtye.power.domain.DlDevice">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
UPDATE dl_device
<set>
<if test="item.parentId != null">parent_id = #{item.parentId},</if>
<if test="item.deviceClassId != null">device_class_id = #{item.deviceClassId},</if>
<if test="item.sotingId != null">soting_id = #{item.sotingId},</if>
<if test="item.sotingName != null">soting_name = #{item.sotingName},</if>
</set>
where `id` = #{item.id}
</foreach>
</update>