最近再做项目的时候遇到了一个问题,poi导入excle数据太慢(数据量比较大),不得不另寻他法,在网上找了找,发现oracle 自带的sqlLoad方法导入数据量比较大的文件的时候真的很快,所以就用这种方式试着做了一下,一下是代码:
java接收文件的代码:
/**
* 数据导入的方法
*
* @param request
* @param response
* @return
*/
public ModelAndView importStore(HttpServletRequest request,
HttpServletResponse response) throws Exception {
ModelAndView mav = new ModelAndView(urlMap.get("importIndex"));
request.setCharacterEncoding("UTF-8");
EControllerHelper helper = new EControllerHelper(request);
FileUtils utils = new FileUtils(request);
String typeId = ""; // 导入类型
String[] allowedExt = null;
List<CommonsMultipartFile> list = utils.getCommonsMultipartFiles();
if (list == null || list.size() == 0) {
helper.setActionMessage("请选择所要导入文件!", ActionMessage.ERROR);
mav = new ModelAndView(urlMap.get("success"));
return mav;
} else {
// 得到所有上传的文件
for (int i = 0; i < list.size(); i++) {
String path = null;
long size = 0;
CommonsMultipartFile file = (CommonsMultipartFile) list.get(i);
// 得到文件的大小
size = file.getSize();
// 得到去除路径的文件名
String t_name = file.getFileItem().getName();
// 得到文件的扩展名(无扩展名时将得到全名)
String t_ext = t_name.substring(t_name.lastIndexOf(".") + 1);
typeId = file.getFileItem().getFieldName();
if (typeId.equals("4") || typeId.equals("5")) { // 处理导入 execl
allowedExt = new String[] { "xls", "xlsx" }; // 允许上传的文件格式的列表
} else {
allowedExt = new String[] { "txt" }; // 允许上传的文件格式的列表
}
int allowFlag = 0;
int allowedExtCount = allowedExt.length;
for (; allowFlag < allowedExtCount; allowFlag++) {
if (allowedExt[allowFlag].equals(t_ext))
break;
}
if (allowFlag == allowedExtCount) {
String fileAllow = "";
for (allowFlag = 0; allowFlag < allowedExtCount; allowFlag++) {
fileAllow += "*." + allowedExt[allowFlag] + " ";
}
helper.setActionMessage("请上传此类型的文件 " + fileAllow,
ActionMessage.ERROR);
mav = new ModelAndView(urlMap.get("success"));
return mav;
}
long now = System.currentTimeMillis() + i;
// 根据系统时间生成上传后保存的文件名
String prefix = String.valueOf(now);
// 创建文件夹,每天创建一个
File dirFile = null;
String dirName = new SimpleDateFormat("yyyy-MM-dd")
.format(new Date());
dirFile = new File("D:\\Uploaded/" + dirName);
// dirFile = new File(this.getServletContext().getRealPath("/")
// + "Uploaded/" + dirName);
if (!(dirFile.exists()) && !(dirFile.isDirectory())) {
boolean creadok = dirFile.mkdirs();
if (creadok) {
System.out.println(" ok:创建文件夹成功! ");
} else {
System.out.println(" err:创建文件夹失败! ");
}
}
// 保存的最终文件完整路径,保存在web根目录下的Uploaded目录下
String u_name = "D:\\Uploaded\\" + dirName + "\\" + prefix + "." + t_ext;
// String u_name = this.getServletContext().getRealPath("/")
// + "Uploaded\\" + dirName + "\\" + prefix + "." + t_ext;
String ctlPth = "D:\\Uploaded\\" + dirName + "\\" + prefix;
// String ctlPth = this.getServletContext().getRealPath("/")+ "Uploaded\\" + dirName + "\\" + prefix;
String ctlFile = SqlLoad.createloadctl(u_name,"knowledge_table".toUpperCase(),",",ctlPth+".ctl",ctlPth);
// 下载路径
String downloadUrl = this.getServletContext().getRealPath("/")
+ "Uploaded\\" + dirName + "\\";
try {
// 保存文件
file.getFileItem().write(new File(u_name));
} catch (Exception e) {
e.printStackTrace();
}
Runtime run = Runtime.getRuntime();
/**
* ************************** 目标用户入库
* *****************************
*/
// String strCmd = "cmd /c sqlldr 数据库用户名/数据库密码@172ORCL control="+ctlFile+" log="+ctlFile.substring(0, ctlFile.lastIndexOf(".") + 1)+"log errors=100160 rows=100160 readsize=20971520 bindsize=20971520 PARALLEL=TRUE"; // 执行的命令
// System.out.println(strCmd);
System.out.println("目标用户导入执行开始。。。。");
// Process process = run.exec(strCmd); // 执行命令开始导入
// String endexec = SqlLoad.readInputFromProcess(process); // 等待导入完成程序
SqlLoad.sqlldr(u_name, "knowledge_table".toUpperCase(), " ", ctlFile,null);
System.out.println("目标用户导入执行完成。。。。");
helper.setActionMessage("导入完成。", ActionMessage.INFO);
}
}
return mav;
}
创建ctl文件的代码:
/**
* 创建ctl文件
*
* @param infile:有数据的txt文档
* @param tablename:要导入的oracle数据表
* @param split_flag:导入的数据以何种标志分割字段
* @param ctl文件的最终存放路径
*/
public static String createloadctl(String infile, String tablename,String split_flag, String ctlfile,String path) {
if (tablename == "" || tablename == null) {
System.out.println("数据库名称不能为空!!");
}
if (ctlfile == "" || ctlfile == null) {
ctlfile = path + ".ctl";
System.out.println("路径:" + ctlfile);
}
File out_file = new File(ctlfile);
try {
if (out_file.exists()) {
out_file.delete();
} else {
out_file.createNewFile();
}
} catch (Exception ex) {
ex.printStackTrace();
}
// String ctlstr="LOAD DATA"+"\n"+
// "INFILE "+"'"+infile+"'"+"\n"+
// "INTO TABLE "+tablename.toUpperCase()+"\n"+
// "replace \n"+
// "fields terminated by "+"','"+"\n"+
// "(AAA,BBB)";
String ctlstr = "LOAD DATA" + "\n" + "INFILE " + "'" + infile + "'"
+ "\n" + "into table " + tablename + "\n" + "append \n"
+ "fields terminated by " + "','" + "\n"
+ "TRAILING NULLCOLS \n" + "(KNOW_TITLE,KNOW_FLAG,KNOW_ZYBS,KNOW_CONTENT,KNOW_REMAEK)";
System.out.println(ctlstr);
try {
// 将数据库数据写入指定的txt文件
File file = new File(ctlfile); // 自定义文件路径
OutputStreamWriter write;
// write = new OutputStreamWriter(new
// FileOutputStream(file),"UTF-8");
write = new OutputStreamWriter(new FileOutputStream(file));
BufferedWriter writer = new java.io.BufferedWriter(write);
writer.write(ctlstr); // 输出流写入到文件中
writer.close();
write.close();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
}
return ctlfile;
}
经测试,此方法在导入6百万条数据的时候花费时间越为20秒左右。
当然鱼与熊掌不可兼得,sqlLoad只能导入txt类型的文件,同时在导入的时候程序是不能获取到导入时有没有错误发生的,只能通过日志查看,日志的路径配置如下:
,配置日志的完整路径,导入的结果会在日志中展示。