今天在项目开发中需要用到批量插入数据,在网上很容易找到了代码,但是报错SQL没有正确结束,后来查了下,是因为用了MySQL的批量插入。
这里贴出Oracle的批量插入代码,以做记录。
<insert id="insertAll" parameterType="java.util.List">
INSERT INTO TABLE_NAME
(ID,NAME,VALUE)
SELECT SEQUENCE.NEXTVAL ID,A.* FROM (
<foreach collection="list" item="item" index="index" separator="UNION ALL">
SELECT
#{item.name,jdbcType=VARCHAR},
#{item.value,jdbcType=DOUBLE}
FROM DUAL
</foreach>
) A
</insert>
注:insert语句中不写values关键字,不然会报错。
如果不用序列自增主键,如下:
<insert id="insertAll" parameterType="java.util.List">
INSERT INTO TABLE_NAME
(ID,NAME,VALUE)
<foreach collection="list" index="index" item="item" open="(" close=")" separator="union all">
SELECT
#{item.id,jdbcType=VARCHAR},
#{item.name,jdbcType=VARCHAR},
#{item.value,jdbcType=DOUBLE}
FROM DUAL
</foreach>
</insert>
批量更新操作:
<update id="updateAll" parameterType="com.ab.bean.MainDataBean">
<foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
update table_name set
value = #{item.value,jdbcType=DOUBLE}
where id = #{item.id,jdbcType=VARCHAR}
</foreach>
</update>
批量删除操作:
<delete id="deleteAll" parameterType="com.ab.bean.MainDataBean">
<foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
delete from table_name
where id = #{item.id} and name = #{item.name}
</foreach>
</delete>
jdbcType正常情况下可以不写,但是如果数据中有null,就必须声明jdbcType,否则会报错。
重点:批量insert的效率问题!
后来在项目中,因为数据稍大一些,比如有一万多条的时候,批量insert的效率会变得异常差,要二十多分钟甚至更久才能完成。可以说还不如在java中用for循环每次单条数据插入。后来查了查,可以通过在java中对要插入的list通过subList方法进行分割,多次处理。代码如下:(这段代码在网上也能找到,但是我发现他们的逻辑有问题,会漏掉部分数据,我做了修改)
Service实现类的主要代码:
ArrayList<MainDataBean> list = new ArrayList<>();//这里忽略list封装值的过程
int count = 0; //用于累计结果
if (list != null && list.size() > 0) {
int batchCount = 500; //每次批量插入的数据量大小
int batchLastIndex = batchCount; //声明每次截取list的终止索引大小
for (int index = 0; index < endowList.size(); ) { //声明index为截取的起始索引
if (batchLastIndex >= endowList.size()) {
//当结尾索引大于list的size时,将终止索引赋值为list的size,subList方法含头不含尾
batchLastIndex = endowList.size();
count += testDao.insertAll(list.subList(index, batchLastIndex),table_name);
break; //最后一次执行后,终止循环
} else {
count += testDao.insertAll(list.subList(index, batchLastIndex),table_name);
//每次执行完insert之后,将起始索引赋值为终止索引
index = batchLastIndex;
//将终止索引赋值为原先的终止索引+每次批量处理的数据大小
batchLastIndex = index + batchCount;
}
}
}
return count;
通过上面的方式,将较大的数据分批处理,可以一定程度上提高效率。我试过每次批量插入的数据量,当大小为500时,相对效率要比800或1000要高不少,其他的数据量大小没有试过。