java sql loader_sqlloader导入数据

1 importjava.io.BufferedReader;2 importjava.io.BufferedWriter;3 importjava.io.File;4 importjava.io.FileNotFoundException;5 importjava.io.FileOutputStream;6 importjava.io.InputStreamReader;7 importjava.io.OutputStreamWriter;8 importjava.io.UnsupportedEncodingException;9

10 public classSqlLoad {11 //数据库配置信息

12 private static String dbconnStr="bxw/root@ORCL";13

14 /**java运行dos命令将txt文件下的数据导入到oracle15 * 没有ctlfile16 *@paramargs17 */

18 public voidsqlldr (String infile,String tablename,String split_flag,String ctlfile){19 Runtime rt=Runtime.getRuntime();20 Process proc;21 String ctl_path=createloadctl(infile,tablename,split_flag,ctlfile);22 String log_path=ctl_path.substring(0,ctl_path.indexOf(".")+1)+"log";23 String cmdstr="sqlldr "+dbconnStr+" control="+ctl_path+" log="+log_path+" skip=1";24 System.out.println("cmd命令："+cmdstr);25 try{26 proc =rt.exec(cmdstr);27 }catch(Exception e) {28 System.out.println(" error while running sqlldr!");29 }30 }31

32 /**

33 * 有ctlfile34 *@paramctl_path35 */

36 public voidsqlldr (String ctl_path){37 Runtime rt=Runtime.getRuntime();38 Process proc;39 String log_path=ctl_path.substring(0,ctl_path.indexOf(".")+1)+"log";40 String cmdstr="sqlldr "+dbconnStr+" control="+ctl_path+" log="+log_path+" skip=1";41 System.out.println("cmd命令："+cmdstr);42 try{43 proc =rt.exec(cmdstr);44 }catch(Exception e) {45 System.out.println(" error while running sqlldr!");46 }47 }48

49 /**创建ctl文件50 *@paraminfile:有数据的txt文档51 *@paramtablename：要导入的oracle数据表52 *@paramsplit_flag:导入的数据以何种标志分割字段53 *@paramctl文件的最终存放路径54 */

55 publicString createloadctl(String infile,String tablename,String split_flag,String ctlfile){56 if (tablename=="" || tablename==null){57 System.out.println("数据库名称不能为空！！");58 }59

60 if (ctlfile=="" || ctlfile==null){61 String path="F:\\sqlloadTest\\";62 ctlfile=path+"lc_sqlldr.ctl";63 System.out.println("路径："+ctlfile);64 }65

66 File out_file=newFile(ctlfile);67 try{68 if(out_file.exists()){69 out_file.delete();70 }else{71 out_file.createNewFile();72 }73 }catch(Exception ex){74 ex.printStackTrace();75 }76

77 /*

78 * 编写ctlfile的格式79 */

80 StringBuffer sb = newStringBuffer();81 sb.append("load data");82 sb.append("\n");83 sb.append("infile ");84 sb.append("'");85 sb.append(infile);86 sb.append("'");87 sb.append("\n");88 sb.append("append into table ");89 sb.append(tablename);90 sb.append("\n");91 sb.append("fields terminated by ");92 sb.append("'");93 sb.append(split_flag);94 sb.append("'");95 sb.append("\n");96 sb.append("(id,name,price,author,pubishdate DATE\"YYYY-MM-DD HH24:MI:SS\")");97

98 String sbstr=sb.toString();99

100 String ctlstr="load data"+"\n"+

101 "infile "+"'"+infile+"'"+"\n"+

102 "append into table "+tablename+"\n"+

103 "fields terminated by "+"'"+split_flag+"'"+"\n"+

104 "(id,name,price,author,pubishdate DATE\"YYYY-MM-DD HH24:MI:SS\")";105 System.out.println(ctlstr);106 try{107 //将数据库数据写入指定的txt文件

108 File file = new File(ctlfile); //自定义文件路径

109 OutputStreamWriter write;110 //write = new OutputStreamWriter(new FileOutputStream(file),"UTF-8");

111 write = new OutputStreamWriter(newFileOutputStream(file));112 BufferedWriter writer = newjava.io.BufferedWriter(write);113 writer.write(ctlstr); //输出流写入到文件中

114 writer.close();115 write.close();116 }catch(UnsupportedEncodingException e){117 e.printStackTrace();118 }catch(FileNotFoundException e){119 e.printStackTrace();120 }catch(Exception ex){121 ex.printStackTrace();122 }123

124 returnctlfile;125 }126

127 public static voidmain(String[] args) {128 String infile="F:\\sqlloadTest\\data.txt";129 String tablename="book";130 String split_flag="|";131 String ctlfile="F:\\sqlloadTest\\lc_loader.ctl";132 SqlLoad loader=newSqlLoad();133 loader.sqlldr(infile, tablename, split_flag, ctlfile);134 //loader.sqlldr(ctlfile);

135 }136

137 }