oracle存取二进制文件（图片）

package cn.com.songjy.test.db;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Blob;
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 org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class OracleLob {

	private static final Log log = LogFactory.getLog(OracleLob.class);

	private static final String id = "3";

	private static final BufferedInputStream bufferedInputStream = new BufferedInputStream(
			OracleLob.class.getClassLoader().getResourceAsStream(
					"cn/com/songjy/test/db/oracle.properties"));

	private static final Properties props = new Properties();

	static {
		try {
			props.load(bufferedInputStream);
			Class.forName(props.getProperty("driver"));
		} catch (IOException e) {
			log.error(e.getMessage(), e);
		} catch (ClassNotFoundException e) {
			log.error(e.getMessage(), e);
		} finally {
			try {
				if (null != bufferedInputStream)
					bufferedInputStream.close();
			} catch (IOException e) {
				log.error(e.getMessage(), e);
			}
		}
	}

	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(props.getProperty("url"),
				props.getProperty("user"), props.getProperty("password"));
	}

	/**
	 * 保存图片到oracle库中
	 * 
	 * @throws SQLException
	 * @throws IOException
	 */
	public void save_blob() throws SQLException, IOException {
		PreparedStatement preparedStatement = null;
		Connection connection = null;
		ResultSet resultSet = null;
		try {

			/* 先保存空blob */
			connection = getConnection();
			connection.setAutoCommit(false);// 必须设置为false，即开启事务！

			preparedStatement = connection
					.prepareStatement("INSERT INTO testblob(id,image) VALUES(?,?)");

			preparedStatement.setString(1, id);
			// preparedStatement.setBlob(2, oracle.sql.BLOB.empty_lob());//empty_lob()已过期
			preparedStatement.setBlob(2, oracle.sql.BLOB.getEmptyBLOB());// 先存一个空blob,此方法在驱动jar包中

			// log.info(preparedStatement.executeUpdate() > 0 ? "保存空blob成功" : "保存空blob失败");

			if (preparedStatement.executeUpdate() > 0) {
				log.info("保存空blob成功，方法继续！");
			} else {
				log.info("保存空blob失败，方法返回！");
				connection.rollback();
				return;
			}

			/* 正式保存文件 */
			preparedStatement = connection.prepareStatement("SELECT image FROM testblob WHERE id = ? FOR UPDATE");// 须加for update，锁定该行，直至该行被修改完毕，保证不产生并发冲突
			preparedStatement.setString(1, id);

			resultSet = preparedStatement.executeQuery();

			while (true == resultSet.next()) {
				BufferedInputStream bufferedInputStream = null;
				try {
					bufferedInputStream = new BufferedInputStream(
							new FileInputStream(
									"src/main/java/cn/com/songjy/test/db/Jellyfish.jpg"));// 需要保存的文件路径

					Blob image = resultSet.getBlob("image");

					byte[] buffer = new byte[1024];// 每次读取/写入1k

					for (int len = 0; (len = bufferedInputStream.read(buffer)) > 0;) {
						image.setBytes(image.length() + 1, buffer, 0, len);// Blob第一个字节（byte）的位置是从1开始，所以需要+1
					}
				} finally {
					if (null != bufferedInputStream)
						bufferedInputStream.close();
				}

			}

			connection.commit();// 开启了事物，必须手动提交

		} finally {

			if (null != preparedStatement)
				preparedStatement.close();

			if (null != connection)
				connection.close();
		}
	}

	/**
	 * 从oracle库中获取图片
	 * 
	 * @throws SQLException
	 * @throws IOException
	 */
	public void query_blog() throws SQLException, IOException {
		PreparedStatement preparedStatement = null;
		Connection connection = null;
		ResultSet resultSet = null;

		try {
			connection = getConnection();
			preparedStatement = connection
					.prepareStatement("SELECT image FROM testblob WHERE id = ?");

			preparedStatement.setString(1, id);

			resultSet = preparedStatement.executeQuery();

			while (true == resultSet.next()) {
				BufferedInputStream bufferedInputStream = null;
				BufferedOutputStream bufferedOutputStream = null;
				try {
					bufferedInputStream = new BufferedInputStream(
							resultSet.getBinaryStream("image"));

					bufferedOutputStream = new BufferedOutputStream(
							new FileOutputStream(
									"src/main/java/cn/com/songjy/test/db/1.jpg"));// 读取后的保存图片路径

					byte[] buffer = new byte[1024];

					for (int len = 0; (len = bufferedInputStream.read(buffer)) > 0;) {
						bufferedOutputStream.write(buffer, 0, len);
						// bufferedOutputStream.flush();//关闭流时会自动刷新
					}
				} finally {
					if (null != bufferedOutputStream)
						bufferedOutputStream.close();
					if (null != bufferedInputStream)
						bufferedInputStream.close();
				}
			}
		} finally {

			if (null != resultSet)
				resultSet.close();

			if (null != preparedStatement)
				preparedStatement.close();

			if (null != connection)
				connection.close();

		}
	}

	public static void main(String[] args) throws SQLException, IOException {
		// new OracleLob().save_blob();
		new OracleLob().query_blog();
	}
}

/*
 * 本次示例oracle版本是Oracle Database 11g Enterprise Edition Release
 * 11.2.0.1.0,上传的附件是oracle的驱动jar包
 */

相关阅读：

MySQL存取大文本及二进制文件（图片）