利用Oracle数据库进行文件传输
为甲方升级系统时，堡垒机应用服务器正常，唯独FTP出现故障，无法上传程序包（堡垒机软件不支持直接向Linux服务器传文件）。在为无法完成部署任务发愁时，我们发现借助专用VPN可以直连甲方的Oracle数据库，并以DBA身份登录，于是我们临时编写了一款“基于Oracle的文件传输工具”。
代码
由于VPN网络不太稳定，我们在上传和下载时实现了文件分块，这样一旦发生网络问题我们可以“断点续传”。至于服务器那边，网络肯定是稳定的，否则应用系统早就让用户喷下线了。
Up.java
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Up {
private final static String DB_CONNSTR = "jdbc:oracle:thin:@数据库IP地址:1521:数据库实例名";
private final static String DB_USERNAME = "用户名";
private final static String DB_PASSWORD = "密码";
private final static String INSERT_SQL = "INSERT INTO TMP_FTP (ID,NAME,GROUPID,SEQ,CONTENT) VALUES (SEQ_TMP_FTP.NEXTVAL, ?, ?, ?, ?)";
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
System.err.println("错误：未找到ojdbc.jar！");
return;
}
String fileName = args[0];
String gid = fileName;
//String gid = args[1];
int part = -1;
if (args.length > 1) {
part = Integer.parseInt(args[1]);
}
// 文件分割
try {
File blob = new File(fileName);
FileInputStream in = new FileInputStream(blob);
byte[] buffer = new byte[1048576];
int seq = 0;
int len = 0;
long upload = 0;
long length = blob.length();
while ((len = in.read(buffer)) != -1) {
seq++;
if (part != -1) {
if (seq != part) {
continue;
}
}
System.out.print("\rUploading " + fileName + "#" + seq + " (" + upload + "/" + length + ")...");
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
ByteArrayOutputStream bos = new ByteArrayOutputStream();
bos.write(buffer, 0, len);
try {
conn = DriverManager.getConnection(DB_CONNSTR, DB_USERNAME, DB_PASSWORD);
conn.setAutoCommit(true);
stmt = conn.prepareStatement(INSERT_SQL);
stmt.setString(1, fileName);
stmt.setString(2, gid);
stmt.setLong(3, seq);
stmt.setBytes(4, bos.toByteArray());
stmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
bos.close();
System.out.print("\r" + fileName + "#" + seq + " Done. ");
System.out.println();
upload += len;
}
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}Down.java
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Down {
private final static String DB_CONNSTR = "jdbc:oracle:thin:@数据库IP地址:1521:数据库实例名";
private final static String DB_USERNAME = "用户名";
private final static String DB_PASSWORD = "密码";
private final static String QUERY_SQL = "SELECT ID,NAME,LENGTH(CONTENT) AS LEN,CONTENT FROM TMP_FTP WHERE GROUPID=? ORDER BY SEQ";
private final static String QUERY_CNT_SQL = "SELECT COUNT(1) FROM TMP_FTP WHERE GROUPID=?";
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
System.err.println("错误：未找到ojdbc.jar！");
return;
}
String gid = args[0];
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(DB_CONNSTR, DB_USERNAME, DB_PASSWORD);
long cnt = 0;
stmt = conn.prepareStatement(QUERY_CNT_SQL);
stmt.setString(1, gid);
rs = stmt.executeQuery();
if (rs.next()) {
cnt = rs.getLong(1);
}
rs.close();
stmt.close();
stmt = conn.prepareStatement(QUERY_SQL);
stmt.setString(1, gid);
rs = stmt.executeQuery();
String fileName = "";
boolean append = false;
long part = 0;
while (rs.next()) {
if (fileName.length() == 0) {
String sfileName = rs.getString("NAME");
if (sfileName != null) {
fileName = sfileName;
if (!append) {
new File(fileName).delete();
append = true;
}
}
}
long id = rs.getLong("ID");
long length = rs.getLong("LEN");
long download = 0;
Blob blob = rs.getBlob("CONTENT");
InputStream in = blob.getBinaryStream();
OutputStream out = new FileOutputStream(fileName, true);
byte[] buff = new byte[4096];
int len = 0;
part++;
while ((len = in.read(buff)) != -1) {
System.out.print("\rDownloading " + fileName + "#" + id + " (" + download + "/" + length + ", Part " + part + "/" + cnt + ")...");
out.write(buff, 0, len);
download += len;
}
System.out.print("\r" + fileName + "#" + id + " Done. ");
System.out.println();
out.close();
in.close();
}
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}部署方式
- 连接VPN，登录数据库，初始化表结构：
CREATE TABLE TMP_FTP ( ID NUMBER NOT NULL, NAME VARCHAR2(50), GROUPID VARCHAR2(20), SEQ NUMBER, CONTENT BLOB ); CREATE SEQUENCE SEQ_TMP_FTP START WITH 1000000; - 修改Up.java和Down.java的数据源配置。
- 上传文件（下面假设自己电脑也是Linux/Mac，并且假设ojdbc6.jar放在了家目录）
javac Up.java CLASSPATH=.:~/ojdbc6.jar java Up 文件名 - 如果出现网络错误，记录屏幕上提示的数字，用
CLASSPATH=.:~/ojdbc6.jar java Up 文件名 数字的方式“断点续传”。 - 登录堡垒机，进入应用服务器，将Down.java内容粘贴到服务器中，并且（假设ojdbc6.jar放在了/opt中）
javac Down.java CLASSPATH=.:/opt/ojdbc6.jar java Down 文件名 - 确认文件下载好了，而且内容没有错误，继续升级操作。
- 操作完成后进行清理：
DROP TABLE TMP_FTP; DROP SEQUENCE SEQ_TMP_FTP;