Java代码远程操作oracle数据库,执行sql文件、备份、回滚
前提简述
Sqlplus
是一个用于与OracleDatabase交互和执行查询的工具,所以我们要安装的是命令行工具。
imp|exp
是oracle导入导出工具,由于要备份回滚,所以我们也要安装这两个命令工具。
下载工具包
测试:
运行cmd->sqlplus:
运行cmd->exp:
运行cmd->imp:
java代码调用:代码找中env.bat就是上述讲到的环境变量bat脚本
public class SqlplusTest {
public static void main(String[] args) {
// 执行sql文件
String ip = "192.168.75.76";
String port = "1521";
String user = "system";
String password = "oracle";
String serviceId = "hsoradb";
String basePath = "F:/eclipse/workspace/MyTest/src/sqlplus";
String sqlplusPath = "E:/instantclient_12_1/";
String sqlPath = "C:/Users/liqiao13413/Desktop/test.sql";
String cmd = "cd /d " + basePath + " && env.bat " + sqlplusPath + " && sqlplus " + user + "/" + password + "@"+ ip + ":" + port + "/" + serviceId + " @" + sqlPath;
ICommandLineClient client = new LocalWindowsCommandClient();
ComposeResponse reponse = client.execute(cmd);
System.out.println(reponse.getExitCode());
System.out.println(reponse.getData());
// 导出数据dump
String dumpPath = "E:/database.dump";
String backupCmd = "cd /d " + basePath + " && env.bat " + sqlplusPath + " && exp " + user + "/" + password + "@"+ ip + ":" + port + "/" + serviceId + " file=" + dumpPath + " full=y";
ComposeResponse backUpreponse = client.execute(backupCmd);
System.out.println(backUpreponse.getData());
System.out.println(backUpreponse.getExitCode());
// 导入数据dump
String rollbackCmd = "cd /d " + basePath + " && env.bat " + sqlplusPath + " && imp " + user + "/" + password+ "@" + ip + ":" + port + "/" + serviceId + " file=" + dumpPath + " full=y";
ComposeResponse rollbackreponse = client.execute(rollbackCmd);
System.out.println(rollbackreponse.getData());
System.out.println(rollbackreponse.getExitCode());
client.disconnect();
}
}