Oracle blob字段类型 文件读写实例

Oracle的Blob字段比较特殊，他比long字段的性能要好很多，可以用来保存例如图片之类的二进制数据。

准备环境：pl/sql  java JDK  Oracle JDBC driver

创建blob_test表

create table blob_test(

id number,

name varchar(50),

word blob                     

)

先插入空白数据然后执行更新

Code:

package sql;     //JDBC connect management for Oracle require Oracle Jdbc driver

import java.sql.Connection;

import java.sql.DriverManager;

public class DBConnection {

    public static Connection getDBConnection() throws Exception {

        Connection con = null;

        String driver = "oracle.jdbc.driver.OracleDriver";//database driver

        String url = "jdbc:oracle:thin:@localhost:1521:ORCL";//database URL

        String user = "system";           //database name        

        String password = "3edc4rfv";       //database Password

        Class.forName(driver);

        con = DriverManager.getConnection(url, user, password);

        return con;       

    }

}

---------------------------------------------------------------------------------------------------------------------------------

package sql;

import java.io.*;

import java.sql.*;

import oracle.sql.BLOB;

import sql.DBConnection;

public class bolb_test {

    public static void main(String[] args) throws SQLException {

            Connection con=null ;      

            long start=System.currentTimeMillis();          //count runtime

            CallableStatement pstmt=null;  

            InputStream fin=null;

            OutputStream outStream=null;

            String path = "D:\\test.txt";

            File file = new File(path);

         try{

            con = DBConnection.getDBConnection();

            con.setAutoCommit(false);

            String sql="insert into blob_test values(?,?,?)";

//insert database

            pstmt = con.prepareCall(sql);

            for(int i=0;i<10;i++)

            {  

            String name = "Young_"+i;

            int id = 1;

            pstmt.setInt(1,id+i);

            pstmt.setString(2, name);

            Blob word = BLOB.empty_lob();

            pstmt.setBlob(3, word); 

            pstmt.execute();

            }

            System.out.print("instert ok\n");

            Statement stmt = con.createStatement();

            ResultSet rs =stmt.executeQuery("select id,word from blob_test for update");

            //get specially columns and rows for update

            while(rs.next()) {

                        //System.out.print(rs.getInt(1)+rs.getString(2)+"\n");//print select sql for debug

                BLOB blob = (BLOB) rs.getBlob("word");

                outStream = blob.getBinaryOutputStream();

                fin = new FileInputStream(file);  //put file into stream

                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();

            }

                System.out.print("\nupdate ok\n");

                con.commit(); 

         }

          catch (Exception e) {

            e.printStackTrace();

        }

         con.close();

         long end=System.currentTimeMillis();

         System.out.println(end-start);

    }

}  

读取blob文件

package sql;

import java.io.*;

import java.sql.*;

import oracle.sql.BLOB;

import sql.DBConnection;

public class getbolb_test {

         public static void main(String[] args) throws SQLException {

                       Connection con=null ;               

                       long start=System.currentTimeMillis();               //count runtime  

                       String path = "D:\\test1.txt";

                       File file = new File(path);

                    try{

                            con = DBConnection.getDBConnection();

                            con.setAutoCommit(false);

                            Statement stmt = con.createStatement();

                            ResultSet rs =stmt.executeQuery("select id,word from blob_test where id=10");

                            //get blob form your table

                            if(rs.next()) {

                                     BLOB blob = (BLOB) rs.getBlob("word");

//get word column

                FileOutputStream output = new FileOutputStream(file);

// define a file output stream

                InputStream input = blob.getBinaryStream();//put blob into input

                byte[] buffer = new byte[blob.getBufferSize()];

//if use 1024 it will lose some bytes

                int len = 0;

                while ((len = input.read(buffer)) != -1) {

                //get all input stream into output file stream

                output.write(buffer, 0, len);

                input.close();

                output.flush();

                output.close();

                                         }

                System.out.print("\ndownload ok\n");

                            }

                    }

                     catch (Exception e) {

                            e.printStackTrace();

                   }

                    con.close();

                    long end=System.currentTimeMillis();

                    System.out.println(end-start);

         }

}