流程分析:
数据类型:
ROUTE_ID,LXBM,ROAD_NAME,SRC_LON,SRC_LAT,DEST_LON,DEST_LAT
10000,G50,沪渝高速,115.8605349,30.08934467,115.5437817,30.08898601
10001,G50,沪渝高速,115.5437817,30.08898601,115.2825297,30.28938191
需求分析:数据文件名就是数据库表名,数据类型大概就是第一行是字段名(但是里面的字段名不一定跟数据库中名字完全匹配,可能多了,可能少),第二行以及后面都是对应的数据。
需求设计:
- 第一步:找到文件存放的指定文件夹
- 第二步:循环读取这些文件,获取文件名并且去掉后缀
- 第三步:将文件名去掉_并且全部转为小写。
- 第四步:匹配对应的数据库
- 第五步:读取文件数据第一行,并用map存储字段名对应的位置index
- 第六步:继续读取下面的数据,并将数据通过逗号分隔,获取list,根据字段所在位置获取数据
routeZonesTest.setDestLat(Double.valueOf(split.get(map.get("destlat"))));
- 第七步:将所有需要插入数据库的数据放在一个list中,只到一个文件中的数据读完。(这里采用批量插入效率会高很多)
- 第八步:将list数据插入对应的数据
- 第九步:将已经读取并且插入到数据库的文件移动到别的文件夹。
- 第十步:记录数据插入情况
下面是代码:
public Result<?> importDB() {
List<String> filesPath = new ArrayList<String>();
File files = new File(BaseConst.file_data_path);
File[] tempList = files.listFiles();
continueOut:
for (int i = 0; i < tempList.length; i++) {
// 如果目标文件 是文件
int line = 1;
String fileName = tempList[i].getName();
if (tempList[i].isFile()) {
System.out.println("文 件:" + tempList[i].getName().substring(0, tempList[i].getName().lastIndexOf(".")));
filesPath.add(tempList[i].toString());
File file = new File(tempList[i].toString());
try {
BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(file), "GBK"));
String tempString = null;
Map<String, Integer> map = new HashMap<>();
// 记录下每个字段对应的位置
while ((tempString = reader.readLine()) != null) {
List<String> split = Arrays.asList(tempString.split(","));
if (line == 1) {
System.out.println(tempString);
for (int s = 0; s < split.size(); s++) {
map.put(split.get(s).replace("_", "").replace(" ", "").toLowerCase(), s);
}
}
break;
}
// 匹配该文件属于哪个数据库
String fileNameNew = fileName.substring(0, tempList[i].getName().lastIndexOf(".")).replace("_", "")
.replace(" ", "").toLowerCase();
if (fileNameNew.equals("routezonestest")) {
List<RouteZonesTest> routeZonesTestList = new ArrayList<>();
while ((tempString = reader.readLine()) != null) {
try {
RouteZonesTest routeZonesTest = new RouteZonesTest();
List<String> split = Arrays.asList(tempString.split(","));
routeZonesTest.setDestLat(Double.valueOf(split.get(map.get("destlat"))));
routeZonesTest.setDestLon(Double.valueOf(split.get(map.get("destlon"))));
routeZonesTest.setRoadName(split.get(map.get("roadname")));
if (map.get("routeid") == null) {
dataImportLog(0, 0, "Missing primary key columns", fileName);
continue continueOut;
}
if (split.get(map.get("routeid")) == null) {
dataImportLog(0, line, "The " + line + " row primary key does not exist.", fileName);
continue continueOut;
}
routeZonesTest.setRouteid(Integer.valueOf((split.get(map.get("routeid")))));
routeZonesTest.setSrcLat(Double.valueOf(split.get(map.get("srclat"))));
routeZonesTest.setSrcLon(Double.valueOf(split.get(map.get("srclon"))));
routeZonesTestList.add(routeZonesTest);
line++;
} catch (Exception e) {
dataImportLog(0, 0, "The "+line+" row data has problems.", fileName);
continue continueOut;
}
}
// 批量插入数据库
try {
routezonestest.insertDataBatch(routeZonesTestList);
} catch (Exception e) {
dataImportLog(0, line+1, "Data insertion failed. ", fileName);
continue;
}
} else if (fileNameNew.equals("routezonestest2")) {
List<RouteZonesTest2> routeZonesTest2List = new ArrayList<>();
while ((tempString = reader.readLine()) != null) {
try {
RouteZonesTest2 routeZonesTest2 = new RouteZonesTest2();
List<String> split = Arrays.asList(tempString.split(","));
routeZonesTest2.setDestLat(Double.valueOf(split.get(map.get("destlat"))));
routeZonesTest2.setDestLon(Double.valueOf(split.get(map.get("destlon"))));
routeZonesTest2.setRoadName(split.get(map.get("roadname")));
if (map.get("routeid") == null) {
dataImportLog(0, 0, "Missing primary key columns", fileName);
continue continueOut;
}
if (split.get(map.get("routeid")) == null) {
dataImportLog(0, line, "The " + line + " row primary key does not exist.", fileName);
continue continueOut;
}
routeZonesTest2.setRouteid(Integer.valueOf((split.get(map.get("routeid")))));
routeZonesTest2.setSrcLat(Double.valueOf(split.get(map.get("srclat"))));
routeZonesTest2.setSrcLon(Double.valueOf(split.get(map.get("srclon"))));
routeZonesTest2List.add(routeZonesTest2);
line++;
} catch (ArrayIndexOutOfBoundsException e) {
dataImportLog(0, 0, "The "+line+" row data has problems.", fileName);
continue continueOut;
}
}
reader.close();
// 批量插入数据库
try {
routezonestest2.insertDataBatch(routeZonesTest2List);
} catch (Exception e) {
dataImportLog(0, line, "Data insertion failed. ", fileName);
continue;
}
} else {// 没有找到对应的数据库
dataImportLog(0, line, "The filename does not correspond to the database name.", fileName);
continue;
}
} catch (IOException e) {
e.printStackTrace();
}
// 移动文件
Result<?> removeFile = RemoveFile(file, BaseConst.file_data_used_path);
if (removeFile.getCode() != 200) {
dataImportLog(0, line, removeFile.getMsg(), fileName);
} else {
dataImportLog(1, line, null, fileName);
}
}
// 如果目标文件是文件夹
if (tempList[i].isDirectory()) {
try {
dataImportLog(0, 0, "The target is a folder, not a file", fileName);
} catch (Exception e) {
dataImportLog(0, 0, "The target is a folder, not a file, and the mobile file fails.", fileName);
}
}
}
return Result.returnResult();
}
private void dataImportLog(int status, int line, String reason, String fileName) {
ImportDataLog importDataLog = new ImportDataLog();
importDataLog.setStatus(0);
importDataLog.setDataNumber(line);
importDataLog.setReason(reason);
importDataLog.setId(UUIDUtil.getUUID22());
importDataLog.setFileName(fileName);
importDataLog.setFileUsedName(fileName + "_" + DateUtil.formatDateTime(new Date()));
importDataLog.setCreateTime(new Date());
importDataLogMapper.insertSelective(importDataLog);
}
private Result<?> RemoveFile(File file, String destinationFloderUrl) {
File destFloder = new File(destinationFloderUrl);
// 检查目标路径是否合法
if (destFloder.exists()) {
if (destFloder.isFile()) {
return Result.returnErrorResult("The target path is a file. Please check the target path!");
}
} else {
if (!destFloder.mkdirs()) {
return Result.returnErrorResult("Target folder does not exist, creation failed!");
}
}
// 检查源文件是否合法
if (file.isFile() && file.exists()) {
String destinationFile = destinationFloderUrl + "\\" + file.getName();
if (!file.renameTo(new File(destinationFile))) {
return Result.returnErrorResult("Failed to move files!");
}
} else {
return Result.returnErrorResult("The backup file path is incorrect, and the migration fails.");
}
return Result.returnResult();
}
日志记录:
由于插入数据肯定会因为数据存在问题,或者文件类型以及文件名存在问题而导致插入不成功,所以将这几种异常情况需要处理并且记录到日志文件中
- 情况1:数据文件中,数据库需要的主键列没有,则这个数据插入肯定失败,然后将插入日志写入数据库,文件不转移,并且继续执行下一个文件的数据插入
if (map.get("routeid") == null) { dataImportLog(0, 0, "Missing primary key columns", fileName); continue continueOut; }
- 情况1:数据文件中,某一条数据没有数据,或者数据库不够,没有找到主键对应的数据,则这个数据插入失败,然后将插入日志写入数据库(同时记录在哪一行失败的),文件不转移,并且继续执行下一个文件的数据插入
if (split.get(map.get("routeid")) == null) { dataImportLog(0, line, "The " + line + " row primary key does not exist.", fileName); continue continueOut; }
- 情况3:尽管在讲数据放入list时做了异常情况处理,但是还是多加一个catch来补货异常
} catch (Exception e) { dataImportLog(0, 0, "The "+line+" row data has problems.", fileName); continue continueOut; }
- 情况4:在将已经封装好的list数据插入数据库中也可能存在异常
try { routezonestest.insertDataBatch(routeZonesTestList); } catch (Exception e) { dataImportLog(0, line+1, "Data insertion failed. ", fileName); continue; }
- 情况5:没有找到对应的数据库
dataImportLog(0, line, "The filename does not correspond to the database name.", fileName); continue;
- 情况6:目标不是文件,而是文件夹
dataImportLog(0, 0, "The target is a folder, not a file", fileName);
- 情况7::移动文件失败(失败的原因有很多,具体见RemoveFile方法)
dataImportLog(0, line, removeFile.getMsg(), fileName);
批量插入sql
<insert id="insertDataBatch" parameterType="java.util.List" >
insert into ROUTE_ZONES_TEST2 (ROUTEID, ROAD_NAME, SRC_LAT, SRC_LON, DEST_LAT, DEST_LON )
values
<foreach collection="list" item="bean" separator=",">
(#{bean.routeid},#{bean.roadName},#{bean.srcLat},#{bean.srcLon},#{bean.destLat},#{bean.destLon})
</foreach>
</insert>