批量修改why?

批量新增修改删除

逐条插入修改删除百万数据，你看个综艺都结束不了，批量分分钟

不说了先建个表

CREATE TABLE `ua_addrorg_tyxx` (
  `ADDR_ORGANIZATION_ID` bigint(16) NOT NULL AUTO_INCREMENT COMMENT,
  `O` varchar(64) DEFAULT NULL COMMENT ,
  `PARENT_ORG_ID` varchar(1164) DEFAULT NULL COMMENT ,
  `DISPLAY_NAME` varchar(255) DEFAULT NULL COMMENT,
  `ORG_LEVEL` varchar(32) DEFAULT NULL COMMENT ,
  `DISPLAY_ORDER` varchar(32) DEFAULT NULL COMMENT,
  `STATUS` varchar(1) DEFAULT NULL COMMENT '0-正常  1-不可用',
  `DONE_DATE` datetime DEFAULT NULL,
  `REGION_ID` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`ADDR_ORGANIZATION_ID`),
  UNIQUE KEY `index_o` (`O`)
) ENGINE=InnoDB AUTO_INCREMENT=64074 DEFAULT CHARSET=utf8;

写个测试类

    public void  aoo(){
        List<UaAddrorgTyxx> addrorgTyxxes=new ArrayList<>();
        for (int i = 0; i < 4500; i++) {
            UaAddrorgTyxx uaAddrorgTyxx=new UaAddrorgTyxx();
            uaAddrorgTyxx.setO("o"+i);
            uaAddrorgTyxx.setParentOrgId("o"+String.valueOf(i-1));
            uaAddrorgTyxx.setDisplayName("displayName"+i);
            uaAddrorgTyxx.setOrgLevel("orgLevel"+i);
            uaAddrorgTyxx.setDisplayOrder("displayOrder"+i);
            uaAddrorgTyxx.setDoneDate(new Date());
            uaAddrorgTyxx.setRegionId("CM001");
            uaAddrorgTyxx.setStatus("0");
            addrorgTyxxes.add(uaAddrorgTyxx);
        }
      	//分片类，把一个list按大小分多个 牛逼
        List<List<UaAddrorgTyxx>> partition = ListUtils.partition(addrorgTyxxes, 3000);
        partition.stream().forEach(a->{
        int i = uaAddrorgTyxxMapper.insertUaAddrorgTyxxs(a);
        System.out.println(i);
        });
    }

新增，简简单单

  <insert id="insertUaAddrorgTyxxs" parameterType="com.cmcc.uniuser.inter.tyxx.dao.bo.UaAddrorgTyxx">
    insert into ua_addrorg_tyxx (O, PARENT_ORG_ID,
      DISPLAY_NAME, ORG_LEVEL, DISPLAY_ORDER,
      STATUS, DONE_DATE, REGION_ID
      )
    values
      <foreach collection="records" item="item" separator=",">
     (#{item.o,jdbcType=VARCHAR}, #{item.parentOrgId,jdbcType=VARCHAR},
      #{item.displayName,jdbcType=VARCHAR}, #{item.orgLevel,jdbcType=VARCHAR}, #{item.displayOrder,jdbcType=VARCHAR},
      #{item.status,jdbcType=VARCHAR}, #{item.doneDate,jdbcType=TIMESTAMP}, #{item.regionId,jdbcType=VARCHAR}
      )
      </foreach>
  </insert>

修改 恶，心

有其他方法留言学习呀

htm需要修改配置类 Mybatis映射文件中的sql语句默认是不支持以" ; " 结尾的，也就是不支持多条sql语句的执行。所以需要在连接mysql的url上加 &allowMultiQueries=true 这个才可以执行（复制的）。

生产让我加？逗我呢

<update id="updateUaAddrorgTyxxs" parameterType="com.cmcc.uniuser.inter.tyxx.dao.bo.UaAddrorgTyxx">
    <foreach collection="records" item="item" separator=";">
    update ua_addrorg_tyxx
      <set>

        <if test="item.parentOrgId != null">
          PARENT_ORG_ID = #{item.parentOrgId,jdbcType=VARCHAR},
        </if>
        <if test="item.displayName != null">
          DISPLAY_NAME = #{item.displayName,jdbcType=VARCHAR},
        </if>
        <if test="item.orgLevel != null">
          ORG_LEVEL = #{item.orgLevel,jdbcType=VARCHAR},
        </if>
        <if test="item.displayOrder != null">
          DISPLAY_ORDER = #{item.displayOrder,jdbcType=VARCHAR},
        </if>
        <if test="item.status != null">
          STATUS = #{item.status,jdbcType=VARCHAR},
        </if>
        <if test="item.doneDate != null">
          DONE_DATE = #{item.doneDate,jdbcType=TIMESTAMP},
        </if>
        <if test="item.regionId != null">
          REGION_ID = #{item.regionId,jdbcType=VARCHAR},
        </if>
        DONE_DATE =now()
      </set>
      <where>
         O =#{item.o,jdbcType=VARCHAR}
      </where>
    </foreach>
  </update>

牛逼的修改

给一个字段加上一个唯一索引

alter table ua_addrorg_tyxx add UNIQUE index_o(o);

  <insert id="replaceUaAddrorgTyxxs" parameterType="com.cmcc.uniuser.inter.tyxx.dao.bo.UaAddrorgTyxx">
    replace into ua_addrorg_tyxx (O, PARENT_ORG_ID,
    DISPLAY_NAME, ORG_LEVEL, DISPLAY_ORDER,
    STATUS, DONE_DATE, REGION_ID
    )
    values
    <foreach collection="records" item="item" separator=",">
      (#{item.o,jdbcType=VARCHAR}, #{item.parentOrgId,jdbcType=VARCHAR},
      #{item.displayName,jdbcType=VARCHAR}, #{item.orgLevel,jdbcType=VARCHAR}, #{item.displayOrder,jdbcType=VARCHAR},
      #{item.status,jdbcType=VARCHAR}, #{item.doneDate,jdbcType=TIMESTAMP}, #{item.regionId,jdbcType=VARCHAR}
      )
    </foreach>
  </insert>

有重复的o就修改，没有就插入

删除，简简单单

  <insert id="deleteUaAddrorgTyxxs" parameterType="string">
    delete FROM  ua_addrorg_tyxx
    where o in
    <foreach collection="records" item="item" separator="," open="(" close=")">
      #{item,jdbcType=VARCHAR}
    </foreach>
  </insert>

结束