添加
<insert id="functionName" parameterType="java.util.List">
INSERT INTO table_name
( field1,field2,field3 )
VALUE
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.field1},#{item.field2}
<if test="item.field3 != null">,#{item.field3}</if>
<if test="item.field3 == null">,null</if>//这里这个部分是为了告诉大家,某些字段如果不穿值得话我的处理方式,
<if test="item.field3 != null">,#{item.field3}</if>//因为这个语句在 mysql上的是这样的:
// INSERT INTO table_name ( field1,field2,field3) VALUE('','',''),('','','')
)
</foreach>
</insert>
修改
<update id="functionName" parameterType="java.util.List">
UPDATE effect_info
<trim prefix="SET" suffixOverrides=",">
<trim prefix="field = CASE id" suffix="END,">
<foreach collection="list" item="item" index="index">
<if test="item.field != null">
WHEN #{item.id} THEN #{item.field}
</if>
</foreach>
</trim>
<trim prefix="profile = CASE effect_id" suffix="END,">
<foreach collection="list" item="item" index="index">
<if test="item.profile != null">
WHEN #{item.effectId} THEN #{item.profile}
</if>
</foreach>
</trim>
</trim>
WHERE id IN
<foreach collection="list" item="item" index="no" open="(" separator="," close=")">
#{item.id}
</foreach>
</update>
主要运用的是mysql when then 语法
UPDATE table_name SET field = case id
WHEN #{id1} THEN #{field1}
WHEN #{id2} THEN #{field2}
END
WHERE id IN(id1,id2)
删除
<delete id="functionName" parameterType="java.util.List">
DELETE FROM table_name WHERE id IN
<foreach collection="list" item="item" index="no" open="(" separator="," close=")">
#{item.id}
</foreach>
</delete>
DELETE FROM table_name WHERE id IN(id1,id2,id3)