java sqlplus方式实现sql在Oracle服务器上运行

1.代码如下：

   

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;

public class SqlPlus {
    public static void main(String[] args) {
        OracleJdbc conn = new OracleJdbc();//获取Oracle连接 自己写一个方法类就好了。
        //需要执行的sql文件
        String filePath ="e:\\test\\test2.sql";
        //文件编码  获取文件编码请看
        String db_code="UTF-8";
        try {
            runShell(conn,filePath,db_code);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行sql文件
     * @param conn
     * @param file
     * @param db_code 文件编码
     * @throws Exception
     */
    static String runShell(OracleJdbc conn,String filePath,String db_code) throws Exception{
        String message = "";
        File file = new File(filePath);
        //1.sqlplus方式执行
        String [] cmds = getExeCommend(file,db_code);
        Process pro = Runtime.getRuntime().exec(cmds);  
        pro.waitFor();  
        InputStream in = pro.getInputStream();  
        BufferedReader read = new BufferedReader(new InputStreamReader(in,db_code));  
        String line = null;  
        while((line = read.readLine())!=null){  
            message += line+System.lineSeparator();  
        }
        return message;
    }
    /**
     * 获取cmd命令
     * @param file
     * @return
     */
    private static String[] getExeCommend(File file,String db_code) {
          //服务器上 通过sh执行的目标脚本
          String sqlPckPath ="/testsqlplus"+File.separator+file.getName();
          //未生成
          String sqlLogPath ="/testsqlplus/test.log";
          //写sh文件
          int port = 1521;
          String dbName = "orcl";//实例名
          String user = "zhb";
          String pass = "123456";
          String ip = "192.168.100.41";
          //每一行 的ssh命令都需要换行所以加上\r\n的换行符 否则报错
          String ssh = "sqlplus -s "+user+"/"+pass+"@"+ip+":"+port+"/"+dbName+" << eof\r\nset define off;\r\n@"+sqlPckPath+";\r\nexit;\r\n"+"eof";
          //服务器上的路径
          String sshPath = "/testsqlplus"+File.separator+"sshPath"+File.separator+"exeSqlPlus.sh";
          BufferedReader reader=null;
          BufferedWriter write = null; 
          BufferedWriter writeSsh = null; 
          try {
              //从原文件中读出
              FileInputStream fis = new  FileInputStream(file.getPath());
              //写入文件
              FileOutputStream fos = new FileOutputStream(sqlPckPath);
              FileOutputStream fosSsh = new FileOutputStream(sshPath);
              reader = new  BufferedReader(new InputStreamReader(fis,db_code)); 
              write = new BufferedWriter(new  OutputStreamWriter(fos,db_code));
              writeSsh = new BufferedWriter(new  OutputStreamWriter(fosSsh));
              String temp = ""; 
              while((temp=reader.readLine()) != null) {
                  write.write(temp+System.lineSeparator());//每写一行就换行
              }
              writeSsh.write(ssh);
          }catch(Exception e) {
              e.printStackTrace();
          }finally {
              try {
                  if(reader!=null) {
                      reader.close();
                  }
                if(write!=null) {
                    write.close(); 
                }
                if(writeSsh!=null) {
                    writeSsh.close(); 
                }
              } catch (IOException e) {
                  e.printStackTrace();
                }
          }
        String[] cmds = {"sh", sshPath,"|","tee",sqlLogPath};
        return cmds;
    }
}

2。说明：

　　①以上是文件路径都需要改成服务器的文件路径，否则会报文件找不到的错误。

　　②实现思路：将需要执行的sql文件，用ssh命令执行，将ssh文件写到服务器目录里，用代码中Runtime.getRuntime().exec(cmds); 方式执行。

　　③服务器上必须有sqlplus环境，及目标文件的执行权限