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环境,及目标文件的执行权限