mybatis批量增、删、改（更新）操作oracle和mysql的几点区别

    最近在使用mybatis框架写操作oracle数据库时，在批量操作中遇到了一些与mysql的差别。写一篇文章记录一下。

    按照mysql习惯的写完xml后会出现ORA-00911:无效的字符错误

检查了分号是英文状态的。其他的字符也没有问题。最后找到原因是因为oracle的批量写法与mysql不同。

言归正传：

1.批量插入

mysql写法，实例：

<insert id="insertBatch" parameterType="java.util.List" > insert into TEC_KJ_CR_EO (id, cr_id, item_name, item_id, opinion_conclusion, specific_comments, review_expert_id, review_expert_name, company_id, create_person, create_date, dep_id, delete_flag)
values <foreach collection="list" item="item" index="index" open="" separator="union all" close=""> ( #{item.id,jdbcType=VARCHAR}, #{item.crId,jdbcType=VARCHAR}, #{item.itemName,jdbcType=VARCHAR}, #{item.itemId,jdbcType=VARCHAR}, #{item.opinionConclusion,jdbcType=CHAR}, #{item.specificComments,jdbcType=VARCHAR}, #{item.reviewExpertId,jdbcType=VARCHAR}, #{item.reviewExpertName,jdbcType=VARCHAR}, #{item.companyId,jdbcType=VARCHAR}, #{item.createPerson,jdbcType=VARCHAR}, #{item.createDate,jdbcType=TIMESTAMP}, #{item.depId,jdbcType=VARCHAR}, #{item.deleteFlag,jdbcType=CHAR} ) </foreach> </insert>

oracle 写法，实例：

<insert id="insertBatch" parameterType="java.util.List" >
    insert into TEC_KJ_CR_EO (id, cr_id, item_name, 
      item_id, opinion_conclusion, specific_comments, 
      review_expert_id, review_expert_name, company_id, 
      create_person, create_date, dep_id, 
      delete_flag)
       <foreach collection="list" item="item" index="index" open="" separator="union all" close="">
    (select #{item.id,jdbcType=VARCHAR}, #{item.crId,jdbcType=VARCHAR}, #{item.itemName,jdbcType=VARCHAR}, 
      #{item.itemId,jdbcType=VARCHAR}, #{item.opinionConclusion,jdbcType=CHAR}, #{item.specificComments,jdbcType=VARCHAR}, 
      #{item.reviewExpertId,jdbcType=VARCHAR}, #{item.reviewExpertName,jdbcType=VARCHAR}, #{item.companyId,jdbcType=VARCHAR}, 
      #{item.createPerson,jdbcType=VARCHAR}, #{item.createDate,jdbcType=TIMESTAMP}, #{item.depId,jdbcType=VARCHAR}, 
      #{item.deleteFlag,jdbcType=CHAR} from dual)
       </foreach>
  </insert>

注意到oracle没有values;

2.批量更新

<update id="updateByPrimaryKeySelectiveBatch" parameterType="java.util.List">
 <foreach collection="list" item="item" index="index" separator=";" open="" close="">
 update TEC_KJ_CTP 
 <set>
  <if test="projectName != null"> project_name = #{projectName,jdbcType=VARCHAR}, </if>
  <if test="projectCode != null"> project_code = #{projectCode,jdbcType=VARCHAR}, </if>
  <if test="projectKey != null"> project_key = #{projectKey,jdbcType=VARCHAR}, </if>
  </set> 
 where id = #{id,jdbcType=VARCHAR}
 </foreach>
</update>

oracle写法，实例：

注意 parameterType 区别

<update id="updateByPrimaryKeySelectiveBatch" parameterType="com.test.tec.kj.model.Ctp"> 
begin 
 <foreach collection="list" item="item" index="index" separator=";" open="" close="">
 update TEC_KJ_CTP 
 <set> 
 <if test="projectName != null"> project_name = #{projectName,jdbcType=VARCHAR}, </if>
 <if test="projectCode != null"> project_code = #{projectCode,jdbcType=VARCHAR}, </if> 
 <if test="projectKey != null"> project_key = #{projectKey,jdbcType=VARCHAR}, </if>
 </set> 
 where id = #{id,jdbcType=VARCHAR}
 </foreach> 
;end; 
</update>

主要这两个差别需要注意一下。