Special processing and general processing for reading blob and clob by JDBC in Oracle

Table creation:
Create Table blobimg (ID int primary key, contents BLOB );
1. dedicated access to blob storage:
1) JDBC examples that are most common in Oracle
Generally, the Blob column is first locked Through select... for update, then the Blob value is written, and then submitted. Use a specific Oracle blob class.
Class. forname ("oracle. JDBC. Driver. oracledriver ");
Connection con = drivermanager. getconnection ("JDBC: oracle: thin: @ localhost: 1521: testdb", "test", "test ");
// Process the transaction
Con. setautocommit (false );
Statement ST = con. createstatement ();
// Insert an empty object
St.exe cuteupdate ("insert into blobimg values (1, empty_blob ())");
// Use the for update method to lock data rows
Resultset rs = st.exe cutequery (
"Select contents from blobimg where id = 1 for update ");
If (Rs. Next ()){
// Use the Oracle. SQL. Blob class, but it cannot be changed to dedicated
Oracle. SQL. Blob blob = (Oracle. SQL. Blob) Rs. getblob (1 ).;
// Output stream to the database
Outputstream outstream = blob. getbinaryoutputstream ();
// Here, an object is used to simulate the input stream.
File file = new file ("D: // proxy.txt ");
Inputstream fin = new fileinputstream (File );
// Write the input stream to the output stream
Byte [] B = new byte [blob. getbuffersize ()];
Int Len = 0;
While (LEN = fin. Read (B ))! =-1 ){
Outstream. Write (B, 0, Len );
}
// Close in sequence
Fin. Close ();
Outstream. Flush ();
Outstream. Close ();
}
Con. Commit ();
Con. Close ();

2) another amazing thing is to call some functions in the dbms_lob package for processing, and the efficiency seems to be good.
However, to use the stored procedure, use the dedicated class oraclecallablestatement.
Example:
Import java. SQL .*;
Import java. Io .*;
Import oracle. JDBC. Driver .*;
Import oracle. SQL .*;
Class testblobwritebydbms_lob {

Public static void main (string ARGs []) throws sqlexception,
Filenotfoundexception, ioexception
{
Drivermanager. registerdriver (New Oracle. JDBC. Driver. oracledriver ());
Connection conn =
Drivermanager. getconnection ("JDBC: oracle: thin: @ localhost: 1521: ora92", "Scott", "Tiger ");
Conn. setautocommit (false );
Statement stmt = conn. createstatement ();
Stmt.exe cute ("delete from demo ");
System. Out. println ("deleted from demo ");
Stmt.exe cute ("insert into demo (ID, theblob) values (s_enr.nextval, empty_blob ())");
Conn. Commit ();
System. Out. println ("committed ");
Resultset rset = stmt.exe cutequery ("select theblob from demo where id = s_enr.currval for update ");
System. Out. println ("executed query ");
If (rset. Next ())
{
System. Out. println ("fetched row ");
Blob l_mapblob = (oracleresultset) rset). getblob (1 );
File binaryfile = new file ("E: // free // jo.jpg ");
Fileinputstream instream = new fileinputstream (binaryfile );
Int chunk = 32000;

System. Out. println ("chunk =" + chunk );

Byte [] l_buffer = new byte [chunk];
Int l_nread = 0;

Oraclecallablestatement cstmt =
(Oraclecallablestatement) Conn. preparecall ("begin dbms_lob.writeappend (: 1,: 2,: 3); end ;");
Cstmt. registeroutparameter (1, oracletypes. Blob );
While (l_nread = instream. Read (l_buffer ))! =-1)
{
Cstmt. setblob (1, l_mapblob );
Cstmt. setint (2, l_nread );
Cstmt. setbytes (3, l_buffer );
Cstmt.exe cuteupdate ();
Rochelle mapblob = cstmt. getblob (1 );
}
Instream. Close ();
Conn. Commit ();
Rset. Close ();
Stmt. Close ();
Conn. Close ();
}
}
}
　

II. General processing for reading blob values:
This JDBC standard interface can be called directly, so it is relatively simple, as shown below:
Connection con = connectionfactory. getconnection ();
Con. setautocommit (false );
Statement ST = con. createstatement ();
Resultset rs = st.exe cutequery ("select contents from blobimg where id = 1 ");
If (Rs. Next ()){
Java. SQL. Blob blob = Rs. getblob (1 );
Inputstream ins = blob. getbinarystream ();
// Output to file
File file = new file ("D: // output.txt ");
Outputstream fout = new fileoutputstream (File );
// Write BLOB data to the file below
Byte [] B = new byte [1024];
Int Len = 0;
While (LEN = ins. Read (B ))! =-1 ){
Fout. Write (B, 0, Len );
}
// Close in sequence
Fout. Close ();
INS. Close ();
}
Con. Commit ();
Con. Close ();

Iii. General processing of Blob value writing:
In this case, we need to use the dynamic binding function of preparedstatement to use its setobject () method to insert byte streams to BLOB fields.
Public void insertfile (file F) throws exception {
Fileinputstream FCM = new fileinputstream (F, connection conn );
Byte [] buffer = new byte [1024];
Data = NULL;
Int Sept = 0; int Len = 0;
While (sept = FS. Read (buffer ))! =-1 ){
If (Data = NULL ){
Len = Sept;
Data = buffer;
} Else {
Byte [] temp;
Int templength;
Templength = Len + Sept;
Temp = new byte [templength];
System. arraycopy (data, 0, temp, 0, Len );
System. arraycopy (buffer, 0, temp, Len, sept );
Data = temp;
Len = templength;
}
If (Len! = Data. Length ()){
Byte temp = new byte [Len];
System. arraycopy (data, 0, temp, 0, Len );
Data = temp;
}
}
String SQL = "insert into filedata (filename, blobdata) value (?,?) ";
Preparedstatement PS = conn. preparestatement (SQL );
PS. setstring (1, F. getname ());
PS. setobject (2, data );
Ps.exe cuteupdate ();
}

Iv. General processing of clob reading
Public static string getclobstring (resultset RS, int col ){
Try {
Clob c = resultset. getclob (2 );
Reader reader = C.Getcharacterstream():
If (reader = NULL ){
Return NULL;
}
Stringbuffer sb = new stringbuffer ();
Char [] charbuf = new char [4096];
For (INT I = reader. Read (charbuf); I> 0; I = reader. Read (charbuf )){
SB. append (charbuf, 0, I );
}
Return sb. tostring ();
} Catch (exception e ){
Return "";
}
}

Of course, you can also directly write the Blob access stored procedure for JDBC calling, which is also very convenient. However, the external lob type may be used. This will be introduced later.