在开发过程中,我们经常会对某个数据库表进行批量增删改的操作,批量的好处在于它可以减少对数据库的访问次数,减轻数据的库压力,同时也会提高项目的性能,这里我只贴sql语句的批量增删改
批量添加
<insert id="batchInsert">
insert into user(name,age,password,hobby) values
<foreach collection="list" item="user" separator=",">
(#{user.name},
#{user.age},
#{user.password},
#{user.hobby})
</foreach>
</insert>
批量修改
<update id="batchUpdate">
<foreach collection="list" item="user" separator=";" open="" close="">
update user
<set >
<trim suffixOverrides=",">
<if test="user.name != null" >
name= #{user.name},
</if>
<if test="user.age != null" >
age= #{user.age},
</if>
<if test="user.password!= null" >
password= #{user.password},
</if>
<if test="user.hobby}!= null" >
hobby= #{user.hobby}},
</if>
</trim>
</set>
where id= #{user.id}
</foreach>
</update>
批量修改值得注意的地方是
在配置文件中需要在数据库链接地址后面加上&allowMultiQueries=true
否则运行报错
批量删除
<delete id="batchDelete">
delete from user where id in
<foreach collection="list" item="ids" separator="," open="(" close=")">
#{ids}
</foreach>
</delete>