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包
*/
相关阅读:
[url=http://songjianyong.iteye.com/blog/1961765]MySQL存取大文本及二进制文件(图片)[/url]