目录
Oracle批量插入:separator="union all"来连接语句
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>