设有表： createtableblobimg(idintprimarykey,contentsblob); 一、BLOB入库的专用访问： 1)最常见于Oracle的JDBC示例中 一般是先通过select...forupdate锁定blob列，然后写入blob值，然后提交。要用到特定的OracleBLOB类。 Class.forName(oracle.jdbc.drive
设有表：
create table blobimg (id int primary key, contents blob);
一、blob入库的专用访问：
1) 最常见于oracle的jdbc示例中
一般是先通过select ... for update锁定blob列，然后写入blob值，然后提交。要用到特定的oracle blob类。
class.forname("oracle.jdbc.driver.oracledriver");
connection con = drivermanager.getconnection("jdbc:oracle:thin:@localhost:1521:testdb", "test", "test");
//处理事务
con.setautocommit(false);
statement st = con.createstatement();
//插入一个空对象
st.executeupdate("insert into blobimg values(1,empty_blob())");
//用for update方式锁定数据行
resultset rs = st.executequery(
"select contents from blobimg where id=1 for update");
if (rs.next()) {
//使用oracle.sql.blob类，没办法了，变成专用的了
oracle.sql.blob blob = (oracle.sql.blob) rs.getblob(1).;
//到数据库的输出流
outputstream outstream = blob.getbinaryoutputstream();
//这里用一个文件模拟输入流
file file = new file("d://proxy.txt");
inputstream fin = new fileinputstream(file);
//将输入流写到输出流
byte[] b = new byte[blob.getbuffersize()];
int len = 0;
while ( (len = fin.read(b)) != -1) {
outstream.write(b, 0, len);
}
//依次关闭
fin.close();
outstream.flush();
outstream.close();
}
con.commit();
con.close();
2) 再厉害一点的，是通过调用dbms_lob包中的一些函数来处理，效率好像也不错.
不过，要使用到存储过程，用到专用类oraclecallablestatement。
例：
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
class testblobwritebydbms_lob {
public static void main (string args []) throws sqlexception ,
filenotfoundexception, ioexception
{
drivermanager.registerdriver (new oracle.jdbc.driver.oracledriver());
connection conn =
drivermanager.getconnection("jdbc:oracle:thin:@localhost:1521:ora92","scott","tiger");
conn.setautocommit(false);
statement stmt = conn.createstatement();
stmt.execute( "delete from demo" );
system.out.println( "deleted from demo" );
stmt.execute( "insert into demo (id,theblob) values (s_enr.nextval,empty_blob())" );
conn.commit();
system.out.println( "committed" );
resultset rset = stmt.executequery ("select theblob from demo where id = s_enr.currval for update");
system.out.println( "executed query" );
if(rset.next())
{
system.out.println( "fetched row " );
blob l_mapblob = ((oracleresultset)rset).getblob(1);
file binaryfile = new file("e://free//jo.jpg");
fileinputstream instream=new fileinputstream(binaryfile);
int chunk = 32000;
system.out.println( "chunk = "+ chunk );
byte[] l_buffer = new byte[chunk];
int l_nread = 0;
oraclecallablestatement cstmt =
(oraclecallablestatement)conn.preparecall( "begin dbms_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值读取的通用处理：
这个jdbc标准接口可以直接调用，因此比较简单，如下所示：
connection con = connectionfactory.getconnection();
con.setautocommit(false);
statement st = con.createstatement();
resultset rs = st.executequery("select contents from blobimg where id=1");
if (rs.next()) {
java.sql.blob blob = rs.getblob(1);
inputstream ins = blob.getbinarystream();
//输出到文件
file file = new file("d://output.txt");
outputstream fout = new fileoutputstream(file);
//下面将blob数据写入文件
byte[] b = new byte[1024];
int len = 0;
while ( (len = ins.read(b)) != -1) {
fout.write(b, 0, len);
}
//依次关闭
fout.close();
ins.close();
}
con.commit();
con.close();
三、blob值写入的通用处理：
这时要借助于preparedstatement的动态绑定功能，借用其setobject()方法插入字节流到blob字段。
public void insertfile(file f) throws exception{
fileinputstream fis=new fileinputstream(f,connection conn);
byte[] buffer=new byte[1024];
data=null;
int sept=0;int len=0;
while((sept=fis.read(buffer))!=-1){
if(data==null){
len=sept;
data=buffer;
}else{
byte[] temp;
int templength;
templength=len+sept;
temp=new byte[templength];
system.arraycopy(data,0,temp,0,len);
system.arraycopy(buffer,0,temp,len,sept);
data=temp;
len=templength;
}
if(len!=data.length()){
byte temp=new byte[len];
system.arraycopy(data,0,temp,0,len);
data=temp;
}
}
string sql="insert into filedata (filename,blobdata) value(?,?)";
preparedstatement ps=conn.preparestatement(sql);
ps.setstring(1,f.getname());
ps.setobject(2,data);
ps.executeupdate();
}
四. CLOB读取的通用处理
public static String getClobString(ResultSet rs, int col) {
try {
Clob c=resultSet.getClob(2);
Reader reader=c.getCharacterStream():
if (reader == null) {
return null;
}
StringBuffer sb = new StringBuffer();
char[] charbuf = new char[4096];
for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) {
sb.append(charbuf, 0, i);
}
return sb.toString();
} catch (Exception e) {
return "";
}
}
当然还可以直接编写BLOB存取的存储过程供JDBC调用，那也非常方便。不过可能要用到外部LOB类型。这将在后边陆续进行介绍。
