场景需求
利用java完成Oracle dmp文件的导入和导出
环境
jdk1.8 , oracle服务端、客户端(批pl/sql)
代码解析
直接上导入导出的工具类了,里面有些结果集和参数,是一些封装类,上传太麻烦,在这里就不献上了,仔细看下测试类就能明白了
package com.tydic.util;
import com.google.common.base.Joiner;
import com.tydic.pojo.DsOracleDmp;
import com.tydic.pojo.Result;
import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.List;
/**
* @author dengyichao
* @Title: OracleDmpUtil
* @Package: com.tydic.util
* @Description:
* @date 2018/5/28 17:10
*/
public class OracleDmpUtil {
/**
* @Author: dengyichao
* @Date: 2018/5/28 17:34
* @param dsTables 数据源对象(包含表)
* @Description: 从oracle中导出dmp文件
*/
public static Result generateDmp(DsOracleDmp dsTables){
/*表导出语句 如果要导出多表则在 tables参数里添加 例如
String commandBuf = "exp userid=system/system@localhost:1521/ORCL file=E://dyc.dmp tables=(TEST1)";*/
StringBuffer commandBuf = new StringBuffer("exp userid=");
commandBuf.append(dsTables.getDbUsername() + "/");
commandBuf.append(dsTables.getDbPassword() + "@");
commandBuf.append(dsTables.getDbAddress() + "/");
commandBuf.append(dsTables.getServiceName());
commandBuf.append(" file=" + Constants.DMP_UP_FILE + "//" + dsTables.getFileName() + " tables=(");
List<String> tabNameList = dsTables.getTabNameList();
if(null != tabNameList && tabNameList.size() > 0){
tabNameList.forEach(e -> {
commandBuf.append(e + ",");
});
commandBuf.deleteCharAt(commandBuf.length() - 1);
}else{
return ResultUtil.fail("导出失败,没有导出的表名");
}
commandBuf.append(")");
String[] cmds ={"cmd","/C",commandBuf.toString()} ;
Joiner joiner = Joiner.on(" ").skipNulls();
System.out.println(joiner.join(cmds));
Process process = null;
try {
process = Runtime.getRuntime().exec(cmds);
} catch (IOException e) {
e.printStackTrace();
return ResultUtil.fail("导出失败");
}
boolean shouldClose = false;
try {
InputStreamReader isr = new InputStreamReader(process
.getErrorStream());
BufferedReader br = new BufferedReader(isr);
String line = null;
while ((line = br.readLine()) != null) {
System.out.println(line);
if (line.indexOf("????") != -1) {
shouldClose = true;
break;
}
}
} catch (IOException ioe) {
shouldClose = true;
return ResultUtil.fail("导出失败");
}
if (shouldClose)
process.destroy();
int exitVal;
try {
exitVal = process.waitFor();
System.out.print(exitVal);
} catch (InterruptedException e) {
e.printStackTrace();
return ResultUtil.fail("导出失败");
}
return ResultUtil.success();
}
public static Result impDmp(DsOracleDmp dsTables){
String info="";
String filepath = Constants.DMP_UP_FILE + File.separator + dsTables.getFileName();
//String commandBuf = "imp dengyichao/system@localhost:1521/ORCL fromuser=yhgl touser=dengyichao file=d://xxx.dmp ignore=y";
StringBuffer commandBuf = new StringBuffer("imp ");
commandBuf.append(dsTables.getDbUsername() + "/");
commandBuf.append(dsTables.getDbPassword() + "@");
commandBuf.append(dsTables.getDbAddress() + "/");
commandBuf.append(dsTables.getServiceName());
commandBuf.append(" fromuser=" + dsTables.getImpDmpUserName());
commandBuf.append(" touser=" + dsTables.getDbUsername());
commandBuf.append(" file=" + filepath);
commandBuf.append(" ignore=y");
String[] cmds ={"cmd","/C",commandBuf.toString()};
Process process = null;
try {
process = Runtime.getRuntime().exec(cmds);
} catch (IOException e) {
e.printStackTrace();
return ResultUtil.fail("导入失败");
}
boolean shouldClose = false;
try {
InputStreamReader isr = new InputStreamReader(process
.getErrorStream());
BufferedReader br = new BufferedReader(isr);
String line = null;
while ((line = br.readLine()) != null) {
System.out.println(line);
if (line.indexOf("????") != -1) {
shouldClose = true;
break;
}
}
} catch (IOException ioe) {
shouldClose = true;
return ResultUtil.fail("导入失败");
}
if (shouldClose)
process.destroy();
int exitVal;
try {
exitVal = process.waitFor();
System.out.print(exitVal);
} catch (InterruptedException e) {
e.printStackTrace();
return ResultUtil.fail("导入失败");
}
return ResultUtil.success();
}
}
测试类
package com.tydic.util;
import com.google.common.collect.ImmutableList;
import com.tydic.pojo.DsOracleDmp;
import org.junit.Test;
/**
* @author dengyichao
* @Title: OracleDmpUtilTest
* @Package: com.tydic.util
* @Description:
* @date 2018/5/29 9:05
*/
public class OracleDmpUtilTest {
@Test
public void generateDmp() throws Exception {
DsOracleDmp dod = new DsOracleDmp();
dod.setFileName("test1.dmp");
dod.setTabNameList(ImmutableList.of("test1"));
dod.setDbUsername("system");
dod.setDbPassword("admin");
dod.setDbAddress("localhost:1521");
dod.setServiceName("ORCL");
OracleDmpUtil.generateDmp(dod);
}
@Test
public void impDmp(){
DsOracleDmp dod = new DsOracleDmp();
dod.setDbUsername("dengyichao");
dod.setDbPassword("dengyichao");
dod.setDbAddress("localhost:1521");
dod.setServiceName("ORCL");
dod.setImpDmpUserName("yhgl");
dod.setFileName("test3.dmp");
OracleDmpUtil.impDmp(dod);
}
}