sqlloader导入数据

适用场景:oracle数据库需要导入大量的数据，数据源来自txt文件。
Sqlloader.java
  1 import java.io.BufferedReader;
  2 import java.io.BufferedWriter;
  3 import java.io.File;
  4 import java.io.FileNotFoundException;
  5 import java.io.FileOutputStream;
  6 import java.io.InputStreamReader;
  7 import java.io.OutputStreamWriter;
  8 import java.io.UnsupportedEncodingException;
  9 
 10 public class SqlLoad {
 11     //数据库配置信息
 12     private static String dbconnStr="bxw/root@ORCL";
 13     
 14     /** java运行dos命令将txt文件下的数据导入到oracle
 15      * 没有ctlfile
 16      * @param args
 17      */
 18     public void sqlldr (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      * 有ctlfile
 34      * @param ctl_path
 35      */
 36     public void sqlldr (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      * @param infile:有数据的txt文档
 51      * @param tablename：要导入的oracle数据表
 52      * @param split_flag:导入的数据以何种标志分割字段
 53      * @param ctl文件的最终存放路径
 54      */
 55     public String 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=new File(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 = new StringBuffer();
 81         sb.append("load data");
 82         sb.append("
");
 83         sb.append("infile ");
 84         sb.append("'");
 85         sb.append(infile);
 86         sb.append("'");
 87         sb.append("
");
 88         sb.append("append into table ");
 89         sb.append(tablename);
 90         sb.append("
");
 91         sb.append("fields terminated by ");
 92         sb.append("'");
 93         sb.append(split_flag);
 94         sb.append("'");
 95         sb.append("
");
 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"+"
"+
101                 "infile "+"'"+infile+"'"+"
"+
102                 "append into table "+tablename+"
"+
103                 "fields terminated by "+"'"+split_flag+"'"+"
"+
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(new FileOutputStream(file));
112             BufferedWriter writer = new java.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         return ctlfile;
125     }
126     
127     public static void main(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=new SqlLoad();
133         loader.sqlldr(infile, tablename, split_flag, ctlfile);
134         //loader.sqlldr(ctlfile);
135     }
136 
137 }