mybatis中oracle,mysql批量操作

1.批量插入

mysql:    参数list要加@param注解

<insert id="insertData" parameterType="java.util.List" >
        insert into person (id,name,sex,address)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id},#{item.name},#{item.sex},#{item.address})
        </foreach>
    </insert>

oracle:

int insertMenuRoleForBatch(List<SysMenuRoleVo> list);// 批量插入菜单角色

  <!-- 批量插入菜单角色 -->
    <insert id="insertMenuRoleForBatch" parameterType="java.util.List">
        INSERT INTO SYS_ROLE_MENU (ID, ROLEID, MENUID,REMARK)
        <foreach collection="list" item="item" index="index" separator="union all">
            (SELECT
            #{item.id}, #{item.roleid}, #{item.menuid}, #{item.remark}
            FROM DUAL)
        </foreach>
    </insert>

2.批量修改

mysql: 

int updateDateByIds(Tb_Consumer_Coupon vo);


<update id="updateDateByIds" parameterType="com.lc.entity.Tb_Consumer_Coupon">
        UPDATE tb_consumer_coupon SET zt = '2' WHERE id in
        <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>

oracle :

 int updateUserBatch(List<SysUser> vo);// 批量更新/重置密码


 <!-- 批量更新重置密码和删除 -->
    <update id="updateUserBatch" parameterType="java.util.List">
        BEGIN
        <foreach collection="list" item="item" index="index" separator=";">
            UPDATE SYS_USER
            <set>
                <if test="item.sfsc!=null and item.sfsc!=''">
                    SFSC = #{item.sfsc},
                </if>
                <if test="item.mm!=null and item.mm!=''">
                    MM = #{item.mm},
                </if>
                <if test="item.ssgw!=null and item.ssgw!=''">
                    ssgw = #{item.ssgw},
                </if>
            </set>
            WHERE ID = #{item.id}
        </foreach>
        ;END;
    </update>

3.批量删除

mysql:

int deleteParkingByParkingId(@Param("parkingIds") List<String> parkingIds);


<!--批量删除车位信息-->
    <delete id="deleteParkingByParkingId" parameterType="java.lang.String">
        DELETE FROM yx_parking_info WHERE parking_id in
        <foreach item="parkingIds" collection="parkingIds" open="(" separator="," close=")">
            #{parkingIds,jdbcType=VARCHAR}
        </foreach>
    </delete>

oracle:

  int deleteRoleForBatch(List<SysRoleVo> list);// 批量删除


<!-- 批量删除 -->
    <delete id="deleteRoleForBatch" parameterType="java.util.List">
        DELETE FROM SYS_ROLE
        <where>
            <foreach collection="list" index="index" item="item" open="(" separator="or" close=")">
                ID=#{item.id}
            </foreach>
        </where>
    </delete>