java oracle blob_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"));

}

}