java调用sqlldr导入csv文件数据到临时表(代码实现)

最新推荐文章于 2023-03-19 02:05:09 发布

原创最新推荐文章于 2023-03-19 02:05:09 发布 · 4.6k 阅读

4 ·

CC 4.0 BY-SA版权

Oracle开发专栏收录该内容

38 篇文章

订阅专栏

java代码：

package cn.com.file;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;

public class Client {
     public static void main(String args[]) throws FileNotFoundException
     {
          String path = "E:\\PORT";
          String templateFile = "1.ctl";
          String fileLists = "index.txt";
          String commandText = "sqlldr.exe userid=webrescmxj/res4cmxj@resdb control=e:/PORT/tmp.ctl log=e:/PORT/1.log";

          // 1.获取模板SQL
          BufferedReader tReader = new BufferedReader(new FileReader(path + "\\" + templateFile));
          StringBuffer buffer = new StringBuffer(); //记录模板SQL
          String str = null;

          try {
               while((str = tReader.readLine())!=null){
                    buffer.append(str);
                  }
          } catch (IOException e) {
               e.printStackTrace();
          }
          //System.out.println(buffer.toString()); /* 测试打印使用 */

          //2.拼装导入SQL
          BufferedReader lsReader = new BufferedReader(new FileReader(path + "\\" + fileLists));
          String tmpSQL = null; //记录模板SQL
          str = null; //初始化

          try {
               while ((str = lsReader.readLine())!=null) {
               tmpSQL = buffer.toString().replace("DEFAULT", path + "\\" + str);
               System.out.println(tmpSQL);

               //3. 将拼装好的SQL写入临时文件
               File f = new File(path + "\\" + "tmp.ctl");
               BufferedWriter writer = new BufferedWriter(new FileWriter(f));
               writer.write(tmpSQL);
               writer.flush();
               writer.close(); //关闭写出

               Process proc = Runtime.getRuntime().exec(commandText); // 执行SQLLDR操作
               //proc.waitFor();
               }
          } catch (IOException e) {
               e.printStackTrace();
          }
     }
}

CTL文件内容:

load data infile 'PORT-20140307203455.csv'
append into table TRANS_PORT
(
   CUID char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   LABEL_CN char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   RELATED_CARD char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   CARD_CUID char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   NE_NAME char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   RELATED_NE_CUID char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   EMS_NAME char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   RELATED_EMS_CUID char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   RATE char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   PORT_STATE char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   PORT_NO char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   PORT_TYPE char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   DOMAIN char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   MSTP_PORT_TYPE char terminated by ',' OPTIONALLY ENCLOSED BY '"',
   CURRENTWAVENUM char terminated by ',' OPTIONALLY ENCLOSED BY '"',

   WAVE_RATE char terminated by whitespace OPTIONALLY ENCLOSED BY '"')