从FTP服务器下载csv文件,并导入数据库
1.pom导入依赖
<dependency>
<groupId>com.opencsv</groupId>
<artifactId>opencsv</artifactId>
<version>4.6</version>
</dependency>
2.设置实体
public static User setData(String[] strs, User user) {
user.setId(strs[0].trim());
user.setName(strs[1].trim());
user.setSex(strs[2].trim());
......
user.setGkey(RandomUtil.randomStringUpper(32));
return user;
}
3.导入数据库
@Override
public void importCsv(String fileName) {
String filePath = "ftp路径" + fileName + ".csv";
File csvFile=null;
OutputStream ops = null;
CSVReader csvReader = null;
try {
long startTime = System.currentTimeMillis();
csvFile = File.createTempFile("tmp", filePath.substring(filePath.lastIndexOf(".")));
logger.info("==========生成临时文件[" + csvFile.getPath() + "]==========");
ops = new FileOutputStream(csvFile);
logger.info("==========开始下载CSV文件,并写入临时文件...==========");
Boolean isSucccess = FtpUtil.retrieveFile(filePath, ops);
logger.info("==========写入临时文件成功与否[" + isSucccess + "]==========");
if (isSucccess) {
DataInputStream in = new DataInputStream(new FileInputStream(csvFile));
csvReader = new CSVReader(new InputStreamReader(in, "utf-8"), '\t',
CSVParser.DEFAULT_QUOTE_CHARACTER, CSVParser.DEFAULT_ESCAPE_CHARACTER);
String[] strs;
List<User> list = new ArrayList<>();
User user;
int i = 0, num = 0;
logger.info("==========开始处理CSV文件数据...==========");
long importTime = System.currentTimeMillis();
long stopTime;
while ((strs = csvReader.readNext()) != null) {
user = new User();
list.add(setData(strs, user));
i++;
if (i == 1000) {
userMapper.insertUserList(list);
list.clear();
num += i;
stopTime = System.currentTimeMillis();
logger.info("==========已导入[" + num + "]条,耗时[" + (stopTime - importTime) / 1000 + "]秒==========");
i = 0;
}
}
long endTime = System.currentTimeMillis();
logger.info("==================================================");
logger.info("文件名[call_detail_daily-" + fileName + ".csv]");
logger.info("完整路径[" + filePath + "]");
logger.info("共导入[" + num + "]条数据");
logger.info("总耗时[" + (endTime - startTime) / 1000 + "]秒");
logger.info("==================================================");
}else {
logger.info("==========文件["+filePath+"]下载失败或不存在==========");
}
} catch (Exception e) {
logger.error("==========错误==========", e);
} finally {
if (csvReader != null) {
try {
csvReader.close();
} catch (IOException e) {
logger.error("==========错误==========", e);
}
}
if (ops!=null){
try {
ops.close();
} catch (IOException e) {
logger.error("==========错误==========", e);
}
}
if (csvFile.exists()) {
boolean isDel = FileUtil.del(csvFile);
logger.info("==========删除临时文件成功与否[" + isDel + "]==========");
}
}
}