Mysql和Oracle中mybatis批量插入，修改，删除

目录

Mysql批量插入：

MySql批量修改：

MySql批量删除：

MySql逻辑批量删除：

Oracle批量插入：separator="union all"来连接语句

Oracle批量修改：

Oracle批量删除：

Oracle逻辑批量删除：

Mysql批量插入：

<insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO mon_alert_detail (
                 id,
                 pm_code,
                 alert_pm_code
        ) VALUES 
        <foreach collection="list" item="item" index="index" separator=",">
        (
                 #{item.id},
                 #{item.pmCode},
                 #{item.alertPmCode}          
        )
        </foreach>
    </insert>

MySql批量修改：

<update id="batchUpdate" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" separator=";">
        UPDATE mon_alert_detail 

      <set>
                     <if test="item.alertPmCode!=null and item.alertPmCode!=''">alert_pm_code = #{item.alertPmCode},</if>
                     <if test="item.alertNo!=null and item.alertNo!=''">alert_no = #{item.alertNo},</if>
                     <if test="item.collectTime!=null and item.collectTime!=''">collect_time = #{item.collectTime},</if>
           WHERE id = #{item.id}

     </set>
        </foreach>
    </update>

MySql批量删除：

<delete id="deleteByIds" parameterType="java.util.List">
        DELETE FROM  mon_detail
        WHERE
        <choose>
            <when test="list!=null and list.size>0">
                id IN
                <foreach collection="list" index="index" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </when>
            <otherwise>1!=1</otherwise>
        </choose>
    </delete>

MySql逻辑批量删除：

<update id="batchDelete" parameterType="java.util.List">
        UPDATE mon_detail
        SET status = 1
        <choose>
        <when test="list!=null and list.size()>0">
            (id) IN 
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
            (#{item.id})
            </foreach>
        </when>
        <otherwise> 1!=1</otherwise>
        </choose>
    </update>

Oracle批量插入：separator="union all"来连接语句

<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="false">
    insert into DOCUMENT_INFO (
    unid,
    cometime,
    copysend
    )
    <foreach collection="list" item="item" index="index" separator="union all" >
        (select
        #{item.unid},
        #{item.cometime},
        #{item.copysend}
        from dual)
    </foreach>
</insert>

Oracle批量修改：

<update id="batchUpdate" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
        UPDATE DOCUMENT_INFO
        <set>
        <if test="item.unid!=null and item.unid!=''">unid = #{item.unid},</if>
        <if test="item.status!=null">status = #{item.status},</if>
        <if test="item.updatetime!=null">updatetime = #{item.updatetime},</if>
        </set>
        WHERE unid = #{item.unid}
    </foreach>
</update>

Oracle批量删除：

方法：int deleteByIds( List<String> list )，传入的参数为String，item等于传入的参数名

<delete id="deleteByIds" parameterType="java.util.List">
    DELETE FROM  DOCUMENT_INFO
    WHERE
    <choose>
        <when test="list!=null and list.size>0">
            unid IN
            <foreach collection="list" index="index" item="unid" open="(" separator="," close=")">
                #{unid}
            </foreach>
        </when>
        <otherwise>1!=1</otherwise>
    </choose>
</delete>

 

---

方法：public int batchDelete(List<Document> document);
<update id="batchDelete" parameterType="java.util.List">
    UPDATE DOCUMENT_INFO
    SET status = 1
    WHERE
    <choose>
        <when test="list!=null and list.size()>0">
            (unid) IN
            <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
                (#{item.unid})
            </foreach>
        </when>
        <otherwise> 1!=1</otherwise>
    </choose>
</update>