springboot+oracle批量导入大数据

涉及技术

• springboot
• mybatis
• oracle

1.mybatis中的sql语句

第一种写法

insert into table(...) (select ... from dual) union all (select ... from dual)

<insert id="addList" parameterType="java.util.List" useGeneratedKeys="false">
        INSERT INTO T_APPLAUD
        (
            ID,
            USER_ID,
            BUSINESS_TYPE,
            PRODUCT_ID,
            CREATE_TIME
        )
        <foreach item="item" index="index" collection="list" separator="union all">
        (
            SELECT
                #{item.id},
                #{item.userId},
                #{item.businessType},
                #{item.productId},
                #{item.createdTime}
            FROM DUAL
        )
        </foreach>
    </insert>

第二种写法

<insert id="addList" parameterType="java.util.List" useGeneratedKeys="false">
        INSERT ALL
        <foreach item="item" index="index" collection="list">
        INTO T_APPLAUD
        (
            ID,
            USER_ID,
            BUSINESS_TYPE,
            PRODUCT_ID,
            CREATE_TIME
        ) VALUES
        (
            #{item.id, jdbcType=NUMERIC},
            #{item.userId, jdbcType=VARCHAR},
            #{item.businessType, jdbcType=VARCHAR},
            #{item.productId, jdbcType=VARCHAR},
            #{item.createdTime, jdbcType=NUMERIC}
        )
        </foreach>
        SELECT 1 FROM DUAL
    </insert>

2.数据处理

    /**
     * 批量导入数据库
     * dataList 要导入的数据
     * DataPojo 实体类
     */
    public String saveBatch() {
        ......
        try {
            //导入数据库,dataList为所得数据
            if (dataList.size() > 0) {
                //每次导入2000条
                int num = 2000;
                int ceil = (int) Math.ceil(dataList.size() / (double) num);
                List<DataPojo> pojoList = new ArrayList<>();
                int start = 0, stop = 0;
                long startTime = System.currentTimeMillis();
                for (int i = 0; i < ceil; i++) {
                    pojoList.clear();
                    start = i * num;
                    stop = Math.min(i * num + num - 1, dataList.size() - 1);
                    System.out.println("range:" + start + " - " + stop);
                    for (int j = start; j <= stop; j++) {
                        pojoList.add(dataList.get(j));
                    }
                    //在循环中分批次多次导入数据
                    dataMapper.insertBatch(pojoList);
                    System.out.println("已经插入[" + (stop + 1) + "]条");
                }
                long endTime = System.currentTimeMillis();
                System.out.println("插入数据库执行时间 = " + (endTime - startTime) / 1000 + "秒");
                return "成功插入[" + (stop + 1) + "]条数据，耗时[" + (endTime - startTime) / 1000 + "]秒";
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }