mysql 实现insert update 功能

insert update 实现

<insert id="insertOrUpdateSelective" keyColumn="id" keyProperty="id" parameterType="com.xxx.xxx.UserDO" useGeneratedKeys="true">
INSERT INTO user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="name != null">
name,
</if>
<if test="accountId != null">
account_id,
</if>
<if test="gmtCreate != null">
gmt_create,
</if>
<if test="gmtModified != null">
gmt_modified,
</if>
<if test="isDelete != null">
is_delete,
</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id},
</if>
<if test="name != null">
#{name},
</if>
<if test="accountId != null">
#{accountId},
</if>
<if test="gmtCreate != null">
#{gmtCreate},
</if>
<if test="gmtModified != null">
#{gmtModified},
</if>
<if test="isDelete != null">
#{isDelete},
</if>
</trim>
ON duplicate key update
<trim suffixOverrides=",">
<if test="id != null">
id =#{id},
</if>
<if test="name != null">
name =#{name},
</if>
<if test="accountId != null">
account_id = #{accountId},
</if>
<if test="gmtCreate != null">
gmt_create = #{gmtCreate},
</if>
<if test="gmtModified != null">
gmt_modified = #{gmtModified},
</if>
<if test="isDelete != null">
is_delete = #{isDelete},
</if>
</trim>
</insert>

 

insert 注解

@Insert ({”insert into sys_role (role_name, enabled, create_by, create_ time )”, 
” values(#{roleName}, #{enabled}, #{createBy },”, 
”#{ createTime, jdbcType=TIMESTAMP })”}) 
@Options(useGeneratedKeys =true, keyProperty =”id”) 
int insert2(SysRole sysRole);

 

返回非自增主键

@Insert ({”insert into sys_role {role_name, enabled, create_by, create time ) ”, 
” values {#{roleName} , #{enabled}, #{createBy },”, 
”#{ createTime , jdbcType= TIMESTAMP }) ” }} 
@SelectKey{statement =”SELECT LAST INSERT ID {)”, 
keyProperty =”id”, 
resultType = Long.class, 
before = false) 
int insert3{SysRole sysRole); 
使用＠ SelectKey 注解，以下代码是前面 XML 中配置的 selectKeyo
<selectKey keyColumn=”id” resultType=”long” keyProperty=”id” order=”AFTER”>
SELECT LAST INSERT ID {) 
</selectKey>