oracle sqlload 导入数据_非关系型数据库

概述sqlload导入大量数据确实有大量优势，是一个比较好的工具。 1 public class OracleLoadDataHelperUntil { 2 3 private static String databaseUrl = ProperUtil.getValue("db.properties", "jdbc.url"); 4 5 private
sqlload导入大量数据确实有大量优势，是一个比较好的工具。
  1 public class OracleLoadDataHelperUntil {  2   3     private static String databaseUrl = ProperUtil.getValue("db.propertIEs","jdbc.url");  4   5     private static String user = ProperUtil.getValue("db.propertIEs","jdbc.username");  6   7     private static String pwd = ProperUtil.getValue("db.propertIEs","jdbc.password");  8   9     private static String driver = ProperUtil.getValue("db.propertIEs","jdbc.driver"); 10  11     private static String fileRoute = ""; 12  13     /** 14      * 链接数据库地址 15      */ 16     private static String baseUrl = ""; 17  18     public OracleLoadDataHelperUntil() { 19  20     } 21  22     static { 23         try { 24             String url = databaseUrl.substring(databaseUrl.indexOf("@")); 25             baseUrl = url.replaceAll("@","//").substring(0,url.lastIndexOf(":")+1) + "/" 26                     + url.substring(url.lastIndexOf(":") + 1); 27             Class.forname(driver); 28         } catch (ClassNotFoundException e) { 29             e.printstacktrace(); 30         } 31     } 32  33     /** 34      * 根据表名称获取数据库字段 35      *  36      * @param tablename 37      * @return 38      * @throws Exception 39      */ 40     private static List<String> getFIEldsBytablename(String tablename) throws Exception { 41         List<String> fIElds = Lists.newArrayList(); 42         Connection conn = DriverManager.getConnection(databaseUrl,user,pwd); 43         String sql = "select ColUMN_name from USER_TAB_ColUMNS where table_name=upper(‘" + tablename + "‘)"; 44         Statement stmt = conn.createStatement(); 45         ResultSet rs = stmt.executequery(sql); 46         String filed = ""; 47         while (rs.next()) { 48             filed = rs.getString("ColUMN_name"); 49             fIElds.add(filed); 50         } 51         rs.close(); 52         conn.close(); 53         return fIElds; 54     } 55  56     /** 57      * 获取控制命名字符串 58      *  59      * @param filename  导入的文件名，全路径 60      * @param tablename 表名 61      * @param delimiter 分割符 62      * @return 63      */ 64     private static String getStrCtr(String filename,String tablename,String delimiter,List<String> fileds) { 65         StringBuffer sb = new StringBuffer(); 66         String lineseparator = System.getProperty("line.separator"); 67         // 0是从第一行开始 1是 从第二行 68         sb.append("OPTIONS (skip=0)").append(lineseparator); 69         // 加载数据文件 70         sb.append("LOAD DATA").append(lineseparator); 71         // 防止中文乱码 72         sb.append("CHaraCTERSET ZHS16GBK").append(lineseparator); 73         sb.append("INfile ").append("\‘").append(filename).append("\‘").append(lineseparator); 74         if(Strings.isstringEmpty(delimiter)) { 75             sb.append("\"str X‘170A‘\"").append(lineseparator); 76         } 77         // 覆盖写入 78         sb.append("APPEND INTO table ").append(tablename).append(lineseparator); 79         sb.append("REPLACE").append(lineseparator); 80         // 数据中每行记录用","分隔,TERMINATED用于控制字段的分隔符，可以为多个字符。 81         if(Strings.isstringEmpty(delimiter)) { 82             sb.append("FIELDS TERMINATED BY X‘lib‘").append(lineseparator); 83         }else { 84             sb.append("FIELDS TERMINATED BY ‘"+delimiter+"‘").append(lineseparator); 85         } 86         // 将数据写入对应的字段 87         sb.append("trailing nullcols ( ").append(lineseparator); 88         sb.append(getFIEldsstr(fileds)).append(lineseparator); 89         sb.append(")"); 90  91         return sb.toString(); 92     } 93      94  95     private static String getFIEldsstr(List<String> fileds) { 96         StringJoiner sj = new StringJoiner("," + System.getProperty("line.separator")); 97         for (String s : fileds) { 98             sj.add(s); 99         }100         return sj.toString();101     }102 103     /**104      * 获取控制文件的路径105      * 106      * @param fileRoute   数据文件地址路径107      * @param filename    数据文件名108      * @param tablename   表名109      * @param delimiter   分隔符110      * @param fIEldnames  属性集合111      * @param ctlfilename 控制文件名112      * @return113      */114     private static String getCtlfileUrl(String fileRoute,String filename,115             List<String> fIEldnames,String ctlfilename) {116         String ctlfileUrl = "";117         fileWriter fw = null;118         try {119             String strctl = getStrCtr(fileRoute + filename,tablename,delimiter,fIEldnames);120             fw = new fileWriter(fileRoute + "" + ctlfilename);121             fw.write(strctl);122             ctlfileUrl = fileRoute + "" + ctlfilename;123         } catch (IOException e) {124             e.printstacktrace();125         } finally {126             try {127                 fw.flush();128                 fw.close();129             } catch (IOException e) {130                 e.printstacktrace();131             }132         }133         return ctlfileUrl;134     }135 136     /**137      * 判断是什么系统，true 为windows，false为linux138      * 139      * @return140      */141     private static Boolean isOsWin() {142         Boolean flag = Boolean.FALSE;143         String os = System.getProperty("os.name");144         if (os.tolowerCase().startsWith("win")) {145             flag = Boolean.TRUE;146         }147         return flag;148     }149 150     /**151      * 152      * @param fileRoute   文件路径153      * @param ctlfilename 控制文件路径154      * @param logfilename 日志文件路径155      * @param flag        是否立即执行，true执行命令，false不执行156      * @return sqlload的执行语句157      */158     private static String excuteLoadData(String fileRoute,String ctlfilename,String logfilename,boolean flag) {159         inputStream ins = null;160         Process process = null;161         String dos = "sqlldr " + user + "/" + pwd + "@" + baseUrl + " control=" + fileRoute + "" + ctlfilename + " log="162                 + fileRoute + "" + logfilename;163         System.out.println("dos:" + dos);164         if(flag==false) {165             return dos;166         }167         try {168             if (isOsWin().equals(Boolean.FALSE)) {169                 process = Runtime.getRuntime().exec(dos);170             } else {171                 String[] cmd = new String[] { "cmd.exe","/C",dos };172                 process = Runtime.getRuntime().exec(cmd);173             }174             ins = process.getinputStream();175             Charset charset=Charset.forname("GBK");176             BufferedReader reader = new BufferedReader(new inputStreamReader(ins,charset));177             String line = null;178             while ((line = reader.readline()) != null) {179                 String msg = new String(line.getBytes(),"GBK");180                 System.out.println(line);181             }182             int exitValue = process.waitFor();183             if (exitValue == 0) {184                 System.out.println("返回值：" + exitValue + "\n数据导入成功");185 186             } else {187                 System.out.println("返回值：" + exitValue + "\n数据导入失败");188             }189             process.getoutputStream().close();190         } catch (Exception e) {191             e.printstacktrace();192         }193         return dos;194     }195 196     /**197      * 198      * 执行sqlload数据导入方法199      * 200      * @param filePath 文件根路径201      * @param filename 数据文件 .dat文件202      * @param tablename 表名203      * @param fIEldnames 属性字段204      * @param flag 是否立即执行，true执行命令，false不执行205      * @return sqlload的执行语句206      * @throws Exception 207      */208 209     public static String excutesqlLoadimportData(String filePath,List<String> fIElds,boolean flag) throws Exception {210         String str = "";211         String ctlfilename = tablename + ".ctl";212         String logfilename = tablename + ".log";213         if (Strings.isstringEmpty(filePath)) {214             filePath = fileRoute;215         }216         if(CollectionUtils.isEmpty(fIElds)) {217             fIElds=getFIEldsBytablename(tablename);218         }219         String fileUrl = getCtlfileUrl(filePath,filename,fIElds,ctlfilename);220         if (!Strings.isstringEmpty(fileUrl)) {221             str = excuteLoadData(filePath,ctlfilename,logfilename,flag);222         }223         return str;224     }225 226 }