Mybatis基于Oracle数据库实现批量插入的几种方式

几种方式"/>

Mybatis基于Oracle数据库实现批量插入的几种方式

1：MySQL

首先我们说如何基于MySQL实现批量插入：

    <insert id="insertBatch">INSERT INTO LY_TEST (id, name, age )VALUES<foreach collection ="list" item="user" separator ="," close = ";">(#{user.id}, #{user.name}, #{user.age})</foreach></insert>

2：Oracle

2.1：第一种

    <insert id="insertBatch">INSERT ALL<foreach collection="list" item="user" separator=" "close="SELECT * FROM dual" index="index">INTO LY_TEST (id, name, age)VALUES (#{user.id}, #{user.name}, #{user.age})</foreach></insert>

2.2：第二种

    <insert id="insertBatch">INSERT INTO LY_TEST (id, name, age )<foreach collection ="list" item="user" index="index" separator ="union all">(SELECT #{user.id,jdbcType=INTEGER},#{user.name,jdbcType=VARCHAR},#{user.age,jdbcType=INTEGER}from dual)</foreach></insert>

2.3：第三种

    <insert id="insertBatch" parameterType="list"><foreach collection ="list" item="user" separator =";" open="begin" close = ";end;">INSERT INTO LY_TEST (id, name, age ) VALUES (#{user.id}, #{user.name}, #{user.age})</foreach></insert>

补充：

批量删除：

     <delete id="xxx" parameterType="list">delete from LY_TESTwhere id in<foreach collection="list" item="item" open="(" close=")" separator=",">#{item}</foreach></delete>