Oracle中JDBC对BLOB和CLOB读取的专用处理和通用处理

try{
       ResultSet rs = (ResultSet)result;
       rs.next();
        BLOB xmlBlob = (BLOB)rs.getBlob(1);
        InputStream is = xmlBlob.getBinaryStream();
        BufferedInputStream input = new BufferedInputStream(is);
        byte[] buff = new byte[2048];
        int bytesRead = 0;
        while(-1 != (bytesRead = input.read(buff, 0, buff.length))) {
         //在这里执行写入，如写入到文件的

System.out.println(new String(buffer))}

设有表：
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类型。这将在后边陆续进行介绍。
To be contd.