有时候，我们需要将文件以二进制流的形式存入数据库中，比如oracle表中有一个字段为files，字段类型为blob。如果编写存储过程，传入blob参数或者文件的路径进行处理似乎有些难度，处理起来也麻烦。由于项目使用到了spring，所以使用spring的jdbc作新增和更新操作。
例如：oracle表t_customer_health_document表中有名为files，类型为blob的字段，需要保存一张图片。
首先，创建一个工具类以获取spring的上下文，如下：
package com.zhangjie.test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TaskInit {
private static ApplicationContext context = null;
/**
* 单例 ApplicationContext
* 用于获取Spring容器中beans
* @return ApplicationContext
* @see
*/
public static ApplicationContext getInstance() {
if (context == null) {
context = new ClassPathXmlApplicationContext(new String[] {
"applicationContext.xml"});
}
return context;
}
}
然后，使用jdbc操作来将文件保存到files字段中，如下：
package com.zhangjie.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.BLOB;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
public class UpdateFiles {
public static Boolean updateFiles(String id, String filepath) {
Boolean result = false;
JdbcTemplate jdbcTemplate = (JdbcTemplate) TaskInit.getInstance()
.getBean("jdbcTemplate");;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
try {
con = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
con.setAutoCommit(false);
BLOB blob = null;
pstmt = con
.prepareStatement("update t_customer_health_document set files=empty_blob() where id=?");
pstmt.setString(1, id);
pstmt.executeUpdate();
pstmt.close();
pstmt = con
.prepareStatement("select files from t_customer_health_document where id= ? for update");
pstmt.setString(1, id);
rset = pstmt.executeQuery();
if (rset.next())
blob = (BLOB) rset.getBlob(1);
File f = new File(filepath);
FileInputStream fin = new FileInputStream(f);
pstmt = con
.prepareStatement("update t_customer_health_document set files=? where id=?");
OutputStream out = blob.getBinaryOutputStream();
byte[] data = new byte[(int) fin.available()];
fin.read(data);
out.write(data);
fin.close();
out.close();
pstmt.setBlob(1, blob);
pstmt.setString(2, id);
pstmt.executeUpdate();
pstmt.close();
con.commit();
con.close();
result = true;
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
JdbcUtils.closeResultSet(rset);
JdbcUtils.closeStatement(pstmt);
DataSourceUtils
.releaseConnection(con, jdbcTemplate.getDataSource());
}
return result;
}
public static void main(String[] args) {
boolean is = updateFiles("D6256CEBFEF76A03E040A8C057E005B3", "D:\\web deployment assembly.jpg");
System.out.println(is);
}
}