批量插入
对应sql
insert into tabelname(colum1,colum2...) values(aa,aa...),(bb,bb...)
<!-- 批量插入数据 -->
<insert id="insertBatch" parameterType="java.util.List"
useGeneratedKeys="true">
<selectKey resultType="long" keyProperty="id" order="AFTER">
SELECT
LAST_INSERT_ID()
</selectKey>
insert into wd_solr(fayu_id,tablename,name,logo,description,section_no,look_count,favorite_count,create_uid,create_time,update_time,timestamp)
values
<foreach collection="list" item="wdSolr" index="index"
separator=",">
(
#{wdSolr.fayuId},#{wdSolr.tablename},#{wdSolr.name},#{wdSolr.logo},
#{wdSolr.description},#{wdSolr.sectionNo},#{wdSolr.lookCount},#{wdSolr.favoriteCount},
#{wdSolr.createUid},#{wdSolr.createTime},#{wdSolr.updateTime},#{wdSolr.timestamp}
)
</foreach>
</insert>
批量更新
对应Sql
update tablename set
colum1= case id when 1 then aa
when 2 then bb
when 3 then cc
....
end,
colum2=case id when 1 then aa
when 2 then bb
when 3 then cc
....
end,
where id in (1,2,3....)
<!-- 批量更新数据 -->
<update id="updateBatch">
update wd_solr set
name =
<foreach collection="list" item="wdSolr" index="index"
separator=" " open="case id" close="end">
when #{wdSolr.id} then
#{wdSolr.name}
</foreach>
,logo =
<foreach collection="list" item="wdSolr" index="index"
separator=" " open="case id" close="end">
when #{wdSolr.id} then
#{wdSolr.logo}
</foreach>
,timestamp =
<foreach collection="list" item="wdSolr" index="index"
separator=" " open="case id" close="end">
when #{wdSolr.id} then #{wdSolr.timestamp}
</foreach>
where id in
<foreach collection="list" item="wdSolr" index="index"
separator="," open="(" close=")">
#{wdSolr.id}
</foreach>
</update>
批量删除
对应sql
delete from tablename where id in (1,2,3....)
<!-- 批量删除数据 -->
<delete id="deleteBatch">
delete from wd_solr where
id in (
<foreach collection="list" item="wdSolr" index="index"
separator=",">
#{wdSolr.id}
</foreach>
)
</delete>
注:批量插入、更新不建议使用,如果数据过大,参数过多同时操作于一条sql会极度占用连接资源,建议使用循环操作单条执行语句。