oracle的sqlload遇到超长,oracle sqlload 导入数据

1 public classOracleLoadDataHelperUntil {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 publicOracleLoadDataHelperUntil() {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 *@paramtableName37 *@return

38 *@throwsException39 */

40 private static List getFieldsByTableName(String tableName) throwsException {41 List 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 returnfields;54 }55

56 /**

57 * 获取控制命名字符串58 *59 *@paramfileName 导入的文件名，全路径60 *@paramtableName 表名61 *@paramdelimiter 分割符62 *@return

63 */

64 private static String getStrCtr(String fileName,String tableName,String delimiter,Listfileds) {65 StringBuffer sb = newStringBuffer();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 returnsb.toString();92 }93

94

95 private static String getFieldsStr(Listfileds) {96 StringJoiner sj = new StringJoiner("," + System.getProperty("line.separator"));97 for(String s : fileds) {98 sj.add(s);99 }100 returnsj.toString();101 }102

103 /**

104 * 获取控制文件的路径105 *106 *@paramfileRoute 数据文件地址路径107 *@paramfileName 数据文件名108 *@paramtableName 表名109 *@paramdelimiter 分隔符110 *@paramfieldNames 属性集合111 *@paramctlfileName 控制文件名112 *@return

113 */

114 private staticString getCtlFileUrl(String fileRoute,String fileName,115 ListfieldNames,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 returnctlFileUrl;134 }135

136 /**

137 * 判断是什么系统，true 为windows，false为linux138 *139 *@return

140 */

141 private staticBoolean 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 returnflag;148 }149

150 /**

151 *152 *@paramfileRoute 文件路径153 *@paramctlfileName 控制文件路径154 *@paramlogfileName 日志文件路径155 *@paramflag 是否立即执行，true执行命令，false不执行156 *@returnsqlload的执行语句157 */

158 private static String excuteLoadData(String fileRoute,String ctlfileName,String logfileName,booleanflag) {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 returndos;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(newInputStreamReader(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 returndos;194 }195

196 /**

197 *198 * 执行sqlload数据导入方法199 *200 *@paramfilePath 文件根路径201 *@paramfileName 数据文件 .dat文件202 *@paramtableName 表名203 *@paramfieldNames 属性字段204 *@paramflag 是否立即执行，true执行命令，false不执行205 *@returnsqlload的执行语句206 *@throwsException207 */

208

209 public static String excutesqlLoadImportData(String filePath,List fields,boolean flag) throwsException {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 returnstr;224 }225

226 }