记录使用MyBatics批量导入数据

1、代码片段

List<User> userList =userService.findByWhere(null,user);
int size = userList.size();
int onceSize = 50;
if(userList!=null && userList.size()>0){
    if(size <= onceSize){
        userService.insertBatch(userList);
    }else{
        int times = ((Double) (Math.ceil(size / (float)onceSize))).intValue();
        for(int i =0; i< times; i++){
            userService.insertBatch(userList.subList(i * onceSize,Math.min((i+1) * onceSize,size)));
        }
    }
}

2、Mapper代码

int insertBatch(@Param("list") List<User> list);

3-1、Mapper配置（MySQL）

<insert id="insertBatch" parameterType="java.util.List">
INSERT INTO T_USER
    <trim prefix="(" suffix=")" suffixOverrides=",">
        ID,NAME,AGE,
    </trim>
    values
    <foreach collection="list" item="user" index="index" separator="," >
        <trim prefix="(" suffix=")" suffixOverrides=",">
            #{user.id,jdbcType=INTEGER},
            #{user.name,jdbcType=VARCHAR},
            #{user.age,jdbcType=INTEGER},
        </trim>
    </foreach>
</insert>

　　MySQL批量插入语句如下：

INSERT INTO T_USER (ID,NAME,AGE) values (1,'1',1),(2,'2',2),(3,'3',3),(4,'4',4) ...; 

3-2、Mapper配置（Oracle）

<insert id="insertBatch" parameterType="java.util.List">
    INSERT INTO T_USER
    <trim prefix="(" suffix=")" suffixOverrides=",">
        ID,NAME,AGE,
    </trim>
    <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>    

　　Oracle批量插入语句如下：

INSERT INTO T_USER (ID,NAME,AGE) select 1,'1',1 from dual union all select 2, '2', 2 from dual union all ...;