foreach语句 :
collection 属性的参数类型可以使用:list、array、map
collection : ( collection="entities" ) 中的值必须跟 @Param标签指定的元素名一样
item:( item="entity" ) 表示在迭代过程中每一个元素的别名,可以随便起名,但是必须跟元素中的 #{} 里面的名称一样。
index:( index="index" ) 表示在迭代过程中每次迭代到的位置(下标)
open:( open="(" ) 前缀 sql语句中集合都必须用小括号()括起来
close:( close=")" ) 后缀 sql语句中集合都必须用小括号()括起来
separator:( separator="," ) 分隔符,表示迭代时每个元素之间以什么分隔 ( , )
批量添加 :
insert into test(create_time,creater,update_time,updater)
values
<foreach collection="entities" item="entity" separator=",">
(#{entity.createTime}, #{entity.creater}, #{entity.updateTime}, #{entity.updater})
</foreach>
批量更新:
/**
* 批量更新
* @param list
* @return
*/
int updateBatch(@Param("list") List<MspCardPoc> list);
<!-- 批量更新 -->
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update card
<set>
create_time = #{item.createTime},
creater = #{item.creater},
update_time = #{item.updateTime},
updater = #{item.updater},
</set>
where id = #{item.id}
</foreach>
</update>
批量添加或更新:
insert into test(create_time,creater, update_time, updater)
values
<foreach collection="entities" item="entity" separator=",">
(#{entity.createTime}, #{entity.creater}, #{entity.updateTime}, #{entity.updater})
</foreach>
on duplicate key update
create_time = values(create_time),
creater = values(creater),
update_time = values(update_time),
updater = values(updater)
通过 id 批量查询 : 入参 list
select * from test
where id in
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
批量删除 : 入参 list
delete
from test
where id in
<foreach open="(" separator="," collection="list" index="index" item="id" close=")">
#{id}
</foreach>