Access to BLOB fields in Oracle databases

This problem has been encountered in the recent access to BLOB fields in the Oracle database. You need to store a file or file stream in the Oracle database. Oracle8 provides Blob and Clob to store binary big object data, but it and Java. SQL. blob is incompatible, which often causes the Blob field to be unable to be locked or the operation fails. In short, 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 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 the byte array java. io. fileInputStream fin = null; java. io. fileOutputStream fout = null; oracle. jdbc. oracleResultSet ors = null; // ** here rs must use Oracle provided by oracle. jdbc. driver. oraclePreparedStatement opst = null; // *** PreparedStatement // try {provided by Oracle {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, and use fin = new java to compare with the source File. 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 the file into the byte array for (; itotal {I = fin. read (a, itotal, f Length-itotal);} fin. close (); System. out. println ("read itotal:" + itotal);/** note that the BLOB of Oracle must use EMPTY_BLOB () to initialize 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.exe cuteUpdate (); opst. clearParameters ();/** after inserting other data, locate the BLOB field 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.exe cuteQuery (); if (ors. next () {oracle. SQL. BLOB blob = ors. getBLOB (1);/** get the BLOB field int j = blob. putBytes (1, a);/** write the byte array to the BLOB field System. out. println ("j:" + j); db1.conn. commit (); ors. close ();} System. out. println ("insert into OK"); byte B [] = null;/** save Byte 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.exe cuteQuery (); 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 byte read from BLOB to 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 {/** disable all data connections to stmt. close (); db1.closeConn ();}}}

 

Compile and run the program in TomCat. It should be noted that the Blob access process generally stores BLOB-related control data first, such as the file name, and then queries and locates the BLOB field, using the methods provided by OracleBlob:

　　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.