java调用sqluldr_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，写自己想要的数据即可

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

希望与广大网友互动？？

点此进行留言吧！