Using Oracle's client load data and Sqlldr command in Java to import data into the database

Test the code in Windows environment:

ImportJava.io.File;Importjava.io.FileNotFoundException;ImportJava.io.FileWriter;Importjava.io.IOException;ImportJava.io.InputStream;ImportJava.io.PrintWriter;ImportJava.util.Scanner; Public classTesttimer {/**     * @paramargs*/     Public Static voidMain (string[] args) {//TODO auto-generated Method Stub//./szrpp_files/clpath//write control file. CTLString Fileroute = "d:\\files\\";//File Address pathString fileName = "Db.txt";//Data file nameString tableName = "TestTable";//Table nameString fieldName = "(Source,file,date,filenum,rechargeserial,status)";//fields to write to the tableString ctlfilename = "Ctlname.ctl";//Control File nameStlfilewriter (fileroute,filename,tablename,fieldname,ctlfilename); //DOS command to executeString user = "SCOTT"; String PSW= "123456"; String Database= "ORCL"; String LogFileName= "LogName.log";            Executive (User,psw,database,fileroute,ctlfilename,logfilename); }        /*** * Write control file. CTL *@paramfileroute Data File address path *@paramfilename Data file name *@paramtableName Table name *@paramfieldName fields to write to the table *@paramctlfilename Control File name*/     Public Static voidstlfilewriter (String fileroute,string filename,string tablename,string fieldname,string ctlfileName) {Fil Ewriter FW=NULL; String Strctl= "OPTIONS (skip=0)" + "LOAD DATA INFILE" "+fileroute+" "+filename+" "+" APPEND into TABLE "+tablename+" " + "Fields TERMINATED by ', '" + "optionally enclosed by \" "\" "+" TRAILING nullcols "+fieldname+" "; Try{FW=NewFileWriter (fileroute+ "" +ctlfilename);         Fw.write (STRCTL); }          Catch(IOException e) {e.printstacktrace (); }        finally {            Try{Fw.flush ();            Fw.close (); }             Catch(IOException e) {e.printstacktrace (); }                    }    }        /*** Call System DOS command *@paramUser *@paramPSW *@paramDatabase *@paramfileroute File path *@paramctlfilename Control File name *@paramlogfilename log file name*/     Public Static voidExecutive (String user,string psw,string database,string fileroute,string ctlfilename,string logfilename) { InputStream ins=NULL; //DOS command to executeString dos= "Sqlldr" +user+ "/" +psw+ "@" +database+ "control=" +fileroute+ "" +ctlfilename+ "log=" +fileroute+ "" +LogFileName; string[] cmd=Newstring[] {"Cmd.exe", "/C", DOS};//Command        Try{Process Process=runtime.getruntime (). exec (CMD); INS= Process.getinputstream ();//gets the information after executing the cmd command//BufferedReader reader = new BufferedReader (new InputStreamReader (INS));//String line = null;//While (line = Reader.readline ()) = null)//            {//string msg = new String (Line.getbytes ("iso-8859-1"), "UTF-8");//System.out.println (msg);//Output//            }            intExitvalue =process.waitfor (); if(exitvalue==0) {System.out.println ("Return value:" + exitvalue+ "\ n Data import succeeded"); }Else{System.out.println ("Return value:" + exitvalue+ "\ n Data import Failed"); } process.getoutputstream (). Close (); //Close        }        Catch(Exception e) {e.printstacktrace (); }    }}

View Code

Test code in the Linux environment:

ImportJava.io.FileWriter;Importjava.io.IOException;ImportJava.io.InputStream; Public classTestlinux {/**     * @paramargs*/     Public Static voidMain (string[] args) {//TODO auto-generated Method Stub//./szrpp_files/clpath//write control file. CTLString Fileroute= "/home/test/files/clpath/";//File Address pathString fileName = "Db.txt";//Data file nameString tableName = "TestTable";//Table nameString fieldName = "(Source,file,date,filenum,status)";//fields to write to the tableString ctlfilename = "Ctltest.ctl";//Control File nameStlfilewriter (fileroute,filename,tablename,fieldname,ctlfilename); //DOS command to executeString user = "Scott"; String PSW= "123456"; String Database= "ORCL"; String LogFileName= "Logtext.log";    Executive (User,psw,database,fileroute,ctlfilename,logfilename); }        /*** * Write control file. CTL *@paramfileroute Data File address path *@paramfilename Data file name *@paramtableName Table name *@paramfieldName fields to write to the table *@paramctlfilename Control File name*/     Public Static voidstlfilewriter (String fileroute,string filename,string tablename,string fieldname,string ctlfileName) {File Writer FW=NULL; String Strctl= "OPTIONS (skip=0)" + "LOAD DATA INFILE" "+fileroute+" "+filename+" "+" APPEND into TABLE "+tablename+" " + "Fields TERMINATED by ', '" + "optionally enclosed by \" "\" "+" TRAILING nullcols "+fieldname+" "; Try{FW=NewFileWriter (fileroute+ "" +ctlfilename);         Fw.write (STRCTL); }          Catch(IOException e) {e.printstacktrace (); }        finally {            Try{Fw.flush ();            Fw.close (); }             Catch(IOException e) {e.printstacktrace (); }                    }    }        /*** Call System DOS command *@paramUser *@paramPSW *@paramDatabase *@paramfileroute File path *@paramctlfilename Control File name *@paramlogfilename log file name*/     Public Static voidExecutive (String user,string psw,string database,string fileroute,string ctlfilename,string logfilename) { InputStream ins=NULL; //DOS command to executeString dos= "Sqlldr" +user+ "/" +psw+ "@" +database+ "control=" +fileroute+ "" +ctlfilename+ "log=" +fileroute+ "" +LogFileName; //commented out in the Linux environment//string[] cmd = new string[]//{"cmd.exe", "/C", DOS};//Windows environment Commands        Try{Process Process=runtime.getruntime (). exec (Dos); INS= Process.getinputstream ();//gets the information after executing the cmd command//BufferedReader reader = new BufferedReader (new InputStreamReader (INS));//String line = null;//While (line = Reader.readline ()) = null)//            {//string msg = new String (Line.getbytes ("iso-8859-1"), "UTF-8");//System.out.println (msg);//Output//            }            intExitvalue =process.waitfor (); if(exitvalue==0) {System.out.println ("Return value:" + exitvalue+ "\ n Data import succeeded"); }Else{System.out.println ("Return value:" + exitvalue+ "\ n Data import Failed"); } process.getoutputstream (). Close (); //Close        }        Catch(Exception e) {e.printstacktrace (); }    }}

View Code

Special attention:

1. What server is your program deployed under the Oracle database server under the database connection configuration address can not be specified, if the deployment of the other server is not under the database server under the database name of the connection address to point to the explicit database server IP address, this self can be many times test test will know.

2. The test program just realizes the function, but put in your project program must be a lot of testing nature will find the problem, this test code in your project when you must pay attention to some code transformation, specific self-test to find. Can be simply explained under the executive () This method inside the code as long as a little transformation is good, their own groping under the understanding.

3. Control file path This is best used when referencing to a project: control= "+clpath+file.separator+ctlfilename+" log= "+clpath+file.separator+logfilename

4. For this code specifically describes the following

String strctl = "OPTIONS (skip=0)" +   //  0 is starting from the first line  1 is from the second row        "LOAD DATA INFILE '" +fileroute+ "" +filename+ "'" +        // overwrite write        "Fields TERMINATED by ', '" +  //--data for each line of record with "," delimited , terminated is used to control the delimiter of a field, which can be multiple characters.         "Optionally  enclosed by \" ' \ "" +  // source file with quotation marks ", remove            " "" TRAILING nullcols "+fieldname+" "; // --The table field does not have a corresponding value when NULL is allowed for the  source data does not correspond, write null

　　

Using Oracle's client load data and Sqlldr command in Java to import data into the database