项目中遇到的导入大批量数据的方法,数据库是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的速度越快;同样的前提,没有索引的要比有索引的要快;