JDBC操作Oracle CLOB对象

把C:\TEMP\cbr_order_version_exp.log内容存入CLOB字段中，并将内容从数据库又读入C:\temp\retrieved\cbr_order_version_exp.log文件中

---创建表
SQL>create table clob_content(file_name varchar2(40) not null, clob_column clob not null);

---确认文件
C:\TEMP>dir cbr_order*.log
 Volume in drive C is OS
 Volume Serial Number is 94D2-727F

 Directory of C:\TEMP

02/06/2012  06:03 PM             1,309 cbr_order_version_exp.log

---java代码

package com.ssgm.jyu.jdbc;
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.*;

public class JdbcClob {
    public static void main(String[] args){
        Connection conn = null;
        Statement stmt = null;
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }
        catch(ClassNotFoundException e){
            e.printStackTrace();
        }
        
        try{
            conn = DriverManager.getConnection("jdbc:oracle:thin:@hostname:1521:SID","username","password");
            stmt = conn.createStatement();
            conn.setAutoCommit(false);
            String sourceDir = "C:\\temp\\";
            String targetDir = "C:\\temp\\retrieved\\";
            String fileName = "cbr_order_version_exp.log";
            
            System.out.println("Writing CLOB from "+sourceDir+fileName+" to table clob_content");
            writeCLOB(stmt,sourceDir+fileName);
            
            System.out.println("Reading CLOB from table clob_content to "+targetDir+fileName);
            readCLOB(stmt, fileName, sourceDir, targetDir);
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally{
            try{
                stmt.close();
                conn.close();
            }
            catch(SQLException e){
                e.printStackTrace();
            }
        }
        
    }
    
    public static void writeCLOB(Statement stmt, String fullName){
        ResultSet clobRS = null;
        try{
            String sqlInsert = "INSERT INTO CLOB_CONTENT VALUES('"+fullName+"',EMPTY_CLOB())";
            //step1: initialize LOB column to set the LOB locator
            stmt.executeUpdate(sqlInsert);

            //step2: retrieve the row containing the LOB locator
            clobRS = stmt.executeQuery("SELECT clob_column FROM clob_content WHERE file_name='"+fullName+"' FOR UPDATE");
            clobRS.next();        
            
            //step3: create a LOB obj and read the LOB locator
            CLOB myClob = ((OracleResultSet) clobRS).getCLOB("clob_column");
            
            //step4: get the chunk size of the LOB from the LOB object
            int chunkSize = myClob.getChunkSize();
            
            //step5: create a buffer to hold a block of data from the file
            char[] textBuffer = new char[chunkSize];
            
            //step6: create a file object
            File file = new File(fullName);
            
            //step7: create input stream objects to read the file contents
            FileInputStream in = new FileInputStream(file);
            InputStreamReader reader = new InputStreamReader(in);
            BufferedReader br = new BufferedReader(reader);
            
            //step8: read the file contents and write it to the LOB
            long position = 1;
            int charsRead;
            while((charsRead = br.read(textBuffer)) != -1){
                //write the buffer contents to myClob
                myClob.putChars(position, textBuffer);
                position = position + charsRead;
            }
            
            //step9: perform a commit
            stmt.execute("COMMIT");
            
            //step10: close the objects used to read the file
            br.close();
            reader.close();
            in.close();
            
            System.out.println("Wrote content from "+fullName+" to CLOB");        
        }
        catch(SQLException e){
            System.out.println("Error Code: "+e.getErrorCode());
            System.out.println("Error Message: "+e.getMessage());
            e.printStackTrace();
        }
        catch(IOException e){
            System.out.println("Error Message: "+e.getMessage());
            e.printStackTrace();
        }
        
        
    }
    
    public static void readCLOB(
            Statement stmt,
            String fileName,
            String sourceDir,
            String targetDir){
        
        String sqlSelect = "SELECT clob_column FROM clob_content WHERE file_name='"
            +sourceDir+fileName+"'";
        ResultSet clobRS = null;
        try{
            //step1: retrieve the row containing the LOB locator
            clobRS = stmt.executeQuery(sqlSelect);
            clobRS.next();
            
            //step2: create a LOB obj and read the LOB locator
            CLOB myClob = ((OracleResultSet) clobRS).getCLOB("clob_column");
            
            //step3: get the chunk size of the LOB from the LOB object
            int chunkSize = myClob.getChunkSize();
            
            //step4: create a buffer to hold a chunk of data
            //retrieved from the LOB object
            char[] textBuffer = new char[chunkSize];
            
            //step5: create a file object
            String saveFile = targetDir+"retrievedCLOB"+fileName;
            File file = new File(saveFile);
            
            //step6: create output stream obj to write the LOB
            //contents to the new file
            FileOutputStream out = new FileOutputStream(file);
            OutputStreamWriter writer = new OutputStreamWriter(out);
            BufferedWriter bw = new BufferedWriter(writer);
            
            //step7: get the length of the LOB contents from the LOB obj
            long clobLength = myClob.getLength();
            
            //step8: while the end of the LOB contents have not been reached,
            //read a chunk of data from LOB to buffer,
            //then write the buffer contents to the file
            for(long position = 1; position <=clobLength; position+=chunkSize){
                //read a chunk of data from myClob
                //and store it in the buffer
                int charsRead = myClob.getChars(position, chunkSize, textBuffer);
                
                //write the buffer contents to file
                bw.write(textBuffer);
            }
            
            //step9: close the stream objects
            bw.close();
            writer.close();
            out.close();
            
            System.out.println("Read CLOB and save file" + saveFile );
        }
        catch(SQLException e){
            System.out.println("Error Code: "+e.getErrorCode());
            System.out.println("Error Message: "+e.getMessage());
            e.printStackTrace();
        }
        catch(IOException e){
            System.out.println("Error Message: "+e.getMessage());
            e.printStackTrace();
        }
    }

}

---运行屏幕显示

Writing CLOB from C:\temp\cbr_order_version_exp.log to table clob_content
Wrote content from C:\temp\cbr_order_version_exp.log to CLOB
Reading CLOB from table clob_content to C:\temp\retrieved\cbr_order_version_exp.log
Read CLOB and save fileC:\temp\retrieved\retrievedCLOBcbr_order_version_exp.log

---数据库验证

SQL>  select * from clob_content;

FILE_NAME
--------------------------------------------------------------------------------
CLOB_COLUMN
--------------------------------------------------------------------------------
C:\temp\cbr_order_version_exp.log
;;;

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 06 February, 2012

---操作系统验证

C:\TEMP>cd retrieved

C:\TEMP\retrieved>dir
 Volume in drive C is OS
 Volume Serial Number is 94D2-727F

 Directory of C:\TEMP\retrieved

02/08/2012  03:45 PM    <DIR>          .
02/08/2012  03:45 PM    <DIR>          ..
02/08/2012  03:45 PM             8,132 retrievedCLOBcbr_order_version_exp.log
               1 File(s)          8,132 bytes
               2 Dir(s)  22,748,835,840 bytes free