Mybatis 实现Mysql批量插入数据，判断数据是否存在

常见插入数据的SQL

• insert into 插入数据库时会检查主键是否存在，存在会报错
• replace into 替换数据库记录，需要表中有主键或者unique索引，如果数据库已存在的数据，会先删除该数据然后新增。不存在的数据效果和insert into 一样。

    <insert id="insertInfoBatch" parameterType="java.util.List">
      replace into GOVRECEIPTS (state,orgname,orgaddr,regauth,rcptyear,receipts,crtdate)
      values
      <foreach collection="list" item="item" index="index" separator=",">
        (#{item.state}, #{item.orgname}, #{item.orgaddr}, #{item.regauth},
        #{item.rcptyear}, #{item.receipts}, #{item.crtdate})
      </foreach>
    </insert>

  insert ignore 需要表中有主键或者unique索引，如果数据库中存在相同的数据，则忽略当前数据。不存在的数据效果和insert into 一样。

•   <insert id="insertInfoBatch" parameterType="java.util.List">
      insert ignore GOVRECEIPTS (state,orgname,orgaddr,regauth,rcptyear,receipts,crtdate)
      values
      <foreach collection="list" item="item" index="index" separator=",">
        (#{item.state}, #{item.orgname}, #{item.orgaddr}, #{item.regauth},
        #{item.rcptyear}, #{item.receipts}, #{item.crtdate})
      </foreach>

  on duplicate key update 使用该语法可在插入记录的时候先判断记录是否存在，如果不存在则插入，否则更新，很方便，无需执行两条SQL

注意：需要设置Mysql表的unique唯一索引值

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Mybatis 批量写入有则修改无则新增，同时判断空选择性写入字段

针对单行数据有则修改无则新增

本案例的建表语句是：

-- auto-generated definition
create table contact_type
(
	sid varchar(50) not null
		primary key,
	name varchar(50) default '' null,
	status int default 1 null comment '状态,默认1表示有效，0为冻结',
	seq float default 0 null,
	create_time datetime default CURRENT_TIMESTAMP null
)
comment '往来单位类型';

所以主键是字符串类型，而不是自增类型。写在 Mybatis 的 xml 文件中的SQL语句如下：

<insert id="saveOne" parameterType="com.ccsoft.femis.model.ContactType">
    <!--<selectKey resultType="java.lang.Integer" keyProperty="id" order="AFTER">-->
      <!--SELECT LAST_INSERT_ID()-->
    <!--</selectKey>-->

    insert into contact_type
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="create_time != null"> create_time, </if>
      <if test="name != null"> name, </if>
      <if test="seq != null"> seq, </if>
      <if test="sid != null"> sid, </if>
      <if test="status != null"> status, </if>
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="create_time != null"> #{create_time},</if>
      <if test="name != null"> #{name},</if>
      <if test="seq != null"> #{seq},</if>
      <if test="sid != null"> #{sid},</if>
      <if test="status != null"> #{status},</if>
    </trim>
    ON DUPLICATE KEY UPDATE
    <trim suffixOverrides=",">
      <if test="create_time != null"> create_time = #{create_time}, </if>
      <if test="name != null"> name = #{name}, </if>
      <if test="seq != null"> seq = #{seq}, </if>
      <if test="sid != null"> sid = #{sid}, </if>
      <if test="status != null"> status = #{status}, </if>
    </trim>
  </insert>

可以看到由于使用了 <if test=...> ，如果 Java 端传递来的对象有部分属性没有设置导致对象中该属性是空那么最终执行的 SQL 语句中就不会有该字段。 测试表创建的主键字段非自增，所以将上面 xml 中的头部 SQL 语句（返回新增行的主键字段值）给注释掉了，如果你的表示有自增字段的去掉注释，即可得到新增的行的自增字段数值。通过在 java 中测试，发现上面的语句新增一行成功后会返回1，修改成功后会返回2（这里有疑惑，如果清楚原因的麻烦跟帖科普下）。如果有未设置的属性恰好在数据库端对应的字段被设置为非空并且没有默认值导致新增或者修改失败那么会报异常 java.sql.SQLException 。

批量写入数据有则修改无则新增，同时判断空选择性写入字段

数据表还是上面的，直接贴出写在 Mybatis 的 XML 文件中的 SQL 是：

<insert id="saveBatch" parameterType="java.util.List">
    <!--<selectKey resultType="java.lang.String" keyProperty="sid" order="AFTER">-->
      <!--SELECT LAST_INSERT_ID()-->
    <!--</selectKey>-->

    <foreach collection ="list" item="ele" index= "index" separator =";">
      insert into contact_type
      <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="ele.create_time != null"> create_time, </if>
        <if test="ele.name != null"> name, </if>
        <if test="ele.seq != null"> seq, </if>
        <if test="ele.sid != null"> sid, </if>
        <if test="ele.status != null"> status, </if>
      </trim>
      values
      <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="ele.create_time != null"> #{ele.create_time},</if>
        <if test="ele.name != null"> #{ele.name},</if>
        <if test="ele.seq != null"> #{ele.seq},</if>
        <if test="ele.sid != null"> #{ele.sid},</if>
        <if test="ele.status != null"> #{ele.status},</if>
      </trim>
      ON DUPLICATE KEY UPDATE
      <trim suffixOverrides=",">
        <if test="ele.create_time != null"> create_time = #{ele.create_time}, </if>
        <if test="ele.name != null"> name = #{ele.name}, </if>
        <if test="ele.seq != null"> seq = #{ele.seq}, </if>
        <if test="ele.sid != null"> sid = #{ele.sid}, </if>
        <if test="ele.status != null"> status = #{ele.status}, </if>
      </trim>
    </foreach>
  </insert>

上面代码中在 SQL 语句的最外层使用了 for 循环，好处是将 List<ContactType> 类型的集合传递来写入数据时可以有的是新增有的是修改，例如3行数据，第一三行由于主键字段对应属性 sid 被设置为 NULL ，会向数据库中新增行，第二行数据设置了 sid ，并且该值在数据库中有对应行，那么会修改数据库中的该行上的数据。不过这种做法也有问题，就是返回给 Java 的数据永远都是1，因为每个对象构成的 SQL 语句间使用的间隔符号是 ; ，那么最终返回的影响的行数是最后一条 SQL 语句影响的行数。对此有其他见解的话麻烦跟帖科普下。

坑

由于上面的 SQL 在数据库端是多条语句，需要在 Java 连接数据库的字串中设置 &allowMultiQueries=true