java读写oracle的blob字段示例

代码如下:

package com.wanmei.meishu;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import oracle.sql.BLOB;

public class BlobUtil {

private static BlobUtil bu;
private String env;
public static BlobUtil getInstance(String env) {
 bu = new BlobUtil();
 bu.env = env;
 return bu;
}

/**
* 得到数据库链接
* @return
* @throws Exception
*/
private Connection getConnection() throws Exception {
 String driver = "oracle.jdbc.driver.OracleDriver";
 Class.forName(driver);
 String env = this.env;
 Properties pro = new Properties();
 // 读取classes 目录下的配置文件
 pro.load(new FileReader(Class.class.getResource("/config.properties").getFile()));
 String host = pro.getProperty(env + ".host");
 String database = pro.getProperty(env + ".database");
 String username = pro.getProperty(env + ".username");
 String password = pro.getProperty(env + ".password");
 String port = pro.getProperty(env + ".port");
 String url = "jdbc:oracle:thin:@" + host + ":" + port +":" + database;
 return DriverManager.getConnection(url, username, password);
}

/**
* 传入项目，任务，附件名称，文件路径，写入数据库
* @param projectId
* @param taskId
* @param fileName
* @param file
* @return 返回是否成功
*/
public boolean write(String projectId, String taskId, String fileName, String file) {
 Connection conn = null;
 ResultSet rs = null;
 PreparedStatement ps = null;
 BLOB blob = null;
 boolean flag = false;
 try {
 conn = getConnection();
 conn.setAutoCommit(false);

 String sql = "INSERT INTO PS_ZP_PRJ_WBS_BLOB BLB(ZP_PRJ_ID, ZZ_SEQ_NUM, ZZ_FILE_NAME, ZZ_IMAGE_BLOB) VALUES(?, ? ,? ,empty_blob())";
 ps = conn.prepareStatement(sql);
 ps.setString(1, projectId);
 ps.setString(2, taskId);
 ps.setString(3, fileName);
 ps.executeUpdate();
 sql = "SELECT ZZ_IMAGE_BLOB FROM PS_ZP_PRJ_WBS_BLOB WHERE ZP_PRJ_ID = ? AND ZZ_SEQ_NUM = ? AND ZZ_FILE_NAME = ? FOR UPDATE";
 ps = conn.prepareStatement(sql);
 ps.setString(1, projectId);
 ps.setString(2, taskId);
 ps.setString(3, fileName);
 rs = ps.executeQuery();
 if(rs.next()) {
 blob = (BLOB) rs.getBlob(1);
 }
 InputStream in = new FileInputStream(file);
 OutputStream out = blob.setBinaryStream(1L);
 byte[] buffer = new byte[1024];
 int length = -1;
 while ((length = in.read(buffer)) != -1){
 out.write(buffer, 0, length);
 }
 in.close();
 out.close();
 conn.commit();
 conn.setAutoCommit(true);
 flag = true;
 }
 catch(Exception e) {
 if(conn != null) {
 try {
 conn.rollback();
 } catch (SQLException e1) {
 e1.printStackTrace();
 }
 }
 }
 finally {
 try {
 rs.close();
 ps.close();
 conn.close();
 }
 catch(Exception e) {
 e.printStackTrace();
 }
 }
 return flag;
}

/**
* 根据项目Id，任务Id，文件名读取数据库blob字段文件，写入指定的文件路径
* @param projectId
* @param taskId
* @param fileName
* @param file
* @return 返回是否成功
*/
public boolean read(String projectId, String taskId, String fileName, String file) {
 Connection conn = null;
 ResultSet rs = null;
 PreparedStatement ps = null;
 BLOB blob = null;
 boolean flag = false;
 try {
 conn = getConnection();
 String sql = "SELECT ZZ_IMAGE_BLOB FROM PS_ZP_PRJ_WBS_BLOB WHERE ZP_PRJ_ID = ? AND ZZ_SEQ_NUM = ? AND ZZ_FILE_NAME = ?";
 ps = conn.prepareStatement(sql);
 ps.setString(1, projectId);
 ps.setString(2, taskId);
 ps.setString(3, fileName);
 rs = ps.executeQuery();
 if(rs.next()) {
 blob = (BLOB) rs.getBlob(1);
 }
 InputStream in = blob.getBinaryStream();
 byte[] buf = new byte[1024];
 int bytesIn = 0;
 FileOutputStream out = new FileOutputStream(file);
 while ((bytesIn = in.read(buf, 0, 1024)) != -1) {
 out.write(buf, 0, bytesIn);
 }
 in.close();
 out.close();
 flag = true;
 }
 catch(Exception e) {
 e.printStackTrace();
 }
 finally {
 try {
 rs.close();
 ps.close();
 conn.close();
 }
 catch(Exception e) {
 e.printStackTrace();
 }
 }
 return flag;
}

public static void main(String[] args) {
  BlobUtil bu = BlobUtil.getInstance("MSDEV");
  System.out.println(bu.write("CB", "001", "image1", "D:\\61e44b02jw1dw4xbp2zo6j.jpg"));
  System.out.println(bu.read("CB", "001", "image1", "D:\\2.jpg"));
}

}