JAVA代码调用SQLLDR

 

java代码调用sqlldr

/**
 * <b>文件名：</b>TestSqlldrDemo.java<br>
 * <b>描述：</b><br>
 * <b>作者：</b>FishRoad<br>
 * <b>时间：</b>2019年12月5日 上午11:36:47<br>
 * Copyright(c) 2019 Joyin Tech,LTD. All Rights Reserved.
 */
package ZRQ;

/**
 * <b>类名：</b>TestSqlldrDemo<br>
 * <b>描述：</b><br>
 * <b>作者：</b>FishRoad<br>
 * <b>时间：</b>2019年12月5日 上午11:36:47<br>
 */

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

public class TestSqlldrDemo {

	public static final String ctlFileDir = "F:\\test\\";
	public static final String logFileDir = "F:\\test\\";

	public static void main(String[] args) {

		String username = "FJHX";
		String password = "FJHX";
		String Database = "@ORCL";
		String ctlFileName = "11.ctl";
		String logFileName = "11.log";
		boolean isDBA = false;

		String command = buildCommand(username, password, Database, isDBA,
				TestSqlldrDemo.ctlFileDir, ctlFileName,
				TestSqlldrDemo.logFileDir, logFileName);

		Executive(command);
	}

	/**
	 * @param username
	 * @param password
	 * @param Database
	 * @param isDBA
	 * @param ctlFileDir
	 * @param ctlFileName
	 * @param logFileDir
	 * @param logFileName
	 * @return the sql loader command
	 */
	public static String buildCommand(String username, String password,
			String Database, boolean isDBA, String ctlFileDir,
			String ctlFileName, String logFileDir, String logFileName) {

		StringBuffer command = new StringBuffer();
		command.append("sqlldr ");
		command.append(isDBA ? "'" : "");
		command.append(username);
		command.append("/");
		command.append(password);
		command.append(Database);
		command.append(isDBA ? " as sysdba'" : "");
		command.append(" control=" + ctlFileDir + ctlFileName);
		command.append(" data=" + ctlFileDir + "11.txt");
		command.append(" log=" + logFileDir + logFileName);

		System.out.println("Command : " + command.toString());
		// command : sqlldr 'SYS/123@ORCL as sysdba' control=C:\test.ctl
		// log=C:\test_table.log
		return command.toString();

	}

	/**
	 * To call DOS command
	 * 
	 * @throws IOException
	 * @throws InterruptedException
	 */
	public static void Executive(String command) {

		// 获取子进程的输入流
		InputStream ins = null;
		// 获取子进程的错误流
		InputStream inserr = null;

		String[] cmd = new String[] { "cmd.exe", "/C", command }; // 命令
		try {
			Process process = Runtime.getRuntime().exec(cmd);
			inserr = process.getErrorStream(); // 获取执行cmd命令后的错误信息

			BufferedReader reader = new BufferedReader(new InputStreamReader(
					inserr, "ISO-8859-1"));
			String line = null;
			while ((line = reader.readLine()) != null) {
				String msg = new String(line.getBytes("ISO-8859-1"), "GBK");
				System.out.println("执行命令错误：" + msg); // 输出
			}

			ins = process.getInputStream(); // 获取执行cmd命令后的信息

			reader = new BufferedReader(
					new InputStreamReader(ins, "ISO-8859-1"));
			while ((line = reader.readLine()) != null) {
				String msg = new String(line.getBytes("ISO-8859-1"), "GBK");
				System.out.println(msg); // 输出
			}

			int exitValue = process.waitFor();

			System.out.println("Returned value was：" + exitValue);
			process.destroy();

			if (exitValue == 0) {
				System.out.println("导入成功!");
			} else {
				System.out.println("导入失败！");
			}

			process.getOutputStream().close(); // 关闭
			ins.close();
			inserr.close();
		} catch (Exception e) {
			System.out.println(e.toString());
			e.printStackTrace();
		}
	}

}

11.txt 的内容如下：

id|name|text|remark|
12|qqqq|hahahah|www|
13|www|hahahah|www|
14|eeee|hahahah|www|

11.ctl 的内容如下：

OPTIONS (skip=1)
load data
truncate into table ZRQ
fields terminated by '|'
TRAILING NULLCOLS
(id,
DATA_COL2 FILLER,
text,
remark
)

ZRQ建表语句

create table ZRQ
(
  id     VARCHAR2(20) not null,
  name   VARCHAR2(50),
  text   VARCHAR2(50),
  remark VARCHAR2(20)
)