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

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