java读取写入oracle的blob字段工具类
import com.hzunitech.fxgk.sys.model.UtFileData;
import com.jfinal.kit.PathKit;
import com.jfinal.plugin.activerecord.DbKit;
import oracle.sql.BLOB;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class FileBlobUtil {
@SuppressWarnings("deprecation")
public static int insertBinary(UtFileData utFileData, String filePath) {
String insertSql = "insert into ut_file_data values(?,?,?,?,?,?)";
String updateSql = "update ut_file_data set FILE_DATA=? where FILE_ID=?";
int res = 0;
try {
// Connection conn = getConnection();
Connection conn = DbKit.getConfig().getDataSource().getConnection();
conn.setAutoCommit(false);
PreparedStatement pstm = conn.prepareStatement(insertSql);
pstm.setString(1, utFileData.getFileId());
pstm.setString(2, utFileData.getFilePath());
//EMPTY_BLOB和EMPTY_CLOB返回一个空的LOB定位器,
//可以用来初始化一个LOB变量,或在INSERT或UPDATE语句,
//初始化LOB列或属性为空。 EMPTY表示LOB初始化,但不填充数据。
pstm.setBlob(3, oracle.sql.BLOB.empty_lob());
pstm.setString(4, utFileData.getStatus());
pstm.setString(5, utFileData.getDataId());
pstm.setTimestamp(6, utFileData.getAddTime());
pstm.executeUpdate();
pstm.close();
pstm = conn.prepareStatement("select * from ut_file_data where FILE_ID=?");
pstm.setString(1, utFileData.getFileId());
ResultSet rs = pstm.executeQuery();
rs.next();
BLOB blob = (BLOB) rs.getBlob(3);
OutputStream os = blob.getBinaryOutputStream();
FileInputStream fis = new FileInputStream(filePath);
byte[] buff = new byte[1024];
// byte[] buff = new byte[fis.available()];
int len = fis.read(buff);
while (len != -1) {
os.write(buff);
len = fis.read(buff);
}
pstm = conn.prepareStatement(updateSql);
pstm.setBlob(1, blob);
pstm.setString(2, utFileData.getFileId());
res = pstm.executeUpdate();
fis.close();
os.close();
conn.commit();
rs.close();
pstm.close();
conn.close();
if (res > 0) {
System.out.println("附件上传ut_file_data:success");
}
} catch (Exception ex) {
ex.printStackTrace();
}
return res;
}
@SuppressWarnings("deprecation")
public static int readBinary(String fileId) {
int res = 0;
try {
String querySql = "select * from ut_file_data where FILE_ID = ?";
Connection conn = DbKit.getConfig().getDataSource().getConnection();
PreparedStatement pstm = conn.prepareStatement(querySql);
pstm.setString(1, fileId);
ResultSet rs = pstm.executeQuery();
if (rs.next()) {
String filePath = rs.getString(2);
BLOB fileData = (BLOB) rs.getBlob(3);
InputStream ins = fileData.getBinaryStream();
String docPath = PathKit.getWebRootPath() + File.separator + filePath;
File file = new File(docPath);
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
if (!file.exists()) {
file.createNewFile();
}
OutputStream os = new FileOutputStream(file);
int bytesRead = 0;
byte[] buffer = new byte[1024];
while ((bytesRead = ins.read(buffer, 0, 1024)) != -1) {
os.write(buffer, 0, bytesRead);
}
os.close();
ins.close();
rs.close();
pstm.close();
conn.close();
res++;
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return res;
}
//写入测试
public static void test() {
String inFile = "C:/Users/zz/Desktop/icon.png";
// String inFile = "C:/Users/zz/Desktop/111.xls";
// String inFile = "C:/Users/zz/Desktop/222.txt";
UtFileData utFileData = new UtFileData();
String uuid = ValidateUtil.getUUid();
utFileData.setFileId(uuid);
utFileData.setFilePath("images/warn/icon.png");
// utFileData.setFilePath("images/warn/111.xls");
// utFileData.setFilePath("images/warn/222.txt");
utFileData.setStatus("true");
utFileData.setDataId("11122233");
utFileData.setAddTime(DateFormatUtil.getCurrTimestamp());
// utFileData.save(uuid);
FileBlobUtil.insertBinary(utFileData, inFile);
}
}
不积跬步无以至千里,不积小流无以成江海