Access to BLOB fields in Oracle databases

The following articles mainly introduce the access to BLOB fields in Oracle databases. I often encounter the access to BLOB fields in Oracle databases, the requirement is to store a file or file stream to the Oracle database. Oracle8 provides Blob and Clob to store binary big object data.

However, it is not compatible with Blob in Java. SQL. It often causes the Blob field to be unable to be locked or the operation fails. In summary, I have shared some experiences.

First, create a test data table.

 
 

  
  
drop table filelist;  
  
  
commit;  
  
  
CREATE TABLE SYSTEM.FILELIST (  
  
  
"FILENAME" VARCHAR2(50) NOT NULL,  
  
  
"FILESIZE" NUMBER(20) NULL,  
  
  
"FILEBODY" BLOB NULL,  
  
  
PRIMARY KEY("FILENAME"), UNIQUE("FILENAME")) ;  
  
  
commit;  
 
 

During the test, first read the hard disk file into the Oracle database and then read it to another new file on the hard disk. The original code is as follows:

 
 

  
  
import java.io.*;  
  
  
import java.util.*;  
  
  
import java.sql.*;  
  
  
import oracle.sql.*;  
  
  
import oracle.jdbc.driver.*;  
  
  
import java.text.*;  
  
  
public class test  
  
  
{  
  
  
public static void main(String args[]) throws java.io.IOException,java.sql.SQLException  
  
  
{  
  
  
dbBean db1=new dbBean();  
  
  
/**  
 
 

* Here is my data connection Bean

* You can use your own connection Bean.

 
 

  
  
*/  
  
  
byte a[]=null;  
 
 

** Read the test file test.doc into this byte array

 
 

  
  
java.io.FileInputStream fin=null;  
  
  
java.io.FileOutputStream fout=null;  
  
  
oracle.jdbc.OracleResultSet ors=null；  
 
 

** Rs must be provided by the Oracle database.

 
 

  
  
oracle.jdbc.driver.OraclePreparedStatement opst=null; 
 
 

** PreparedStatement

Provided by Oracle

 
 

  
  
try  
  
  
{  
  
  
java.io.File f1=new java.io.File("c:/temp/test.doc");  
  
  
java.io.File f2=new java.io.File("c:/temp/testout.doc");  
 
 

** Write the Information read from BLOB

// Enter the file for comparison with the source file

 
 

  
  
fin=new java.io.FileInputStream(f1);  
  
  
fout=new java.io.FileOutputStream(f2);  
 
 

Int flength = (int) f1.length (); // ** the length of the byte used to read the file

 
 

  
  
System.out.println("file length::"+flength);  
  
  
a=new byte[flength];  
  
  
int i=0;int itotal=0;  
 
 

/** Read files into byte Arrays

 
 

  
  
for (;itotal<flength;iitotal=i+itotal )  
  
  
{  
  
  
i=fin.read(a,itotal,flength-itotal);  
  
  
}  
  
  
fin.close();  
  
  
System.out.println("read itotal::"+itotal);  
 
 

/** Note that the BLOB of the Oracle database must be initialized with EMPTY_BLOB ().

 
 

  
  
String mysql="insert into filelist (FileName,FileSize,FileBody) values (?,?,EMPTY_BLOB())";  
  
  
opst=(oracle.jdbc.driver.OraclePreparedStatement)db1.conn.prepareStatement(mysql);  
  
  
opst.setString(1,"wordtemplate");  
  
  
opst.setInt (2,flength);  
  
  
opst.executeUpdate();  
  
  
opst.clearParameters();  
 
 

/** Locate the BLOB field after inserting other data

 
 

  
  
mysql="select filebody from filelist where filename=?";  
  
  
opst=(oracle.jdbc.driver.OraclePreparedStatement)db1.conn.prepareStatement(mysql);  
  
  
opst.setString(1,"wordtemplate");  
  
  
ors=(oracle.jdbc.OracleResultSet)opst.executeQuery();  
  
  
if (ors.next())  
  
  
{  
 
 

Oracle. SQL. BLOB blob = ors. getBLOB (1);/** get the BLOB Field

Int j = blob. putBytes (1, a);/** write the byte array into the BLOB Field

 
 

  
  
System.out.println("j:"+j);  
  
  
db1.conn.commit();  
  
  
ors.close();  
  
  
}  
  
  
System.out.println("insert into ok");  
 
 

Byte B [] = null;/** Save the bytes read from BLOB

 
 

  
  
opst.clearParameters();  
  
  
mysql="select filebody from filelist where filename=?";  
  
  
opst=(oracle.jdbc.driver.OraclePreparedStatement)db1.conn.prepareStatement(mysql);  
  
  
opst.setString(1,"wordtemplate");  
  
  
ors=(oracle.jdbc.OracleResultSet)opst.executeQuery();  
  
  
if (ors.next())  
  
  
{  
  
  
oracle.sql.BLOB blob2=ors.getBLOB(1);  
  
  
System.out.println("blob2 length:"+blob2.length());  
 
 

B = blob2.getBytes (1, flength);/** retrieve byte stream data from BLOB

 
 

  
  
System.out.println("b length::"+b.length);  
  
  
db1.conn.commit();  
  
  
}  
  
  
ors.close();  
 
 

/** Write the bytes read from BLOB into the file

 
 

  
  
fout.write(b,0,b.length);  
  
  
fout.close();  
  
  
System.out.println("write itotal::"+b.length);  
  
  
}  
  
  
catch(Exception e)  
  
  
{  
  
  
System.out.println("errror :"+e.toString() );  
  
  
e.printStackTrace();  
  
  
}  
  
  
finally  
 
 

{/** Close all data connections

 
 

  
  
stmt.close();  
  
  
db1.closeConn();  
  
  
}  
  
  
}  
  
  
}  
 
 

Compile and run the program in TomCat.

It should be noted that the Blob access process is generally first stored in BLOB-related control data, such as the file name, then queried and located BLOB fields, using the methods provided by the Oracle Database Blob:

 
 

  
  
public int putBytes(long pos,byte bytes[])  
  
  
public byte[] getBytes(long pos,byte bytes[])  
 
 

Or use

 
 

  
  
public OutputStream getBinaryOutputStream() throws SQLException  
  
  
public InputStream getBinaryStream() throws SQLException  
 
 

Because the input and output streams are always used to buffer streams in byte arrays, we will not give an example.