Java代码远程操作oracle数据库，执行sql文件、备份、回滚

前提简述

Sqlplus

是一个用于与OracleDatabase交互和执行查询的工具，所以我们要安装的是命令行工具。

imp|exp

是oracle导入导出工具，由于要备份回滚，所以我们也要安装这两个命令工具。

下载工具包

oracle官网去下载三个包：

依赖基础库:instantclient-basic-windows.x64-12.1.0.2.0.zip

sqlplus命令行工具:instantclient-sqlplus-windows.x64-12.1.0.2.0.zip

imp|exp工具:instantclient-tools-windows.x64-12.1.0.2.0.zip

sdk:instantclient-sdk-windows.x64-12.1.0.2.0.zip

安装工具

SQL*Plus命令行工具无需执行exe安装，所以只需将下载回来的两个文件解压到同一个目录即可，解压后文件名应该为instantclient_12_1，在运行工具之前我们需要在windows中配置以下环境变量，先右键计算机->属性->高级系统设置->环境变量:

在系统变量中找到Path并在后面加上刚才解压后instantclient_12_1的目录与sdk子目录

E:\instantclient_12_1\;E:\instantclient_12_1\sdk;

再新增两个变量到系统环境中

TNS_ADMIN=E:\instantclient_12_1

NLS_LANG=AMERICAN_AMERICA.UTF8

或者：编写个bat脚本，内容如下，然后执行一下脚本就好（env.bat）

set Path=%Path%;E:\instantclient_12_1\;E:\instantclient_12_1\sdk;

set TNS_ADMIN=E:\instantclient_11_2

set NLS_LANG=AMERICAN_AMERICA.UTF8

到此则一切工具安装完毕！

测试：

运行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();     } }