使用JDBC对BLOB和CLOB进行处理

 

JDBCOracleSQLF#

从网上看到这篇文章，转过来做为学习用

设有表： create table blobimg (id int primary key, contents blob); 一、BLOB入库的专用访问：     1) 最常见于Oracle的JDBC示例中     一般是先通过select  ... for update锁定blob列，然后写入blob值，然后提交。要用到特定的Oracle BLOB类。

Java代码 复制代码 收藏代码
  1. Class.forName("oracle.jdbc.driver.OracleDriver");  
  2.     Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:testdb", "test", "test");  
  3.     //处理事务  
  4.     con.setAutoCommit(false);  
  5.     Statement st = con.createStatement();  
  6.     //插入一个空对象  
  7.     st.executeUpdate("insert into BLOBIMG  values(1,empty_blob())");  
  8.     //用for update方式锁定数据行  
  9.     ResultSet rs = st.executeQuery(  
  10.               "select contents from  BLOBIMG  where  id=1  for update");  
  11.     if (rs.next()) {  
  12.     //使用oracle.sql.BLOB类，没办法了，变成专用的了  
  13.     oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob(1).;  
  14.     //到数据库的输出流  
  15.     OutputStream outStream = blob.getBinaryOutputStream();  
  16.     //这里用一个文件模拟输入流  
  17.     File file = new File("d:\\proxy.txt");  
  18.     InputStream fin = new FileInputStream(file);  
  19.     //将输入流写到输出流  
  20.     byte[] b = new byte[blob.getBufferSize()];  
  21.     int len = 0;  
  22.     while ( (len = fin.read(b)) != -1) {  
  23.               outStream.write(b, 0, len);  
  24.     }  
  25.     //依次关闭  
  26.     fin.close();  
  27.     outStream.flush();  
  28.     outStream.close();  
  29.     }  
  30.     con.commit();  
  31.     con.close();  
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。

Java代码 复制代码 收藏代码
  1. import java.sql.*;  
  2.     import java.io.*;  
  3.     import oracle.jdbc.driver.*;  
  4.     import oracle.sql.*;  
  5.     class TestBlobWriteByDBMS_LOB {  
  6.       
  7.         public static void main (String args []) throws SQLException ,   
  8.         FileNotFoundException, IOException  
  9.         {  
  10.             DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());  
  11.             Connection conn =   
  12.                 DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora92","scott","tiger");  
  13.             conn.setAutoCommit(false);  
  14.             Statement stmt = conn.createStatement();  
  15.             stmt.execute( "delete from demo" );  
  16.             System.out.println( "deleted from demo" );  
  17.             stmt.execute( "insert into demo (id,theBlob) values (s_enr.nextval,empty_blob())" );  
  18.             conn.commit();  
  19.             System.out.println( "committed" );  
  20.             ResultSet rset = stmt.executeQuery ("SELECT theBlob FROM  demo where id = s_enr.currval  FOR UPDATE");  
  21.             System.out.println( "Executed Query" );  
  22.             if(rset.next())  
  23.             {  
  24.                 System.out.println( "Fetched row " );  
  25.                 BLOB l_mapBLOB = ((OracleResultSet)rset).getBLOB(1);  
  26.                 File binaryFile = new File("e:\\free\\jo.jpg");  
  27.                 FileInputStream instream=new  FileInputStream(binaryFile);  
  28.                 int chunk = 32000;  
  29.                   
  30.                 System.out.println( "Chunk = "+ chunk );  
  31.                   
  32.                 byte[] l_buffer = new byte[chunk];  
  33.                 int l_nread = 0;  
  34.                   
  35.                 OracleCallableStatement cstmt =  
  36.                     (OracleCallableStatement)conn.prepareCall( "begin dbms_lob.writeappend( :1, :2, :3 ); end;" );  
  37.                 cstmt.registerOutParameter( 1, OracleTypes.BLOB );  
  38.                 while ((l_nread= instream.read(l_buffer)) != -1)   
  39.                 {  
  40.                     cstmt.setBLOB(  1, l_mapBLOB );  
  41.                     cstmt.setInt(   2, l_nread );  
  42.                     cstmt.setBytes( 3, l_buffer );  
  43.                     cstmt.executeUpdate();  
  44.                     l_mapBLOB = cstmt.getBLOB(1);  
  45.                 }  
  46.                 instream.close();  
  47.                 conn.commit();  
  48.                 rset.close();  
  49.                 stmt.close();  
  50.                 conn.close();  
  51.             }  
  52.         }  
  53.     }  
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标准接口可以直接调用，因此比较简单，如下所示：

