設(shè)有表：
create table blobimg(idintprimarykey,contentsblob);
一、BLOB入庫(kù)的專用訪問(wèn)：
1)最常見(jiàn)于Oracle的JDBC示例中
一般是先通過(guò)select...forupdate鎖定blob列，然后寫入blob值，然后提交。要用到特定的OracleBLOB類。
Class.forName("oracle.jdbc.driver.OracleDriver");
Connectioncon=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:testdb","test","test");
//處理事務(wù)
con.setAutoCommit(false);
Statementst=con.createStatement();
//插入一個(gè)空對(duì)象
st.executeUpdate("insertintoBLOBIMGvalues(1,empty_blob())");
//用forupdate方式鎖定數(shù)據(jù)行
ResultSetrs=st.executeQuery(
"selectcontentsfromBLOBIMGwhereid=1forupdate");
if(rs.next()){
//使用oracle.sql.BLOB類，沒(méi)辦法了，變成專用的了
oracle.sql.BLOBblob=(oracle.sql.BLOB)rs.getBlob(1).;
//到數(shù)據(jù)庫(kù)的輸出流
OutputStreamoutStream=blob.getBinaryOutputStream();
//這里用一個(gè)文件模擬輸入流
Filefile=newFile("d:\\proxy.txt");
InputStreamfin=newFileInputStream(file);
//將輸入流寫到輸出流
byte[]b=newbyte[blob.getBufferSize()];
intlen=0;
while((len=fin.read(b))!=-1){
outStream.write(b,0,len);
}
//依次關(guān)閉
fin.close();
outStream.flush();
outStream.close();
}
con.commit();
con.close();
2)再厲害一點(diǎn)的，是通過(guò)調(diào)用DBMS_LOB包中的一些函數(shù)來(lái)處理，效率好像也不錯(cuò).
不過(guò)，要使用到存儲(chǔ)過(guò)程，用到專用類OracleCallableStatement。
例：
importjava.sql.*;
importjava.io.*;
importoracle.jdbc.driver.*;
importoracle.sql.*;
classTestBlobWriteByDBMS_LOB{
publicstaticvoidmain(Stringargs[])throwsSQLException,
FileNotFoundException,IOException
{
DriverManager.registerDriver(neworacle.jdbc.driver.OracleDriver());
Connectionconn=
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora92","scott","tiger");
conn.setAutoCommit(false);
Statementstmt=conn.createStatement();
stmt.execute("deletefromdemo");
System.out.println("deletedfromdemo");
stmt.execute("insertintodemo(id,theBlob)values(s_enr.nextval,empty_blob())");
conn.commit();
System.out.println("committed");
ResultSetrset=stmt.executeQuery("SELECTtheBlobFROMdemowhereid=s_enr.currvalFORUPDATE");
System.out.println("ExecutedQuery");
if(rset.next())
{
System.out.println("Fetchedrow");
BLOBl_mapBLOB=((OracleResultSet)rset).getBLOB(1);
FilebinaryFile=newFile("e:\\free\\jo.jpg");
FileInputStreaminstream=newFileInputStream(binaryFile);
intchunk=32000;
System.out.println("Chunk="+chunk);
byte[]l_buffer=newbyte[chunk];
intl_nread=0;
OracleCallableStatementcstmt=
(OracleCallableStatement)conn.prepareCall("begindbms_lob.writeappend(:1,:2,:3);end;");
cstmt.registerOutParameter(1,OracleTypes.BLOB);
while((l_nread=instream.read(l_buffer))!=-1)
{
cstmt.setBLOB(1,l_mapBLOB);
cstmt.setInt(2,l_nread);
cstmt.setBytes(3,l_buffer);
cstmt.executeUpdate();
l_mapBLOB=cstmt.getBLOB(1);
}
instream.close();
conn.commit();
rset.close();
stmt.close();
conn.close();
}
}
}
二、BLOB值讀取的通用處理：
這個(gè)jdbc標(biāo)準(zhǔn)接口可以直接調(diào)用，因此比較簡(jiǎn)單，如下所示：
Connectioncon=ConnectionFactory.getConnection();
con.setAutoCommit(false);
Statementst=con.createStatement();
ResultSetrs=st.executeQuery("selectcontentsfromBLOBIMGwhereid=1");
if(rs.next()){
java.sql.Blobblob=rs.getBlob(1);
InputStreamins=blob.getBinaryStream();
//輸出到文件
Filefile=newFile("d:\\output.txt");
OutputStreamfout=newFileOutputStream(file);
//下面將BLOB數(shù)據(jù)寫入文件
byte[]b=newbyte[1024];
intlen=0;
while((len=ins.read(b))!=-1){
fout.write(b,0,len);
}
//依次關(guān)閉
fout.close();
ins.close();
}
con.commit();
con.close();
三、BLOB值寫入的通用處理：
這時(shí)要借助于PreparedStatement的動(dòng)態(tài)綁定功能，借用其setObject()方法插入字節(jié)流到BLOB字段。
publicvoidinsertFile(Filef)throwsException{
FileInputStreamfis=newFileInputStream(f,Connectionconn);
byte[]buffer=newbyte[1024];
data=null;
intsept=0;intlen=0;
while((sept=fis.read(buffer))!=-1){
if(data==null){
len=sept;
data=buffer;
}else{
byte[]temp;
inttempLength;
tempLength=len+sept;
temp=newbyte[tempLength];
System.arraycopy(data,0,temp,0,len);
System.arraycopy(buffer,0,temp,len,sept);
data=temp;
len=tempLength;
}
if(len!=data.length()){
bytetemp=newbyte[len];
System.arraycopy(data,0,temp,0,len);
data=temp;
}
}
Stringsql="insertintofileData(filename,blobData)value(?,?)";
PreparedStatementps=conn.prepareStatement(sql);
ps.setString(1,f.getName());
ps.setObject(2,data);
ps.executeUpdate();
}
四.CLOB讀取的通用處理
publicstaticStringgetClobString(ResultSetrs,intcol){
try{
Clobc=resultSet.getClob(2);
Readerreader=c.getCharacterStream():
if(reader==null){
returnnull;
}
StringBuffersb=newStringBuffer();
char[]charbuf=newchar[4096];
for(inti=reader.read(charbuf);i>0;i=reader.read(charbuf)){
sb.append(charbuf,0,i);
}
returnsb.toString();
}catch(Exceptione){
return"";
}
}
當(dāng)然還可以直接編寫B(tài)LOB存取的存儲(chǔ)過(guò)程供JDBC調(diào)用，那也非常方便。不過(guò)可能要用到外部LOB類型。