mybatis中的批量操作(参考模板,学习使用)

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.批量查询

批量删除和批量查询同理