- <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<>();
- int count = 0;
- if (list != null && list.size() > 0) {
- int batchCount = 500;
- int batchLastIndex = batchCount;
- for (int index = 0; index < endowList.size(); ) {
- if (batchLastIndex >= endowList.size()) {
-
- batchLastIndex = endowList.size();
- count += testDao.insertAll(list.subList(index, batchLastIndex),table_name);
- break;
- } else {
- count += testDao.insertAll(list.subList(index, batchLastIndex),table_name);
-
- index = batchLastIndex;
-
- batchLastIndex = index + batchCount;
- }
- }
- }
- return count;