JDBC操作BFILE字段

 

import java.io.*;
import java.sql.*;

import oracle.sql.*;
import oracle.jdbc.*;

public class JdbcBfile {
    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:@db_server:1521:SID","username","password");
            stmt = conn.createStatement();
            conn.setAutoCommit(false);
            String dbDir = "LOB_DIR";
            String targetDir = "C:\\";
            String fileName = "a.zip";
            System.out.println("Adding BFILE to db...");
            addBfile(stmt,dbDir, fileName);
            System.out.println("Retrieving BFILE from db...");
            retrieveBfile(stmt,targetDir,fileName);
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally{
            try{
                stmt.close();
                conn.close();
            }
            catch(SQLException e){
                e.printStackTrace();
            }
        }
    }
    
    private static void addBfile(
            Statement stmt, 
            String directory,
            String fileName){
        String sqlInsert = "INSERT INTO bfile_content VALUES('"+fileName+"', bfilename('"+directory+"','"+fileName+"'))";
        System.out.println(sqlInsert);
        
        try{
            stmt.executeUpdate(sqlInsert);
            stmt.execute("COMMIT");
            System.out.println("Added pointer to file"+ fileName+" to BFILE in DB Directory "+directory+"\n");
        }
        catch(SQLException e){
            System.out.println("Error Code: "+e.getErrorCode());
            System.out.println("Error Message: "+e.getMessage());
            e.printStackTrace();
        }
        
      }
    
    private static void retrieveBfile(
            Statement stmt,
            String targetDir,
            String fileName){
        String sqlSelect = "SELECT bfile_column FROM bfile_content WHERE file_name='"+fileName+"'";
        ResultSet bfileRS = null;
        try{
            //step1: retrieve the row containing BFILE locator
            bfileRS = stmt.executeQuery(sqlSelect);
            bfileRS.next();
            System.out.println(sqlSelect);
            
            //step2: create a BFILE obj and read the locator
            BFILE myBfile = ((OracleResultSet) bfileRS).getBFILE("bfile_column");
            
            //step3: get the file name from BFILE obj
            String bfileName = myBfile.getName();

            //step4: check the external file exists
            myBfile.fileExists();

            //step5: open the external file
            myBfile.openFile();

            //step6: create an input stream to read the external file
            InputStream in = myBfile.getBinaryStream();
            
            //step7: save the file contents to a new file
            String saveFileName = targetDir+"retrievedBFILE"+bfileName;
            saveFile(in, saveFileName);

            //step8: close the input stream
            in.close();
            myBfile.closeFile();
            
            System.out.println("Retrieved BFILE and saved to "+saveFileName);
        }
        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();
        }
    }
    
    private static void saveFile(
            InputStream in, 
            String saveFileName){
        try{
            File file = new File(saveFileName);
            FileOutputStream out = new FileOutputStream(file);
            byte[] byteBuffer = new byte[8132];
            int bytesRead;            
            while((bytesRead = in.read(byteBuffer)) != -1){
                out.write(byteBuffer);
            }            
            out.close();
        }
        catch(IOException e){
            e.printStackTrace();
        }
    
    }
}