myBatis批量插入和批量更新

一.批量插入

1.oracle

 <insert id="batchInsert" parameterType="com.chinasofti.domain.ROrgPerson">
    insert into ro_orgperson
    (personuuid,orguuid,isbelong)
    SELECT t.* FROM (
    <foreach collection="list" item="item" index="index" separator="UNION ALL">
      select
      #{item.personuuid,jdbcType=VARCHAR},#{item.orguuid,jdbcType=VARCHAR}, #    
      {item.isbelong}
      from dual
    </foreach>
    ) t
  </insert>

2.mySql

<insert id="batchInsert" parameterType="com.icss.module.entity.DamsScore">
		INSERT INTO dams_score (id,
		dam_name,dam_code,shi,xian,areacode,period,plan_score,policy_score_shi,policy_score_xian,fund_score,subside_score,trans_score,invest_score,train_score,newsub_score,lead_score,total_score,create_time,update_time,level_star)
		values
		<foreach collection="list" item="score" index="index" separator=",">
			(
			#{score.id},#{score.damName},#{score.damCode},#{score.shi},#{score.xian},#{score.areacode},#{score.period},#{score.planScore},#{score.policyScoreShi},
			#{score.policyScoreXian},#{score.fundScore},#{score.subsideScore},#{score.transScore},#{score.investScore},#{score.trainScore},#{score.newsubScore},
			#{score.leadScore},#{score.totalScore},#{score.createTime},#{score.updateTime},#{score.levelStar}
			)
		</foreach>
	</insert>

二.批量更新

1.oracle

<sql id="orSql">
    WHERE
    <foreach collection="list" item="item" index="index" separator="or" >
      personuuid = #{item.personuuid,jdbcType=VARCHAR}
    </foreach>
  </sql>

  <update id="batchUpdate" parameterType="com.chinasofti.domain.ROrgPerson">
    update ro_orgperson
    <trim prefix="set" suffixOverrides=",">
      <trim prefix="ORGUUID =case" suffix=" else ORGUUID end,">
        <foreach collection="list" item="item" index="index">
          <if test="item.orguuid !=null">
            when personuuid=#{item.personuuid} then #{item.orguuid}
          </if>
        </foreach>
      </trim>
    </trim>
    <include refid="orSql"/>
  </update>