使用Java把Excel,Word,PDF,PPT,JPG,MP4等等文件转化为二进制格式存储到数据库中,然后在需要使用的地方再还原文件即可,这样方法相比普通上传下载文件来说比较安全很多
转化文件需要用到的jar包:https://download.csdn.net/download/zxwu_1993/12058241
本地测试文件
存到数据库后的效果图
文件恢复
数据库结构信息
-- Create table
create table T_AUDIT_FILE
(
id VARCHAR2(50) not null,
fileid VARCHAR2(50),
filename VARCHAR2(200),
filecontent BLOB,
filesize INTEGER,
suffix VARCHAR2(50)
)
-- Add comments to the table
comment on table T_AUDIT_FILE
is '文件表';
-- Add comments to the columns
comment on column T_AUDIT_FILE.id
is 'id';
comment on column T_AUDIT_FILE.fileid
is '文件编号';
comment on column T_AUDIT_FILE.filename
is '文件名称';
comment on column T_AUDIT_FILE.filecontent
is '文件内容';
comment on column T_AUDIT_FILE.filesize
is '文件大小';
comment on column T_AUDIT_FILE.suffix
is '文件后缀';
-- Create/Recreate primary, unique and foreign key constraints
alter table T_AUDIT_FILE
add constraint PK_T_AUDIT_FILE primary key (ID)
具体实现代码如下
数据库连接类
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
public static Connection getDBConnection() throws Exception {
Connection con = null;
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521/orcl";
String user = "ceshi";
String password = "Ceshi_0123";
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
return con;
}
public static void main(String[] args) throws Exception {
getDBConnection();
System.out.println("连接成功!");
}
}
文件转化二进制并存储到数据库的核心实现类
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.UUID;
import oracle.sql.BLOB;
@SuppressWarnings("deprecation")
public class Bolb_test {
public static void main(String[] args) throws SQLException {
ArrayList<String> listFileName = new ArrayList<String>();
getAllFileName("D:\\pdf\\", listFileName);
daor(listFileName);
}
public static void daor(ArrayList<String> listFileName) throws SQLException {
Connection con = null;
long start = System.currentTimeMillis(); // count runtime
CallableStatement pstmt = null;
InputStream fin = null;
OutputStream outStream = null;
for (String path : listFileName) {
File file = new File(path);
try {
con = DBConnection.getDBConnection();
con.setAutoCommit(false);
String sql = "insert into T_AUDIT_FILE values(?,?,?,?,?,?)";
// insert database
pstmt = con.prepareCall(sql);
String docid = "";
if (file.getName().indexOf(".", 1) == file.getName().lastIndexOf(".")) {
docid = file.getName().substring(0, file.getName().lastIndexOf("."));
} else {
docid = file.getName().substring(file.getName().indexOf(".", 1) + 1,
file.getName().lastIndexOf("."));
}
String suffix = file.getName().substring(file.getName().lastIndexOf(".") + 1);
String id = UUID.randomUUID().toString().replace("-", "");
pstmt.setString(1, id);
pstmt.setString(2, docid);
pstmt.setString(3, file.getName());
Blob doccontent = BLOB.empty_lob();
pstmt.setBlob(4, doccontent);
pstmt.setInt(5, (int) file.length());
pstmt.setString(6, suffix);
pstmt.execute();
Statement stmt = con.createStatement();
ResultSet rs = stmt
.executeQuery("select id,filecontent from T_AUDIT_FILE where id = '" + id + "' for update");
// get specially columns and rows for update
BLOB blob = null;
while (rs.next()) {
blob = (BLOB) rs.getBlob("filecontent");
outStream = blob.getBinaryOutputStream();
fin = new FileInputStream(file); // put file into stream
// BufferedInputStream bis = new BufferedInputStream(fin);
// byte[] b = new byte[(int) file.length()];
// byte[] b = new byte[blob.getBufferSize()];
int len = 0;
while ((len = fin.read()) != -1) {
outStream.write(len);
}
fin.close();
outStream.flush();
outStream.close();
}
String writeSql = "update T_AUDIT_FILE set filecontent = ? where id = ?";
java.sql.PreparedStatement ps = con.prepareStatement(writeSql);
ps.setBlob(1, blob);
ps.setString(2, id);
ResultSet rs1 = ps.executeQuery();
rs1.close();
con.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
con.close();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
public static void getAllFileName(String path, ArrayList<String> listFileName) {
File file = new File(path);
File[] files = file.listFiles();
String[] names = file.list();
if (names != null) {
String[] completNames = new String[names.length];
for (int i = 0; i < names.length; i++) {
completNames[i] = path + names[i];
}
listFileName.addAll(Arrays.asList(completNames));
}
for (File a : files) {
if (a.isDirectory()) {// 如果文件夹下有子文件夹,获取子文件夹下的所有文件全路径。
getAllFileName(a.getAbsolutePath() + "\\", listFileName);
}
}
}
}
文件恢复实现类
import java.io.*;
import java.sql.*;
import oracle.sql.BLOB;
@SuppressWarnings("deprecation")
public class Getbolb_test {
public static void main(String[] args) throws SQLException {
Connection con = null;
long start = System.currentTimeMillis(); // count runtime
String path = "D:\\pdf\\文件恢复.jpg";
File file = new File(path);
try {
con = DBConnection.getDBConnection();
con.setAutoCommit(false);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select id,filename,filecontent,filesize from T_AUDIT_FILE where id='4bde107e-9cc5-46a5-949c-65374a2bfe03'");
// get blob form your table
if (rs.next()) {
int docsize = rs.getInt("filesize");
String docname = rs.getString("filename");
BLOB blob = (BLOB) rs.getBlob("filecontent");
// get word column
FileOutputStream output = new FileOutputStream(file);
// define a file output stream
InputStream input = blob.getBinaryStream();// put blob into input
//byte[] buffer = new byte[blob.getBufferSize()];
byte[] buffer = new byte[docsize];
// if use 1024 it will lose some bytes
int len = 0;
while ((len = input.read(buffer)) != -1) {
// get all input stream into output file stream
output.write(buffer, 0, len);
input.close();
output.flush();
output.close();
}
System.out.print("\ndownload ok\n");
}
}
catch (Exception e) {
e.printStackTrace();
}
con.close();
long end = System.currentTimeMillis();
System.out.println(end - start);
}
}