JDBC操作BFILEフィールド

5214 ワード

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+"
"); } 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(); } } }