java 使用 oracle sqluldr2 快速导出数据文件

前提
1、确认本地电脑 oracle 已经安装好

2、将工具文件在oracle下的bin目录
以下是64位的 window\linux 文件下载地址
https://github.com/chenjunwei111/sqlLoaderFile

 

实体类

/**
* Description  sqlLoader类
* @param
* @Author junwei
* @Date 14:56 2020/11/26
**/
public class ctlUMsg {
	private String query;
	private String separator;//指定字段分隔符 默认逗号
	private String fileName;//导出文件名 可动态指定名  %y%m%d.txt 表示年月日； %b.txt 大多文件导出时，表示文件序号
	private boolean header;//是否需列头 Yes/No 默认No
	private boolean isSql;
	private String quote;//指定非数字字段值前后的引号符 如双引号：  1,"A",3 \r=0x0d \n=0x0a |=0x7c \t=0x09 :=0x3a #=0x23 "=0x22 '=0x27 &=0x26 |=0x7c 空格=0x20 
	private boolean isCom2gz;//压缩成gz 文件后缀加.gz
	private Integer batch;//分多文件导出 1：50W 2:100W ... 结合fileName设置序号
	private boolean isLog;//输出log
	
	 
	
	/**
	 * controller parameter
	 * @param query sql statement or sql file(.sql)
	 * @param fileName File name including css/txt suffix.can naming by parameter ,ex:%y%m%d.txt(yyyymmdd), %b.txt (automatic generation of serial numbers)
	 */
	public ctlUMsg(String query, String fileName) {
		super();
		this.query=query;
		this.fileName=fileName;
		if (this.query.toLowerCase().endsWith(".sql")) {
			this.isSql=true;
		}
	}
	
	/**
	 * controller parameter
	 * @param query sql statement or sql file(.sql)
	 * @param fileName File name including css/txt suffix.can naming by parameter ,ex:%y%m%d.txt(yyyymmdd), %b.txt (automatic generation of serial numbers)
	 * @param separator data separator. can use ASCII code for separator. default is |.
	 * @param isLog export logger?default append to logger as same log file name.
	 * 
	 *    ASCII:\r=0x0d \n=0x0a |=0x7c \t=0x09 :=0x3a #=0x23 "=0x22 '=0x27 &=0x26 |=0x7c 空格=0x20.
	 */
	public ctlUMsg(String query, String fileName, String separator, boolean isLog) {
		super();
		this.query=query;
		this.fileName=fileName;
		this.separator=separator;
		this.isLog=isLog;
		if (this.query.toLowerCase().endsWith(".sql")) {
			this.isSql=true;
		}
	}

	/**
	 * controller parameter
	 * @param query query sql statement or sql file(.sql)
	 * @param fileName File name including css/txt suffix.can naming by parameter ,ex:%y%m%d.txt(yyyymmdd), %b.txt (automatic generation of serial numbers)
	 * @param separator data separator.separator can use ASCII code. default is |
	 * @param isLog export logger?default append to logger as same log file name.
	 * @param header export header? default is No
	 * @param batch multi-batch export.each batch is 50W.ex:1=50W 2=100W ...
	 * @param quote quotation marks before and after non-numeric field values.quote can use ASCII code.ex:1,"A","b",3
	 * @param isCom2gz compression to gz format
	 * 
	 *    separator and quote ASCII:\r=0x0d \n=0x0a |=0x7c \t=0x09 :=0x3a #=0x23 "=0x22 '=0x27 &=0x26 |=0x7c 空格=0x20
	 */
	public ctlUMsg(String query, String fileName, String separator, boolean isLog, boolean header, Integer batch, String quote, boolean isCom2gz) {
		super();
		this.query=query;
		this.fileName=fileName;
		this.separator=separator;
		this.header=header;
		this.batch=batch;
		this.quote=quote;
		this.isCom2gz=isCom2gz;
		this.isLog=isLog;
		if (this.query.toLowerCase().endsWith(".sql")) {
			this.isSql=true;
		}
	}

	public String getQuery() {
		return query;
	}
	public String getSeparator() {
		return separator;
	}

	public String getFileName() {
		return fileName;
	}

	public boolean isHeader() {
		return header;
	}

	public boolean isSql() {
		return isSql;
	}

	public String getQuote() {
		return quote;
	}

	public boolean isCom2gz() {
		return isCom2gz;
	}

	public Integer getBatch() {
		return batch;
	}
	public boolean isLog() {
		return isLog;
	}

 
}
 

 实现类

import org.springframework.stereotype.Service;
import java.io.BufferedReader;
import java.io.File;
import java.io.InputStream;
import java.io.InputStreamReader;

/**
* Description SQLLdr导出类
* @param 
* @Author junwei
* @Date 14:47 2020/11/26
**/
@Service
public class sqlLdrOut {


	public sqlLdrOut() {

	}

	public boolean Loader(ctlUMsg cMsg) {
		try {
			this._cMsg = cMsg;
			if (!this.chkKW()) {
				return false;
			}
			if (!this.builderCmd()) {
				return false;
			}
			return this.start();

		} catch (Exception e) {
			this.exError = new Throwable("sqluloader executing error. see error log.");
		}
		return true;
	}

