oracle sql&mybatis 防止重复插入(实现唯一性约束)

1.如表ROLE如下所示：

表 ROLE

ID	ROLE_NAME
1	user
2	admin

字段ROLE_NAME在插入或者更新时需要避免数据重复，不选择在表上加unique约束是因为数据迁移可能因为unique约束造成问题，因此选择在sql层面实现。

2.oracle sql

①插入

INSERT INTO ROLE ("ROLE_NAME") SELECT 'user' FROM dual WHERE NOT EXISTS (select 1 from ROLE where ROLE_NAME = 'user');

②更新

update ROLE SET "ROLE_NAME"='user' WHERE not EXISTS (select 1 from ROLE where ROLE_NAME = 'user' AND ID <> 1) and ID = 1;

在sql中做某个字段唯一性校验时，最好给该字段建个索引提高效率。

3.mybatis

①插入

  <insert id="insertSelective" parameterType="com.portal.entity.Role" >
    insert into ROLE
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        ID,
      </if>
      <if test="roleName != null and roleName !=''" >
        ROLE_NAME,
      </if>
    </trim>
        select
    <trim suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=DECIMAL},
      </if>
      <if test="roleName != null and roleName !=''" >
        #{roleName,jdbcType=VARCHAR},
      </if>
    </trim> from dual
    where not exists
    (select 1 from ROLE where ROLE_NAME = #{roleName,jdbcType=VARCHAR})
  </insert>

②更新

<update id="update" parameterType="map">
        update "ROLE"
        <set>
            <if test="role.roleName != null and role.roleName !=''">
                ROLE_NAME=#{role.roleName},
            </if>
        </set>
        WHERE not EXISTS
        (select 1 from ROLE where ROLE_NAME = 'user' AND ID &lt; &gt; #{Id}) and ID = #{Id}
    </update>