欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-10 21:43:23
前提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 ....

前提
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,写自己想要的数据即可 

 

本文地址:https://blog.csdn.net/qq_37203082/article/details/110188164

相关标签: oracle sqluldr2