网上查阅了其他的文章,均报错SQL错误,试了一下只需要简单的修改即可,直接上代码:
<insert id="insertOrUpdateListSelective" parameterType="java.util.List">
<foreach collection="deviceList" item="item" index="index" open="" close="" separator=";">
insert into device
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="item.id != null">
id,
</if>
<if test="item.deviceid != null">
deviceid,
</if>
<if test="item.deviceName != null">
device_name,
</if>
<if test="item.dtId != null">
dt_id,
</if>
<if test="item.xmId != null">
xm_id,
</if>
<if test="item.caId != null">
ca_id,
</if>
<if test="item.fId != null">
f_id,
</if>
<if test="item.cpId != null">
cp_id,
</if>
<if test="item.grId != null">
gr_id,
</if>
<if test="item.status != null">
status,
</if>
<if test="item.realTimeUuid != null">
real_time_uuid,
</if>
<if test="item.createTime != null">
create_time,
</if>
<if test="item.updateTime != null">
update_time,
</if>
<if test="item.deviceComment != null">
device_comment,
</if>
<if test="item.deviceComment1 != null">
device_comment1,
</if>
<if test="item.deviceComment2 != null">
device_comment2,
</if>
<if test="item.deviceComment3 != null">
device_comment3,
</if>
<if test="item.deviceComment4 != null">
device_comment4,
</if>
<if test="item.deviceComment5 != null">
device_comment5,
</if>
<if test="item.deviceComment6 != null">
device_comment6,
</if>
<if test="item.deviceComment7 != null">
device_comment7,
</if>
<if test="item.deviceComment8 != null">
device_comment8,
</if>
<if test="item.deviceComment9 != null">
device_comment9,
</if>
<if test="item.deviceComment10 != null">
device_comment10,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="item.id != null">
#{item.id,jdbcType=INTEGER},
</if>
<if test="item.deviceid != null">
#{item.deviceid,jdbcType=VARCHAR},
</if>
<if test="item.deviceName != null">
#{item.deviceName,jdbcType=VARCHAR},
</if>
<if test="item.dtId != null">
#{item.dtId,jdbcType=INTEGER},
</if>
<if test="item.xmId != null">
#{item.xmId,jdbcType=INTEGER},
</if>
<if test="item.caId != null">
#{item.caId,jdbcType=INTEGER},
</if>
<if test="item.fId != null">
#{item.fId,jdbcType=INTEGER},
</if>
<if test="item.cpId != null">
#{item.cpId,jdbcType=INTEGER},
</if>
<if test="item.grId != null">
#{item.grId,jdbcType=INTEGER},
</if>
<if test="item.status != null">
#{item.status,jdbcType=INTEGER},
</if>
<if test="item.realTimeUuid != null">
#{item.realTimeUuid,jdbcType=VARCHAR},
</if>
<!-- <if test="createTime != null">-->
<!-- #{createTime,jdbcType=TIMESTAMP},-->
<!-- </if>-->
<!-- <if test="updateTime != null">-->
<!-- #{updateTime,jdbcType=TIMESTAMP},-->
<!-- </if>-->
<if test="item.deviceComment != null">
#{item.deviceComment,jdbcType=VARCHAR},
</if>
<if test="item.deviceComment1 != null">
#{item.deviceComment1,jdbcType=VARCHAR},
</if>
<if test="item.deviceComment2 != null">
#{item.deviceComment2,jdbcType=VARCHAR},
</if>
<if test="item.deviceComment3 != null">
#{item.deviceComment3,jdbcType=VARCHAR},
</if>
<if test="item.deviceComment4 != null">
#{item.deviceComment4,jdbcType=VARCHAR},
</if>
<if test="item.deviceComment5 != null">
#{item.deviceComment5,jdbcType=VARCHAR},
</if>
<if test="item.deviceComment6 != null">
#{item.deviceComment6,jdbcType=VARCHAR},
</if>
<if test="item.deviceComment7 != null">
#{item.deviceComment7,jdbcType=VARCHAR},
</if>
<if test="item.deviceComment8 != null">
#{item.deviceComment8,jdbcType=VARCHAR},
</if>
<if test="item.deviceComment9 != null">
#{item.deviceComment9,jdbcType=VARCHAR},
</if>
<if test="item.deviceComment10 != null">
#{item.deviceComment10,jdbcType=VARCHAR},
</if>
</trim>
<!-- 区别在于这里,只需要将判断重复主键方法写在<foreach>标签内即可实现 -->
ON DUPLICATE KEY UPDATE
device_name = VALUES(device_name),
dt_id = VALUES(dt_id),
xm_id = VALUES(xm_id),
gr_id = VALUES(gr_id),
status = VALUES(status),
real_time_uuid = VALUES(real_time_uuid),
device_comment = VALUES(device_comment),
device_comment1 = VALUES(device_comment1),
device_comment2 = VALUES(device_comment2),
device_comment3 = VALUES(device_comment3),
device_comment4 = VALUES(device_comment4),
device_comment5 = VALUES(device_comment5),
device_comment6 = VALUES(device_comment6),
device_comment7 = VALUES(device_comment7),
device_comment8 = VALUES(device_comment8),
device_comment9 = VALUES(device_comment9),
device_comment10 = VALUES(device_comment10)
<!-- 区别结束 -->
</foreach>
<!-- 原判断写在这里,报错! -->
</insert>