Java批量插入数据

 

第一种方式：sql批量插入

if (ObjectUtils.isNotEmpty(list)) {
                // 批量大小
                final int batchSize = 1000;

                // 批量处理数据
                for (int i = 0; i < list.size(); i += batchSize) {
                    List<Entity> batch = list.stream()
                            .skip(i)
                            .peek(obj -> {
                                obj.setCreateBy("sys");
                                obj.setTime(new Date());
                            })
                            .limit(batchSize)
                            .collect(Collectors.toList());

                    // 对这个批次的数据进行处理
                    entityMapper.insertOrUpdateBatch(batch);
                }
}

  <insert id="insertOrUpdateBatch">
    insert into entity(id, name, create_by, create_time)
    values
    <foreach collection="batch" item="item" separator=",">
      (#{item.id}, #{item.name}, #{item.createBy}, #{item.createTime})
    </foreach>
    on duplicate key update
    name = values(name),
    create_by = values(create_by),
    create_time = values(create_time)
  </insert>

 

第二种方式：代码插入

try {

            // 批处理大小
            int batchSize = 10000;
            int count = 0;

            // 获取数据库连接
            Connection conn = DriverManager.getConnection(sqlConfig.getUrl(), sqlConfig.getUsername(), sqlConfig.getPassword());
            // 关闭自动提交
            conn.setAutoCommit(false);
            String sql = "insert into entity (ia, name, create_by, create_time) values (?, ?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            // 开始计时
            long beginTime = System.currentTimeMillis();

            //数据集合
            List<Entity> dataList = Lists.newArrayList();

            for (Entity itemVo : dataList) {
                Entity data = new Entity();
                data.setId(itemVo.getId());
                data.setName(itemVo.getName());
                data.setCreateBy(itemVo.getCreateBy());
                data.setCreateTime(new Date());
                dataList.add(data);
            }


            // 要插入的数据列表
            for (Entity data : dataList) {
                pstmt.setString(1, data.getId());
                pstmt.setString(2, data.getName());
                pstmt.setString(3, data.getCreateBy());
                pstmt.setString(4, data.getCreateTime());
                pstmt.addBatch();

                if (++count % batchSize == 0) {
                    // 执行批处理
                    pstmt.executeBatch();
                    // 可以在特定的批次后提交
                    conn.commit();
                    // 清空批处理
                    pstmt.clearBatch();
                }
            }

            // 处理最后的未完整批次
            if (count % batchSize != 0) {
                pstmt.executeBatch();
                conn.commit();
                pstmt.clearBatch();
            }

            pstmt.close();
            conn.close();

            //结束计时
            long endTime = System.currentTimeMillis();
            //执行时间
            log.info("批量插入数据耗时：" + (endTime - beginTime) + " 毫秒");
        } catch (
                Exception e) {
            log.error(e.getMessage());
}