oracle使用sqlLoad实现超大数据量导入

最近再做项目的时候遇到了一个问题，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")) { // 处理导入 execlallowedExt = 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类型的文件，同时在导入的时候程序是不能获取到导入时有没有错误发生的，只能通过日志查看，日志的路径配置如下：

，配置日志的完整路径，导入的结果会在日志中展示。

sqlLoad导入数据的小示例下载