大批量数据入库方法

桃扇骨 2022-03-31 06:20 460阅读 0赞

项目中遇到的导入大批量数据的方法，数据库是MySQL，数据库连接直接使用的是JDBC。  
大概流程是：**先将一行行的记录写入到一个临时文件中，然后一次性将临时文件中的数据导入到数据库，导入方法使用的是mysql的load data local infile命令**。具体步骤如下：  
1、创建临时文件

File tempFileHandle = File.createTempFile( tableName, tempTableSuffix, FileUtils.getTempDirectory() );
    BufferedWriter tempFileWriter =new BufferedWriter(new FileWriter(tempFileHandle, false));

tableName可以自定义，用于生成文件名，tempTableSuffix为临时文件的后缀。  
2、定义写入文件的方法

public void insertRecord( String... fieldValues) {
          if( fieldValues.length == 0 ){
             return;
          }
          try {
             tempFileWriter.write( escapeValue(fieldValues[0]) );
             for( int i=1; i<fieldValues.length; i++ ){
                tempFileWriter.write( "\t" );
                tempFileWriter.write( escapeValue(fieldValues[i]) );
             }
             tempFileWriter.write("\n");;
          } catch (IOException e) {
             System.err.println( "Unable to write to temp file.\n");
             e.printStackTrace();
          }
       }
          private String escapeValue(String value) {
           if (value==null) {
               return "\\N";
           }       
           return value.replace("\r", "").replaceAll("\n", "\\\\n").replace("\t", "\\t");
       }

3、导入数据库  
在mysql模型中，如果表与表之间有外键约束，那么在每条记录上都要求外键引用完整性，而不仅仅是在事务结束时。所以导入时，需要暂时关闭外键约束。

int checks = 0;//外键约束
       PreparedStatement stmt = null;
       Connection con = null;
      con = JdbcUtil.getDbConnection(MySQLbulkLoader.class);
     //查看当前外键约束的状态，1：启用外键约束；0：关闭外键约束
       stmt = con.prepareStatement("SELECT @@foreign_key_checks;");
       ResultSet result = stmt.executeQuery();
       //备份数据库中外键约束的状态值，以便还原
       result.first();//将指针移至第一行
       checks = result.getInt(1);
       //关闭外键约束
       stmt = con.prepareStatement("SET foreign_key_checks = ?;");
       stmt.setLong(1, 0);
       stmt.execute();
       //导入前刷新文件
       tempFileWriter.flush();
       tempFileWriter.close();
       con = JdbcUtil.getDbConnection();
       stmt = con.createStatement();         
       String command = "LOAD DATA LOCAL INFILE '" + tempFileName.replace("\\", "\\\\") + "'" + " INTO TABLE " + tableName;
       stmt.execute( command );
         //获取已更新的记录数
        int updateCount = stmt.getUpdateCount();
        //获取原文件总行数
        int nLines = FileUtil.getNumLines(tempFileHandle);
         if (nLines!=updateCount ) {
          		//已更新记录与临时文件中的记录不一致
          		//自定义处理逻辑
             
         } else {
             tempFileHandle.delete();
         }

load data vs insert:

load data跳过sql解析，直接生成数据文件;

load data在导入之前会关掉索引，导入完成后更新索引；

load data开始执行后占用的内存空间不会被purge掉

另外load data的速度和文件每行的大小有关，每行所占的字节数越少，load的速度越快；同样的前提，没有索引的要比有索引的要快；