mybatis中oralce,mysql,sqlsever数据库批量修改

java中DAO的代码：

  int editSystemBatch(@Param("list")List<SubSysThemeVo> list);

1.oracle 批量修改

 <update id="editSystemBatch" parameterType="java.util.List">
        <foreach collection="list" item="theme" index="index" open="begin" close=";end;" separator=";">
	     UPDATE T_BAS_MENU
	        <trim prefix="set" suffixOverrides=","> 
	           <if test="theme.menuName !=null and theme.menuName != ''">
	           		MENUNAME = #{theme.menuName,jdbcType= VARCHAR},
	           </if>
	           <if test="theme.menuPic !=null and theme.menuPic != ''">
	           		MENUPIC = #{theme.menuPic,jdbcType= VARCHAR},
	           </if>
	        </trim> 
	       where MENUID = #{theme.menuId,jdbcType=INTEGER}
        </foreach>
</update>

2.mysql批量修改

 <update id="editSystemBatch" parameterType="java.util.List">
      update T_BAS_MENU
        <trim prefix="set" suffixOverrides=",">
          <trim prefix="menuName=(case" suffix="end),">
             <foreach collection="list" item="theme">
	               <if test="theme.menuName !=null and theme.menuName != ''">
	           		when  MENUID = #{theme.menuId} then #{theme.menuName,jdbcType=VARCHAR}
	               </if>
              </foreach>
           </trim>
          <trim prefix="menuPic=(case" suffix="end),">
             <foreach collection="list" item="theme">
	               <if test="theme.menuPic !=null and theme.menuPic != ''">
	           		when  MENUID = #{theme.menuId} then #{theme.menuPic,jdbcType=VARCHAR}
	               </if>
              </foreach>
           </trim>
        </trim>       
       where MENUID in 
        <foreach collection="list" item="theme" index="index" open="("  separator="," close=")">
            #{theme.menuId}
        </foreach> 
</update>

或者

 <update id="editSystemBatch" parameterType="java.util.List">
        insert into  T_BAS_MENU(MENUID,
                            MENUNAME,
                            MENUPIC
        )values
         <foreach collection="list" item="theme" open="" close="" index="index"  separator=",">
                 (#{theme.menuId,jdbcType= INTEGER},
                 #{theme.menuName,jdbcType= VARCHAR},
                 #{theme.menuPic,jdbcType= VARCHAR}
                 )
         </foreach>
          on duplicate key update 
          MENUID = values(MENUID),
          MENUNAME = values(MENUNAME),
          MENUPIC = values(MENUPIC)
      </update>

3.sqlsever批量修改

<update id="editSystemBatch" parameterType="java.util.List">
    <foreach collection="list" item="theme" index="index" open="" close="" separator=";">
     UPDATE T_BAS_MENU
        <trim prefix="set" suffixOverrides=","> 
           <if test="theme.menuName !=null and theme.menuName != ''">
           		MENUNAME = #{theme.menuName,jdbcType= VARCHAR},
           </if>
           <if test="theme.menuPic !=null and theme.menuPic != ''">
           		MENUPIC = #{theme.menuPic,jdbcType= VARCHAR},
           </if>
        </trim> 
       where MENUID = #{theme.menuId,jdbcType=INTEGER}
    </foreach>
</update>

可以看出

1. oracle的foreach 是 ：

<foreach collection="list" item="theme" index="index" open="begin" close=";end;" separator=";">
</foreach>

3. sqlsever的foreach 是 ：

<foreach collection="list" item="theme" index="index" open="" close="" separator=";">
<foreach>

3. mysql 需要借助case… when… 或者用insert into … on duplicate key update …（还有其他两种不常用）