Java代码 复制代码 收藏代码
  1. Connection con = ConnectionFactory.getConnection();  
  2.    con.setAutoCommit(false);  
  3.    Statement st = con.createStatement();  
  4.    ResultSet rs = st.executeQuery("select contents from  BLOBIMG  where  id=1");  
  5.    if (rs.next()) {  
  6.        java.sql.Blob blob = rs.getBlob(1);  
  7.        InputStream ins = blob.getBinaryStream();  
  8.        //输出到文件  
  9.        File file = new File("d:\\output.txt");  
  10.        OutputStream fout = new FileOutputStream(file);  
  11.        //下面将BLOB数据写入文件  
  12.        byte[] b = new byte[1024];  
  13.        int len = 0;  
  14.        while ( (len = ins.read(b)) != -1) {  
  15.          fout.write(b, 0, len);  
  16.        }  
  17.        //依次关闭  
  18.        fout.close();  
  19.        ins.close();  
  20.    }      
  21.    con.commit();  
  22.    con.close();  
 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字段。

Java代码 复制代码 收藏代码
  1. public void insertFile(File f) throws Exception{   
  2.         FileInputStream fis=new FileInputStream(f,Connection conn);   
  3.         byte[] buffer=new byte[1024];   
  4.         data=null;   
  5.         int sept=0;int len=0;   
  6.         while((sept=fis.read(buffer))!=-1){   
  7.             if(data==null){   
  8.             len=sept;   
  9.             data=buffer;   
  10.             }else{   
  11.                 byte[] temp;   
  12.                 int tempLength;   
  13.                 tempLength=len+sept;   
  14.                 temp=new byte[tempLength];   
  15.                 System.arraycopy(data,0,temp,0,len);   
  16.                 System.arraycopy(buffer,0,temp,len,sept);   
  17.                 data=temp;   
  18.                 len=tempLength;   
  19.             }   
  20.             if(len!=data.length()){   
  21.             byte temp=new byte[len];   
  22.             System.arraycopy(data,0,temp,0,len);   
  23.             data=temp;   
  24.             }   
  25.         }   
  26.         String sql="insert into fileData (filename,blobData) value(?,?)";   
  27.         PreparedStatement ps=conn.prepareStatement(sql);   
  28.         ps.setString(1,f.getName());   
  29.         ps.setObject(2,data);   
  30.         ps.executeUpdate();  
  31.     }  
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读取的通用处理

Java代码 复制代码 收藏代码
  1. public static String getClobString(ResultSet rs, int col) {   
  2.        try {   
  3.            Clob c=resultSet.getClob(2);   
  4.            Reader reader=c.getCharacterStream():   
  5.            if (reader == null) {   
  6.                return null;   
  7.            }   
  8.            StringBuffer sb = new StringBuffer();   
  9.            char[] charbuf = new char[4096];   
  10.            for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) {   
  11.                sb.append(charbuf, 0, i);   
  12.            }   
  13.            return sb.toString();   
  14.        } catch (Exception e) {   
  15.            return "";   
  16.        }   
  17.    }  
posted @ 2014-01-20 10:29  roilat  阅读(250)  评论(0)    收藏  举报