oracle中blob字段的寫入處理（一）
韋 偉
oracle中的bloc字段的處理較其他字段來說，會有一些特殊性。現(xiàn)就對在java和pl/sql下，blob字段的處理和大家做一些交流。
下面，簡單介紹一下blob在以下兩種環(huán)境中的寫入處理，
u java環(huán)境
u pl/sql環(huán)境
一、java環(huán)境
1、 blob的插入操作
1) 插入一空的blob。
2) 更新該紀(jì)錄的blob。
例子：
比如表結(jié)構(gòu)如下：
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.更新該紀(jì)錄
…
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下
(例子表結(jié)構(gòu)如一所示)
i.插入一空的blob。
…
declare
bufferblob blob;
data raw(…)
…
insert into student values(‘zhangsan’, 20 , empty_blob() );
…
ii.更新該紀(jì)錄的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);
…
(待續(xù))
