Oracle中Blob字段的写入处理（一）
韦 伟
Oracle中的Bloc字段的处理较其他字段来说，会有一些特殊性。现就对在java和pl/sql下，Blob字段的处理和大家做一些交流。
下面，简单介绍一下Blob在以下两种环境中的写入处理，
u java环境
u PL/SQL环境
一、java环境
1、 Blob的插入操作
1) 插入一空的Blob。
2) 更新该纪录的Blob。
例子：
比如表结构如下：
CREATE TABLE Student (
Name VARCHAR2(30),
Age Int,
Picture Blob);
i.插入一空的Blob。
String command=”INSERT INTO Student VALUES(?,?,?)”;
Connection conn=null;
PreparedStatement ps=null;
try
{
conn=….;
ps=conn. prepareStatement(command);
ps.setString(1,”ZhangSan”);
ps.setInt(2,20);
ps.setBlob(3, BLOB.empty_lob());
ps.executeUpdate();
…
ii.更新该纪录
…
byte[] data=null;
Connection conn=null;
Try
{
data=… //图片信息
conn=..
ByteArrayInputStream in=new ByteArrayInputStream(data);
updateBlob(in, conn, “Student”,”Picture”,”Name”,”zhangsan”);
…
…
public static void updateBlob( InputStream instream,
Connection conn,
String table,
String blobColumn,
String keyColumn,
String keyValue)
throws SQLException, IOException
{
Statement stmt = null;
OracleResultSet rs = null;
BLOB blob = null;
boolean oldAutoCommit = conn.getAutoCommit();
StringBuffer sqlBuffer = new StringBuffer();
try {
conn.setAutoCommit(false);
sqlBuffer.append("select ");
sqlBuffer.append(blobColumn);
sqlBuffer.append(" from ");
sqlBuffer.append(table);
sqlBuffer.append(" where ");
sqlBuffer.append(keyColumn);
sqlBuffer.append("='");
sqlBuffer.append(keyValue);
//注意这里的”for update”
sqlBuffer.append("' for update ");
stmt = conn.createStatement();
rs = (OracleResultSet) stmt.executeQuery(sqlBuffer.toString());
if (!rs.next())
{
rs.close();
stmt.close();
throw new IllegalArgumentException(
"no record found for keyValue: '" + keyValue + "'");
}
blob = rs.getBLOB(1);
OutputStream outstream = blob.getBinaryOutputStream();
int bufferSize = blob.getChunkSize();
byte[] buffer = new byte[bufferSize];
int bytesRead = -1;
while ((bytesRead = instream.read(buffer)) != -1)
{
outstream.write(buffer, 0, bytesRead);
}
instream.close();
outstream.close();
rs.close();
stmt.close();
}
catch (SQLException e)
{
throw e;
}
catch (IOException e)
{
throw e;
}
finally {
conn.setAutoCommit(oldAutoCommit);
}
}
二、PL/SQL下
(例子表结构如一所示)
i.插入一空的Blob。
…
declare
bufferBlob BLOB;
data RAW(…)
…
INSERT INTO Student VALUES(‘zhangsan’, 20 , empty_blob() );
…
ii.更新该纪录的Blob。
…
SELECT Picture INTO bufferBlob FROM Student WHERE Name=’zhangsan’ FOR UPDATE;
DBMS_LOB.OPEN(bufferBlob , dbms_lob.lob_readwrite);
dbms_lob.write(bufferBlob , utl_raw.length(data) , data);
…
(待续)
