现有user表,userId为用户id,做为数据表user的主键:
由于userId不可以重复,而这里userId直接作为主键。为防止并发操作,插入语句可以这样设计:不存在userId则插入,否则更新:
<!--修改或更新-->
<insert id="insertOrUpdate" parameterType="com.iflytek.edu.zx.activitity.dto.ActivityUser">
insert into user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userId != null">
userId,
</if>
<if test="nickName != null">
nickName,
</if>
<if test="role != null">
role,
</if>
<if test="createTime != null">
createTime,
</if>
<if test="updateTime != null">
updateTime,
</if>
<if test="nickNameUpdateTime != null">
nickNameUpdateTime,
</if>
<if test="nickNameCreateTime != null">
nickNameCreateTime,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="userId != null">
#{userId,jdbcType=VARCHAR},
</if>
<if test="nickName != null">
#{nickName,jdbcType=VARCHAR},
</if>
<if test="role != null">
#{role,jdbcType=VARCHAR},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null">
#{updateTime,jdbcType=TIMESTAMP},
</if>
<if test="nickNameUpdateTime != null">
#{nickNameUpdateTime,jdbcType=TIMESTAMP},
</if>
<if test="nickNameCreateTime != null">
#{nickNameCreateTime,jdbcType=TIMESTAMP},
</if>
</trim>
ON DUPLICATE KEY UPDATE
updateTime=#{updateTime,jdbcType=TIMESTAMP}
<if test="nickName != null">
,nickName=#{nickName,jdbcType=VARCHAR}
</if>
<if test="role != null">
,role = #{role,jdbcType=VARCHAR}
</if>
<if test="nickNameUpdateTime != null">
,nickNameUpdateTime=#{nickNameUpdateTime,jdbcType=TIMESTAMP}
</if>
<if test="nickNameCreateTime != null">
,nickNameCreateTime = #{nickNameCreateTime,jdbcType=TIMESTAMP}
</if>
</insert>
on udplicate key update后的内容表示,主键存在时则执行更新操作,需要注意的是insert字段中需要含有唯一性字段(主键索引或唯一索引)。
再如:
<insert id="insertOnDuplicate" parameterType="XXXX">
insert into t_product (
id,
intention_code,
add_type,
product_id,
sbom_code,
sbom_name)
values (#{id},#{intentionCode,jdbcType=VARCHAR}, #{addType,jdbcType=INTEGER}, #{productId,jdbcType=BIGINT}, #{sbomCode,jdbcType=VARCHAR}, #{sbomName,jdbcType=VARCHAR}})
ON DUPLICATE KEY UPDATE
product_id = VALUES(product_id),
sbom_name = VALUES(sbom_name),
price = VALUES(price))
</insert>
多条记录仅仅是多了foreach 标签:
<insert id="insertByBatchOnDuplicate" parameterType="java.util.List">
insert into t_xxx (
id,
intention_code,
add_type,
product_id,
sbom_code,
sbom_name
)
values
<foreach collection="list" item="item" index="index" separator=",">
( #{item.id},#{item.intentionCode,jdbcType=VARCHAR},
#{item.addType,jdbcType=INTEGER},
#{item.productId,jdbcType=BIGINT},
#{item.sbomCode,jdbcType=VARCHAR},
#{item.sbomName,jdbcType=VARCHAR}
)
</foreach>
ON DUPLICATE KEY UPDATE
product_id = VALUES(product_id),
sbom_name = VALUES(sbom_name)
</insert>