JDBC操作Oracle BLOB对象

最新推荐文章于 2023-06-19 14:15:55 发布

zhyuh_perl

最新推荐文章于 2023-06-19 14:15:55 发布

阅读量3.6k

点赞数

文章标签： oracle jdbc file string buffer stream

本文链接：https://blog.csdn.net/zhyuh_perl/article/details/7247324

版权

将文件C:\temp\cbr_order_version.dmp从本机存入Oracle数据库BLOB字段，又从数据库读出另存为C:\temp\retrieved\retrievedBLOBcbr_order_version.dmp。

//blob_content表结构

SQL> desc blob_content
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
FILE_NAME                                 NOT NULL VARCHAR2(100)
BLOB_COLUMN                               NOT NULL BLOB

//C:\temp\cbr_order_version.dmp文件属性，

//大小为8941568/1024/1024=8.527MB
C:\TEMP>dir cbr_order*.dmp
Volume in drive C is OS
Volume Serial Number is 94D2-727F

Directory of C:\TEMP

02/06/2012 06:03 PM         8,941,568 cbr_order_version.dmp
               1 File(s)      8,941,568 bytes
               0 Dir(s) 23,012,745,216 bytes free

//java代码

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

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

public class JdbcBlob {
   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:@Host:1521:SID","username","passwd");
           stmt = conn.createStatement();
           conn.setAutoCommit(false);
           String sourceDir = "C:\\temp\\";
           String targetDir = "C:\\temp\\retrieved\\";
           String fileName = "cbr_order_version.dmp";
           System.out.println("Writing BLOB to blob_content...");
           writeBLOB(stmt,sourceDir+fileName);
           System.out.println("Reading BLOB from blob_content...");
           readBLOB(stmt,fileName,sourceDir,targetDir);
       }
       catch(SQLException e){
           e.printStackTrace();
       }
       finally{
           try{
               stmt.close();
               conn.close();
           }
           catch(SQLException e){
               e.printStackTrace();
           }
       }
       }

       public static void writeBLOB(Statement stmt, String fullName){
           ResultSet blobRS = null;
           try{
               String sqlInsert = "INSERT INTO blob_content VALUES ('"+fullName+"',EMPTY_BLOB())";
               String sqlSelect = "SELECT blob_column FROM blob_content WHERE file_name='"+fullName+"' FOR UPDATE";

               //step1: initialize the LOB column to set the LOB locator
               stmt.executeUpdate(sqlInsert);

               //step2: retrieve the row containing the LOB locator
               blobRS = stmt.executeQuery(sqlSelect);
               blobRS.next();

               //step3: create a LOB obj and read the LOB locator
               BLOB myBlob = ((OracleResultSet) blobRS).getBLOB("blob_column");

               //step4: get the chunksize of the LOB from the LOB object
               int chunkSize = myBlob.getChunkSize();

               //step5: create a buffer to hold a block of data from the file
               byte[] byteBuffer = new byte[chunkSize];

               //step6: create a file obj to open the file
               File file = new File(fullName);

               //step7: create an input stream obj to read the file contents
               FileInputStream in = new FileInputStream(file);

               //step8: read the file contents and write it to the LOB
               long position = 1;
               int bytesRead;

               while((bytesRead = in.read(byteBuffer)) != -1){
                   //write the buffer contents to myBlob
                   myBlob.setBytes(position, byteBuffer);
                   position += bytesRead;
               }

               //step9: commit
               stmt.execute("COMMIT");

               //step10: close the objects used to read the file
               in.close();
               blobRS.close();

               System.out.println("Wrote content from "+fullName+" to BLOB\n");
           }
           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 readBLOB(
               Statement stmt,
               String fileName,
               String sourceDir,
               String targetDir){
           String sqlSelect = "SELECT blob_column FROM blob_content WHERE file_name='"+
              sourceDir+fileName+"'";
           ResultSet blobRS = null;
           try{
               //step1: retrieve the row containing the BLOB locator
               blobRS = stmt.executeQuery(sqlSelect);
               blobRS.next();

               //step2: create a LOB obj and read the LOB locator
               BLOB myBlob = ((OracleResultSet) blobRS).getBLOB("blob_column");

               //step3: get the chunk size of the LOB from the LOB obj
               int chunkSize = myBlob.getChunkSize();

               //setp4: create a buffer to hold a chunk of data from LOB
               byte[] byteBuffer = new byte[chunkSize];

               //step5: create a file obj
               String saveFile = targetDir + "retrievedBLOB"+fileName;
               File file = new File(saveFile);

               //step6: create output stream obj to write the LOB contents
               FileOutputStream out = new FileOutputStream(file);

               //step7: get the long of LOB contents
               long blobLength = myBlob.length();

               //step8: read a chunk of data from myBlob,
               //then write the buffer contents to file
               for (long position=1; position<=blobLength; position += chunkSize){
                   int bytesRead = myBlob.getBytes(position, chunkSize, byteBuffer);
                   out.write(byteBuffer);
               }

               //step9: close the stream obj
               out.close();

               System.out.println("Read BLOB 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 BLOB to blob_content...
Wrote content from C:\temp\cbr_order_version.dmp to BLOB

Reading BLOB from blob_content...
Read BLOB and save fileC:\temp\retrieved\retrievedBLOBcbr_order_version.dmp

//验证

//程序运行前USERS表空间使用空间为81M

SQL> select sum(bytes)/1024/1024 MB from dba_extents where tablespace_name='USERS';
MB
----------
81

//程序运行后USERS表空间使用空间为89M

SQL> select sum(bytes)/1024/1024 MB from dba_extents where tablespace_name='USERS';

MB
----------
89

//用sql*plus检索
SQL> select * from blob_content;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> select FILE_NAME from blob_content;

FILE_NAME
--------------------------------------------------------------------------------
C:\temp\cbr_order_version.dmp

//检查读出来的文件

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

Directory of C:\TEMP\retrieved

02/10/2012 09:03 AM    <DIR>          .
02/10/2012 09:03 AM    <DIR>          ..
02/10/2012 09:16 AM         8,945,200 retrievedBLOBcbr_order_version.dmp
               1 File(s)      8,945,200 bytes
               2 Dir(s) 22,989,176,832 bytes free

zhyuh_perl

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
JDBC操作Oracle BLOB对象

将文件C:\temp\cbr_order_version.dmp从本机存入Oracle数据库BLOB字段，又从数据库读出另存为C:\temp\retrieved\retrievedBLOBcbr_order_version.dmp。//blob_content表结构SQL> desc blob_content Name
复制链接

扫一扫