使用Jdbc对Blob和Clob进行处理的两种方法

来源：互联网发布：企业做账用什么软件编辑：程序博客网时间：2024/05/22 17:09

一、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 "";         }     }

0 0