SpringBoot+Jdbc进行海量数据的批量插入

海量数据的插入

首选Jdbc批量处理，其次就是自己拼接好完整的SQL语句，交给Jdbc处理

Jdbc批处理

伪代码

1. 创建SQL语句
2. 从数据库连接池中获取数据库连接
3. 绑定SQL语句
4. 取消自动提交
5. 创建需要插入的数据
6. 将语句添加到模板中
7. 执行模板
8. 清空模板
9. 提交事务
10. 关闭连接

真代码

 @Slf4j
 public class BatchInsertData {
    //注入数据库连接池，我这里采用的是Druid连接池
    @Autowired
    private DruidDataSource dataSource;
   
    @Transactional(propagation = Propagation.REQUIRED,rollbackFor = Exception.class)
    public void insertData(){
        //记录开始时间：
        log.info("start:" + LocalDate.now())
        //注意在构建SQL语句时不要在最后面添加分号（;）
        String sql = "INSERT INTO personnel_info(gender,`name`,`institution_id`) VALUES (?,?,?)";
        DruidPooledConnection connection = null;
        PreparedStatement ps = null;
        try {
            //从数据库连接池中获取数据库连接
            connection = dataSource.getConnection();
            //绑定SQL语句
            ps = connection.prepareStatement(sql);
            //取消自动提交事务
            connection.setAutoCommit(false);
            //构建数据
            for (int j = 0; j < 200000; j++) {
                for (int k = 1; k <= 10; k++) {
                    //设置性别
                    if(k % 2 == 0)
                      ps.setInt(1,0);
                    else
                      ps.setInt(1,1);
                    ps.setString(2,"人员" + k);
                    ps.setInt(3,j);
                    //构建好一条数据就添加到模板中
                    ps.addBatch();
                }
            }
            //执行模板
            ps.executeBatch();
            //清空模板
            ps.clearBatch();
            //提交事务
            connection.commit();
        } catch (SQLException e) {
           throw new RuntimeException(e);
        } finally {
            try {
              //关闭连接，释放连接
              connection.close();
            } catch (SQLException e) {
              throw new RuntimeException(e);
            }
        }
        //记录结束时间：
        log.info("end:" + LocalDate.now())
    }
 ​
 }

完整SQL语句执行

主要是拼接SQL语句，这里注意最好不要用String去凭借，大量的数据进拼接可能会带来多余的内存消耗，所以我这里采用StringBiuder去做拼接

伪代码

1. 创建基础SQL语句
2. 进行Values后面内容的拼接
3. 拼接完成后一次性执行完整的SQL语句

真代码

 @Transactional(propagation = Propagation.REQUIRED,rollbackFor = Exception.class)
     public void generateData(){
         //设置开始时间
         log.info("start:" + LocalDate.now());
         //构建基础SQL
         StringBuilder sql = new StringBuilder("INSERT INTO personnel_info(gender,`name`,`institution_id`) VALUES ");
         //构建内容，并进行拼接
         for (int i = 0; i < 100000; i++) {
             for (int j = 1; j <= 10; j++) {
                 StringBuilder values = new StringBuilder("(");
                 //设置性别
                 if(i % 2 == 0)
                     values.append("0,");
                 else
                     values.append("1,");
                 //设置姓名
                 values.append("'人员").append(j).append("',");
                 //设置机构Id
                 values.append(i).append(")");
                 sql.append(values);
                 if (i < start + 59999){
                     sql.append(",");
                 } else if (i == start + 59999) {
                     if(j < 10){
                         sql.append(",");
                     }else {
                         sql.append(";");
                     }
                 }
             }
         }
         //拼接完成，执行SQL语句
         jdbcTemplate.batchUpdate(sql.toString());
         log.info("end:" + LocalDate.now());
     }

注意在使用批量插入时，需要在配置文件中的数据库配置地方，数据库url地址后面加上设置允许批处理

&rewriteBatchedStatements=true

 datasource:
   driver-class-name: com.mysql.jdbc.Driver
   url: jdbc:mysql://localhost:3306/dataBase?useUnicode=true&characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true
   username: username
   password: passsword
   # 连接池配置
   druid:
     # 初始连接数
     initialSize: 5
     # 最小连接池数量
     minIdle: 10
     # 最大连接池数量
     maxActive: 20
     # 配置获取连接等待超时的时间
     maxWait: 60000

如果数据量特别大，可以采用分批次提交，设置一定的插入数据量，再给批量插入方法设置一个定时job，让系统隔一段时间就插入一定量的数据，这样可以解开双手，也可以避免一次性插入海量数据带来的内存压力