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

到此结束，希望对大家有帮助！