mybatis中的批量操作
1.批量insert
1.1第一种写法
说明:mysql和oracle通用
<insert id="insertSomeDemo" parameterType="List">
<!-- parameterType的值可以是java.util.List -->
insert into demo (id,name)
<foreach collection="demoList" item="demo"
separator="union all">
(select
#{demo.id},#{demo.name,jdbcType=VARCHAR} from dual)
</foreach>
</insert>
1.2第二种写法
说明:只用mysql使用
<insert id="insertSomeDemo2" parameterType="List">
insert into demo (id,name) values
<foreach collection="demoList" item="demo" separator=",">
(#{demo.id},#{demo.name})
</foreach>
</insert>
1.3第三种写法
说明:只用oracle使用(不建议使用,因为返回的行数是-1)
<insert id="insertSomeDemo2" parameterType="java.util.List">
BEGIN
<foreach collection="demoList" item="demo" separator=";">
INSERT INTO demo (id,name)
VALUES
(#{demo.id},#{demo.name})
</foreach>
;END;
</insert>
2.批量删除
说明:mysql和oracle通用
<delete id="deleteByIds" parameterType="Integer">
delete from demo where id in
<foreach collection="ids" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
3.批量更新
- oracle的批量更新
<update id="updateSomeDemo" parameterType="java.util.ArrayList">
<foreach collection="demoList" item="demo" index="index" open="begin" close=";end;" separator=";">
UPDATE demo
<set>
name= #{demo.name,jdbcType=VARCHAR}
</set>
WHERE
id = #{demo.id,jdbcType=INTEGER}
</foreach>
</update>
- mysql的批量更新
<update id="updateSomeDemo" parameterType="java.util.List">
<foreach collection="demoList" item="item" index="index" open=""
close="" separator=";">
update demo
<set>
name=#{item.name},
</set>
where id = #{item.id}
</foreach>
</update>
4.批量查询
批量删除和批量查询同理