	private boolean builderCmd() {
		try {
			StringBuilder sb = new StringBuilder();
			if (this._sMsg.getSqlldrPath() != null) {
				sb.append(this._sMsg.getSqlldrPath() + File.separator);
			}

			sb.append("sqluldr2 " + this._sMsg.getUserName() + "/" + this._sMsg.getPassWord() + "@"
					+ this._sMsg.getAddressIp() + ":" + this._sMsg.getPort() + "/" + this._sMsg.getOrclName() + " ");

			if (this._cMsg.isSql()) {
				sb.append("sql=" + this._sMsg.getDataDir() + File.separator + this._cMsg.getQuery() + " ");
			} else {
				sb.append("query=\"" + this._cMsg.getQuery() + "\" ");
			}

			sb.append("file=" + this._sMsg.getDataDir() + File.separator + this._cMsg.getFileName()
					+ (this._cMsg.isCom2gz() ? ".gz" : "") + " ");

			if (this._cMsg.isLog()) {
				String f = this._cMsg.getFileName().substring(0, this._cMsg.getFileName().lastIndexOf("."));
				sb.append("log=+" + this._sMsg.getDataDir() + File.separator + f.replace("%b", "") + ".log ");
			}

			if (this._sMsg.getOrclEcoding() != null) {
				sb.append("charset=" + this._sMsg.getOrclEcoding() + " ");
			}
			if (this._cMsg.isHeader()) {
				sb.append("head=yes ");
			}

			if (this._cMsg.getSeparator() != null) {
				sb.append("field=" + this._cMsg.getSeparator() + " ");
			} else {
				sb.append("field=0x7c ");
			}

			if (this._cMsg.getBatch() != null && this._cMsg.getBatch() > 0) {
				sb.append("batch=" + this._cMsg.getBatch() + " ");
			}

			if (this._cMsg.getQuote() != null) {
				sb.append("quote=" + this._cMsg.getQuote() + " ");
			}

			sb.append("safe=yes");

			this.connectStr = sb.toString().trim();
			sb.setLength(0);
			return true;

		} catch (Exception e) {
			this.exError = new Throwable("build cmd error. ", e);
			return false;
		}
	}

	private boolean chkKW() {
		if (this._sMsg == null) {
			this.exError = new Throwable("sMsg is null. ");
			return false;
		}
		if (!this._sMsg.isIsvaild()) {
			this.exError = new Throwable("sMsg key parameter is null. ");
			return false;
		}
		if (this._cMsg.getFileName() == null || this._cMsg.getQuery() == null) {
			this.exError = new Throwable("cMsg key parameter is null. ");
			return false;
		}
		return true;
	}

	private boolean start() {
		InputStream ins = null;
		Process process = null;
		BufferedReader readers = null;
		try {
			System.out.println("CMD命令");
			System.out.println(this.connectStr);
			process = Runtime.getRuntime().exec(this.connectStr);
			ins = process.getInputStream();
			readers = new BufferedReader(new InputStreamReader(ins));
			String line = null;
			while ((line = readers.readLine()) != null) {
//				String msg = new String(line.getBytes("ISO-8859-1"), "GBK");
//				System.out.println(msg); // 输出
			}
			int exitValue = process.waitFor();
			if (exitValue == 0) {
				return true;
			} else {
				this.exError = new Throwable("load data file error.see log");
				return false;
			}
		} catch (Exception e) {
			this.exError = new Throwable("start sqluder2 process error. ", e);
			return false;
		} finally {
			try {
				if (process != null) {
					process.getOutputStream().close();
				}
				if (readers != null) {
					readers.close();
					readers = null;
				}
				if (ins != null) {
					ins.close();
					ins = null;
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}

	/**
	 * get error message
	 * 
	 * @return
	 */
	public Throwable getexError() {
		return this.exError;
	}

	private ldrMsg _sMsg = null;
	private ctlUMsg _cMsg = null;
	private Throwable exError = null;
	private String connectStr;

	public sqlLdrOut(ldrMsg sMsg) {
		super();
		this._sMsg = sMsg;
	}

	//导出例子
	public static void main(String[] args) {

		String getDataSql="select 't1' as t1 ,'t2' as t2 from dual \n" +
				"union select 't3' as t3, 't4' as t4 from dual ";
		String tableName="dual";
		sqlLdrOut out=new sqlLdrOut();
		out.operate(getDataSql, tableName);

	}




	/**
	* Description 核心导出方法
	* @param
	* @Author junwei
	* @Date 16:54 2020/11/25
	**/
	public  void  operate(String getDataSql,String tableName){

       //这里导出CSV后缀文件，可以修改为txt等
		String fileName=tableName+".csv";
		//linux
//		String originfilename=" /data/static/spmv/ftpupload/";
//		String toolPath="/home/tomcat/";

		//window
		//导出的文件路径
		String originfilename="D:\\export\\";
		
		//使用到的sqlLoader工具文件 父级目录（通常是oracle安装目录下BIN目录是）
		String toolPath="D:\\oracle\\product\\11.2.0\\dbhome_2\\BIN";

		//JDBC数据库连接
		ldrMsg lm = new ldrMsg("192.168.10.11", "account", "password", 1521, "orcl",originfilename ,
				toolPath, null);
		sqlLdrOut sOut = new sqlLdrOut(lm);

		ctlUMsg cu = new ctlUMsg(
				getDataSql,fileName, "0x2c", false, true, 1, null, false);
		if (!sOut.Loader(cu)) {
			System.out.println("错误1：" + tableName + ":"+sOut.getexError());
		}else{
			System.out.println("导出完成");
		}
	}

}

查询SQL，写自己想要的数据即可