java 将csv文件数据加载到oracle数据库中

loading csv file data into oracle database

提问by adityak

I am trying to load a csv file in oracle database using sql loader through java program. I have successfully executed it by run command, but i want to load csv file data in database through a java program. My programs are:

我正在尝试使用 sql loader 通过 java 程序在 oracle 数据库中加载 csv 文件。我已通过运行命令成功执行它，但我想通过 java 程序将 csv 文件数据加载到数据库中。我的程序是：

1. loadCsv.csv:

   ID,firstName,LastName,Address
   1,aditya,kumar,gaya
   2,abhijeet,chanda,kol
   3,Rahul,Jordar,kol
   

2. trial.ctl:

   LOAD DATA
   INFILE loadCsv.csv
   BADFILE trial.bad
   DISCARDFILE trial.dsc
   APPEND 
   INTO TABLE load1
   FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "”"
   (x,y,z,t)
   

3. SqlLoaderTest.java:

   public class SqlLoaderTest
   {
       public static void main(String[] args) {
           try {
               String sqlldrCmd = "sqlldr control=E:\load_data\trial.ctl"+
                  "LOG=trial.log "+
                  "DATA=E:\load_data\loadCsv.csv USERID=vehere/adi"+
                  "BAD=E:\load_data\trial.bad";
               System.out.println("SQLLDR Started ....... ");
               Runtime rt = Runtime.getRuntime();
               Process proc = rt.exec(sqlldrCmd);
               System.out.println("SQLLDR Ended ........  ");
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   }
   

1. 加载CSV.csv：

   ID,firstName,LastName,Address
   1,aditya,kumar,gaya
   2,abhijeet,chanda,kol
   3,Rahul,Jordar,kol
   

2. 试用.ctl：

   LOAD DATA
   INFILE loadCsv.csv
   BADFILE trial.bad
   DISCARDFILE trial.dsc
   APPEND 
   INTO TABLE load1
   FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "”"
   (x,y,z,t)
   

3. SqlLoaderTest.java:

   public class SqlLoaderTest
   {
       public static void main(String[] args) {
           try {
               String sqlldrCmd = "sqlldr control=E:\load_data\trial.ctl"+
                  "LOG=trial.log "+
                  "DATA=E:\load_data\loadCsv.csv USERID=vehere/adi"+
                  "BAD=E:\load_data\trial.bad";
               System.out.println("SQLLDR Started ....... ");
               Runtime rt = Runtime.getRuntime();
               Process proc = rt.exec(sqlldrCmd);
               System.out.println("SQLLDR Ended ........  ");
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   }
   

It is compiled and run successfully but not inserting any data in database. your suggestion is highly appreciated.Thanx in advance.

它已成功编译并运行，但未在数据库中插入任何数据。非常感谢您的建议。提前谢谢。

采纳答案by adityak

I got my error. The right code is:

我得到了我的错误。正确的代码是：

  public class SqlLoaderTest
  {
  public static void main(String[] args) {
    try {
        String sqlldrCmd = "sqlldr username/pwd, control=trial.ctl";

        System.out.println("SQLLDR Started ....... ");
        Runtime rt = Runtime.getRuntime();
        Process proc = rt.exec(sqlldrCmd);
        System.out.println("SQLLDR Ended ........  ");
      } catch (Exception e) {
        e.printStackTrace();
    }
   }
  }

回答by npe

I'm afraid, it does not run successfully, you just do not know what the error is.

恐怕它没有成功运行，您只是不知道错误是什么。

Note that, if the sqlldris executed, but fails do load the file into DB, you will not get any Java excaptions. Instead, the Processwill return a non-zero exitValue, and there will probably be some output on the process' console.

请注意，如果sqlldr执行了，但将文件加载到 DB 失败，您将不会获得任何 Java excaptions。相反，Process将返回一个非零的 exitValue，并且进程的控制台上可能会有一些输出。

What you need to do is to take the InputStreamof your Process, and log it somehow (for example, dump it into the console), so you can see what the sqlldroutput is. Alternatively, redirect output of sqlldrto a file in your sqlldrCmdstring.

你需要做的是获取InputStream你的Process，并以某种方式记录它（例如，将它转储到控制台），这样你就可以看到sqlldr输出是什么。或者，将 的输出重定向sqlldr到sqlldrCmd字符串中的文件。