mysql存在则更新,不存在则插入
单条数据
<insert id="insertUpdate" parameterType="com.hx.electric.iot.plugin.types.dataObj.DeviceDo">
insert
into el_device (id, name, identifier, lastSynchronizeTime, productId, secretKey, synchronizeStatus, description,
updateTime, type, parentId, enabledState, createTime, typeValue)
values (#{dataObj.id}, #{dataObj.name}, #{dataObj.identifier}, #{dataObj.lastSynchronizeTime},
#{dataObj.productId}, #{dataObj.secretKey}, #{dataObj.synchronizeStatus}, #{dataObj.description},
#{dataObj.updateTime}, #{dataObj.type}, #{dataObj.parentId},
#{dataObj.enabledState}, #{dataObj.createTime}, #{dataObj.typeValue})
on duplicate key
update name = #{dataObj.name},
identifier = #{dataObj.identifier},
lastSynchronizeTime = #{dataObj.lastSynchronizeTime},
productId = #{dataObj.productId},
secretKey = #{dataObj.secretKey},
description = #{dataObj.description},
updateTime = #{dataObj.updateTime},
parentId = #{dataObj.parentId},
enabledState = #{dataObj.enabledState}
</insert>
批量,且忽略插入错误的记录
ignore是忽略错误的记录
<insert id="insertUpdateBatch" parameterType="java.util.List">
insert ignore
into el_device (id, name, identifier, lastSynchronizeTime, productId, secretKey, synchronizeStatus, description,
updateTime, type, parentId, enabledState, createTime, typeValue)
values
<foreach collection="dos" item="item" separator=",">
(#{item.id}, #{item.name}, #{item.identifier}, #{item.lastSynchronizeTime},
#{item.productId}, #{item.secretKey}, #{item.synchronizeStatus}, #{item.description},
#{item.updateTime}, #{item.type}, #{item.parentId},
#{item.enabledState}, #{item.createTime}, #{item.typeValue})
</foreach>
on duplicate key
update name = values(name),
identifier = values(identifier),
lastSynchronizeTime = values(lastSynchronizeTime),
productId = values(productId),
secretKey = values(secretKey),
description = values(description),
updateTime = values(updateTime),
parentId = values(parentId),
enabledState = values(enabledState)
</insert>