Use the Oracle client load data and sqlldr commands in Java to execute data import to the database, oraclesqlldr

Use the Oracle client load data and sqlldr commands in Java to execute data import to the database, oraclesqlldr

Test code in Windows:

Import java. io. file; import java. io. fileNotFoundException; import java. io. fileWriter; import java. io. IOException; import java. io. inputStream; import java. io. printWriter; import java. util. role; public class testTimer {/*** @ param args */public static void main (String [] args) {// TODO Auto-generated method stub //. /szrpp_files/clPath // write control file. ctl String fileRoute = "D: \ files \"; // file address path String fileN Ame = "db.txt"; // data File name String tableName = "testtable"; // table name String fieldName = "(Source, File, Date, FileNum, RechargeSerial, Status )"; // String ctlfileName = "ctlName. ctl "; // control the file name stlFileWriter (fileRoute, fileName, tableName, fieldName, ctlfileName); // String user =" SCOTT "; String psw =" 123456 "; string Database = "ORCL"; String logfileName = "logName. log "; Executive (user, psw, Database, fil ERoute, ctlfileName, logfileName);}/***** write control file. ctl * @ param fileRoute data file address path * @ param fileName data file name * @ param tableName table name * @ param fieldName field of the table to be written * @ param ctlfileName control file name */public static void stlFileWriter (String fileRoute, string fileName, String tableName, String fieldName, String ctlfileName) {FileWriter fw = null; String strctl = "OPTIONS (skip = 0) "+" load data infile '"+ fileRoute +" "+ FileName + "'" + "append into table" + tableName + "" + "fields terminated ', '"+" optionally enclosed by \ "' \" "+" trailing nullcols "+ fieldName +" "; try {fw = new FileWriter (fileRoute +" "+ ctlfileName ); fw. write (strctl);} catch (IOException e) {e. printStackTrace ();} finally {try {fw. flush (); fw. close ();} catch (IOException e) {e. printStackTrace () ;}}/ *** call the doscommand * @ param user * @ pa Ram psw * @ param Database * @ param fileRoute file path * @ param ctlfileName control file name * @ param logfileName log file name */public static void Executive (String user, String psw, String Database, string fileRoute, String ctlfileName, String logfileName) {InputStream ins = null; // The doscommand String dos = "sqlldr" + user + "/" + psw + "@" + Database + "control =" + fileRoute + "" + ctlfileName +" log = "+ fileRoute +" "+ logfileName; string [] cmd = New String [] {"cmd.exe", "/C", dos}; // command try {Process process = runtime.getruntime(cmd.exe c (cmd); ins = process. getInputStream (); // obtain 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 //} int exitValue = process. waitFor (); if (exitValue = 0) {System. out. println ("Return Value:" + exitValue + "\ n data imported successfully");} 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 Linux:

Import java. io. fileWriter; import java. io. IOException; import java. io. inputStream; public class TestLinux {/*** @ param args */public static void main (String [] args) {// TODO Auto-generated method stub //. /szrpp_files/clPath // write control file. ctl String fileRoute = "/home/test/files/clPath/"; // file address path String fileName = "db.txt"; // data file name String tableName = "testtable "; // table name String fieldName = "(Source, File, Date, FileNum, Status) "; // String ctlfileName =" ctltest. ctl "; // control the file name stlFileWriter (fileRoute, fileName, tableName, fieldName, ctlfileName); // String user =" scott "; String psw =" 123456 "; string Database = "ORCL"; String logfileName = "logtext. log "; Executive (user, psw, Database, fileRoute, ctlfileName, logfileName);}/***** write control file. ctl * @ param fileRoute data file address path * @ param fileName data file name * @ par Am tableName table name * @ param fieldName field of the table to be written * @ param ctlfileName control file name */public static void stlFileWriter (String fileRoute, String fileName, String tableName, String fieldName, string ctlfileName) {FileWriter fw = null; String strctl = "OPTIONS (skip = 0) "+" load data infile '"+ fileRoute +" "+ fileName +"' "+" append into table "+ tableName +" "+" fields terminated ', '"+" optionally enclosed \"'\ "" + "Trailing nullcols" + fieldName + ""; try {fw = new FileWriter (fileRoute + "" + ctlfileName); fw. write (strctl);} catch (IOException e) {e. printStackTrace ();} finally {try {fw. flush (); fw. close ();} catch (IOException e) {e. printStackTrace ();}}} /*** call the system doscommand * @ param user * @ param psw * @ param Database * @ param fileRoute file path * @ param ctlfileName control file name * @ param logfileName log file name */publ Ic static void Executive (String user, String psw, String Database, String fileRoute, String ctlfileName, String logfileName) {InputStream ins = null; // The doscommand String dos = "sqlldr" + user + "/" + psw + "@" + Database + "control =" + fileRoute + "" + ctlfileName +" log = "+ fileRoute +" "+ logfileName; // comment out // String [] cmd = new String [] // {"cmd.exe", "/C", dos} in Linux }; // Windows Environment command try {Process process = Runtime. getR Untime(cmd.exe c (dos); ins = process. getInputStream (); // obtain 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 //} int exitValue = process. waitFor (); if (exitValue = 0) {System. out. println ("Return Value:" + exitValue + "\ n data imported successfully");} else {System. out. println ("Return Value:" + exitValue + "\ n data import failed");} process. getOutputStream (). close (); // close} catch (Exception e) {e. printStackTrace ();}}}View Code

Note:

1. the database connection configuration address under which your program is deployed is not specified under the Oracle Database Server, if the connection address of the database name deployed on other servers and not under the database server directs to a specific IP address of the database server, you can test it multiple times.

2. the test program only implements the function, but you must test more in your project to find problems. When you put the test code in your project, you must pay attention to some code transformation, you can test it on your own. You can simply explain the code in the Executive () method as long as it is modified a little, and you will understand it by yourself.

3. It is best to control the File path in this way: control = "+ clPath + File. separator + ctlfileName +" log = "+ clPath + File. separator + logfileName when referenced to the Project

4. specific descriptions of this Code Section

 

String strctl = "OPTIONS (skip = 0) "+ // 0 is from the first line. 1 is from the second line." load data infile' "+ fileRoute +" "+ fileName +" '"+" append into table "+ tableName + "" + // overwrite "fields terminated ', '"+ // -- records in each row in the data are separated by", ". TERMINATED is the delimiter used to control fields and can contain multiple characters. "Optionally enclosed by \" '\ "" + // The source file has quotation marks ''. Here, the ''''" "trailing nullcols" + fieldName + "is removed ""; // -- when the table field does not have a corresponding value, it is allowed to be empty. The source data does not have a corresponding value. Write null