jdbc处理blob对象编程源代码

设有表：

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:@local host: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();

}

https://www.360docs.net/doc/727833509.html,mit();

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.OracleDr iver());

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.ne xtval,empty_blob())" );

https://www.360docs.net/doc/727833509.html,mit();

System.out.println( "committed" );

ResultSet rset = stmt.executeQuery ("SELECT theBlob FROM dem o 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 dbm s_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();

https://www.360docs.net/doc/727833509.html,mit();

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();

}

https://www.360docs.net/doc/727833509.html,mit();

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(cha rbuf)) {

sb.append(charbuf, 0, i);

}

return sb.toString();

} catch (Exception e) {

return "";

}

}

当然还可以直接编写BLOB存取的存储过程供JDBC调用，那也非常方便。不过可能要用到外部LOB类型。这将在后边陆续进行介绍。

To be contd.