Mybatis笔记 -- 批量操作(查询、插入、更新、删除)
批量查询
Mapper接口
/**
* 根据剧典id list查询剧典
*/
public List<Drama> selectByIds(@Param("dramaIds")List<Long> dramaIds);
mapper.xml
<!-- 根据剧典id list查询剧典 -->
<select id="selectByIds" resultMap="DramaImageResultMap">
select * from drama where drama_id in
<foreach collection="dramaIds" item="dramaId" open="(" close=")" separator=",">
#{dramaId}
</foreach>
</select>
批量插入
<!-- 批量插入生成的兑换码 -->
<insert id ="insertCodeBatch" parameterType="java.util.List" >
insert into redeem_code
(bach_id, code, type, facevalue,create_user,create_time)
values
<foreach collection ="list" item="reddemCode" index= "index" separator =",">
(
#{reddemCode.batchId}, #{reddemCode.code},
#{reddemCode.type},
#{reddemCode.facevalue},
#{reddemCode.createUser}, #{reddemCode.createTime}
)
</foreach >
</insert >
批量更新
这种方式最简单,就是用foreach组装成多条update语句,但Mybatis映射文件中的sql语句默认是不支持以" ; " 结尾的,也就是不支持多条sql语句的执行。所以需要在连接mysql的url上加 &allowMultiQueries=true 这个才可以执行。
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update tableName
<set>
name=#{item.name},
name2=#{item.name2}
</set>
where id = #{item.id}
</foreach>
</update>
批量删除
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from user where id in
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
#{item}
</foreach>
</delete>
总结
批量操作,使用 for each
collection="list" ,这里需要注意,如果对象里有list属性,比如userList,list要换成userList
<foreach collection="list" index="index" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
心如止水,虚怀如谷