Write Processing in the blob field in Oracle (1)
Wei Wei
There are some particularities for the process of the Bloc field in Oracle. Now, under Java and PL / SQL, the process of the blob field and some exchanges are made.
Below, briefly introduce BLOB writing in the following two environments,
u java environment
u pl / sql environment
First, Java environment
1, blob insertion operation
1) Insert an empty BLOB.
2) Update the record of BLOB.
example:
For example, the surface structure is as follows:
Create Table Student
Name varchar2 (30),
Age Int,
Picture blob);
i. Insert an empty blob.
String command = "INSERT INTO Student VALUES (?,?,?)
Connection conn = NULL;
PreparedStatement PS = NULL;
Try
{
CONN = ... .;
PS = conn. prepareStatement (Command);
Ps.setstring (1, "zhangsan");
Ps.setint (2,20);
Ps.setblob (3, blob.empty_lob ());
ps.executeUpdate ();
...
II. Update this record
...
BYTE [] DATA = NULL;
Connection conn = NULL;
Try
{
Data = ... // Picture Information
CONN = ..
BYTEARRAYINPUTSTREAM IN = New ByteArrayInputStream (data);
UpdateBlob (In, Conn, "Student", "Picture", "Name", "ZHANGSAN");
...
...
Public Static Void UpdateBlob (InputStream Instream,
Connection CONN,
String Table,
String blobcolumn,
String KeyColumn,
String KeyValue)
Throws SQLException, IOException
{
Statement Stmt = NULL;
OracleResultSet RS = NULL;
BLOB BLOB = NULL;
Boolean Oldautocommit = Conn.getautocommit ();
Stringbuffer sqlbuffer = new stringbuffer ();
Try {
Conn.setautocommit (false);
SQLBuffer.Append ("SELECT");
SQLBuffer.Append (blobalcolumn);
SQLBuffer.Append ("from");
Sqlbuffer.Append (Table);
SQLBuffer.Append ("where");
SQLBuffer.Append (KeyColumn);
SQLBuffer.Append ("= '");
SQLBuffer.Append (keyvalue);
// Note "for Update"
SQLBuffer.Append ("'for update");
STMT = conn.createstatement ();
RS = (ORACleResultset) stmt.executequery (SQLBuffer.toString ());
IF (! rs.next ()) {
Rs.close ();
Stmt.close ();
Throw new IllegalargumentException
"NO Record Found for KeyValue: '" KeyValue "');
}
BLOB = rs.getblob (1);
OutputStream outstream = blob.getbinaryoutputstream ();
Int buffersize = blob.getchunksize ();
BYTE [] Buffer = New byte [buffersize];
INT bytesread = -1;
While ((bytesRead = instream.read)! = -1)
{
Outstream.write (buffer, 0, bytesread);
}
Instream.close ();
Outstream.close ();
Rs.close ();
Stmt.close ();
}
Catch (SQLException E)
{
Throw e;
}
Catch (IOException E)
{
Throw e;
}
Finally {
Conn.setautocommit (Oldautocommit);
}
}
Second, PL / SQL
(Example of form structure, as shown)
i. Insert an empty blob.
...
Declare
Bufferblob blob;
Data Raw (...)
...
INSERT INTO Student Values ('Zhangsan', 20, EMPTY_BLOB ());
...
II. Update the record of BLOB.
...
Select Picture Into Bufferblob from student where name = 'zhangsan' for update;
DBMS_LOB.Open (bufferblob, dbms_lob.lob_readwrite);
DBMS_LOB.WRITE (Bufferblob, UTL_RAW.LENGTH (DATA), DATA);
...
(to be continued)
