Analysis of Lob field manipulation in Oracle Database through JDBC

In Oracle, fields of the lob (large object, large object) type are increasingly used. This type of field has a large capacity (up to 4 GB of data), and a table can have multiple fields of this type, which is flexible, applicable to business areas with a large data volume (e.g., archives, etc ). However, although the storage capacity of fields such as long and long raw is not small (up to 2 GB), a table can have only one field of this type, it is rarely used now.
The lob type can be blob or clob: blob is a binary large object. It is suitable for storing non-text byte stream data (such as programs, images, audio and video ). Clob, a character-Type Large Object (character large object), is related to character sets and is suitable for storing text-type data (such as historical files and big part-head books ).
The following example describes how to manipulate the lob type fields of the Oracle database through JDBC.
First, create the following two database tables for testing. The Power Designer PD model is as follows:
The SQL statement for table creation is:
Create Table test_clob (ID number (3), clobcol clob)
Create Table test_blob (ID number (3), blobcol BLOB)
1. Access to clob objects
1. Insert a new clob object to the database.
Public static void clobinsert (string infile) throws exception
{
/* Set not to submit automatically */
Boolean defacomcommit = conn. getautocommit ();
Conn. setautocommit (false );
Try {
/* Insert an empty clob object */
Stmt.exe cuteupdate ("insert into test_clob values ('000000', empty_clob ())");
/* Query this clob object and lock it */
Resultset rs = stmt.exe cutequery ("select clobcol from test_clob where id = '000000' for update ");
While (Rs. Next ()){
/* Retrieve the clob object */
Oracle. SQL. clob = (Oracle. SQL. clob) Rs. getclob ("clobcol ");
/* Write data to the clob object */
Bufferedwriter out = new bufferedwriter (clob. getcharacteroutputstream ());
Bufferedreader in = new bufferedreader (New filereader (infile ));
Int C;
While (C = in. Read ())! =-1 ){
Out. Write (C );
}
In. Close ();
Out. Close ();
}
/* Formally submit */
Conn. Commit ();
} Catch (exception ex ){
/* Error rollback */
Conn. rollback ();
Throw ex;
}
/* Restore the original submission status */
Conn. setautocommit (defaultcommit );
}
2. Modify the clob object (overwrite modification based on the original clob object)
Public static void clobmodify (string infile) throws exception
{
/* Set not to submit automatically */
Boolean defacomcommit = conn. getautocommit ();
Conn. setautocommit (false );
Try {
/* Query the clob object and lock it */
Resultset rs = stmt.exe cutequery ("select clobcol from test_clob where id = '000000' for update ");
While (Rs. Next ()){
/* Get This clob object */
Oracle. SQL. clob = (Oracle. SQL. clob) Rs. getclob ("clobcol ");
/* Perform overwrite modification */
Bufferedwriter out = new bufferedwriter (clob. getcharacteroutputstream ());
Bufferedreader in = new bufferedreader (New filereader (infile ));
Int C;
While (C = in. Read ())! =-1 ){
Out. Write (C );
}
In. Close ();
Out. Close ();
}
/* Formally submit */
Conn. Commit ();
} Catch (exception ex ){
/* Error rollback */
Conn. rollback ();
Throw ex;
}
/* Restore the original submission status */
Conn. setautocommit (defaultcommit );
}

3. Replace the clob object (clear the original clob object and replace it with a brand new clob object)
Public static void clobreplace (string infile) throws exception
{
/* Set not to submit automatically */
Boolean defacomcommit = conn. getautocommit ();
Conn. setautocommit (false );
Try {
/* Clear the original clob object */
Stmt.exe cuteupdate ("Update test_clob set clobcol = empty_clob () Where id = '000000 '");
/* Query the clob object and lock it */
Resultset rs = stmt.exe cutequery ("select clobcol from test_clob where id = '000000' for update ");
While (Rs. Next ()){
/* Get This clob object */
Oracle. SQL. clob = (Oracle. SQL. clob) Rs. getclob ("clobcol ");
/* Update data */
Bufferedwriter out = new bufferedwriter (clob. getcharacteroutputstream ());
Bufferedreader in = new bufferedreader (New filereader (infile ));
Int C;
While (C = in. Read ())! =-1 ){
Out. Write (C );
}
In. Close ();
Out. Close ();
}
/* Formally submit */
Conn. Commit ();
} Catch (exception ex ){
/* Error rollback */
Conn. rollback ();
Throw ex;
}
/* Restore the original submission status */
Conn. setautocommit (defaultcommit );
}
4. Read clob objects
Public static void clobread (string OUTFILE) throws exception
{
/* Set not to submit automatically */
Boolean defacomcommit = conn. getautocommit ();
Conn. setautocommit (false );
Try {
/* Query clob objects */
Resultset rs = stmt.exe cutequery ("select * From test_clob where id = '000000 '");
While (Rs. Next ()){
/* Get the clob object */
Oracle. SQL. clob = (Oracle. SQL. clob) Rs. getclob ("clobcol ");
/* Output in character form */
Bufferedreader in = new bufferedreader (clob. getcharacterstream ());
Bufferedwriter out = new bufferedwriter (New filewriter (OUTFILE ));
Int C;
While (C = in. Read ())! =-1 ){
Out. Write (C );
}
Out. Close ();
In. Close ();
}
} Catch (exception ex ){
Conn. rollback ();
Throw ex;
}
/* Restore the original submission status */
Conn. setautocommit (defaultcommit );
}

2. BLOB Object Access
1. Insert a new BLOB Object to the database.
Public static void blobinsert (string infile) throws exception
{
/* Set not to submit automatically */
Boolean defacomcommit = conn. getautocommit ();
Conn. setautocommit (false );
Try {
/* Insert an empty BLOB Object */
Stmt.exe cuteupdate ("insert into test_blob values ('2013', empty_blob ())");
/* Query this BLOB Object and lock it */
Resultset rs = stmt.exe cutequery ("select blobcol from test_blob where id = '000000' for update ");
While (Rs. Next ()){
/* Retrieve this BLOB Object */
Oracle. SQL. Blob blob = (Oracle. SQL. Blob) Rs. getblob ("blobcol ");
/* Write data to the BLOB Object */
Bufferedoutputstream out = new bufferedoutputstream (BLOB. getbinaryoutputstream ());
Bufferedinputstream in = new bufferedinputstream (New fileinputstream (infile ));
Int C;
While (C = in. Read ())! =-1 ){
Out. Write (C );
}
In. Close ();
Out. Close ();
}
/* Formally submit */
Conn. Commit ();
} Catch (exception ex ){
/* Error rollback */
Conn. rollback ();
Throw ex;
}
/* Restore the original submission status */
Conn. setautocommit (defaultcommit );
}

2. Modify the BLOB Object (overwrite the original BLOB Object)
Public static void blobmodify (string infile) throws exception
{
/* Set not to submit automatically */
Boolean defacomcommit = conn. getautocommit ();
Conn. setautocommit (false );
Try {
/* Query blob objects and lock them */
Resultset rs = stmt.exe cutequery ("select blobcol from test_blob where id = '000000' for update ");
While (Rs. Next ()){
/* Retrieve this BLOB Object */
Oracle. SQL. Blob blob = (Oracle. SQL. Blob) Rs. getblob ("blobcol ");
/* Write data to the BLOB Object */
Bufferedoutputstream out = new bufferedoutputstream (BLOB. getbinaryoutputstream ());
Bufferedinputstream in = new bufferedinputstream (New fileinputstream (infile ));
Int C;
While (C = in. Read ())! =-1 ){
Out. Write (C );
}
In. Close ();
Out. Close ();
}
/* Formally submit */
Conn. Commit ();
} Catch (exception ex ){
/* Error rollback */
Conn. rollback ();
Throw ex;
}
/* Restore the original submission status */
Conn. setautocommit (defaultcommit );
}
3. Replace BLOB Object (clear the original BLOB Object and replace it with a brand new BLOB Object)
Public static void blobreplace (string infile) throws exception
{
/* Set not to submit automatically */
Boolean defacomcommit = conn. getautocommit ();
Conn. setautocommit (false );
Try {
/* Clear the original BLOB Object */
Stmt.exe cuteupdate ("Update test_blob set blobcol = empty_blob () Where id = '20140901 '");
/* Query this BLOB Object and lock it */
Resultset rs = stmt.exe cutequery ("select blobcol from test_blob where id = '000000' for update ");
While (Rs. Next ()){
/* Retrieve this BLOB Object */
Oracle. SQL. Blob blob = (Oracle. SQL. Blob) Rs. getblob ("blobcol ");
/* Write data to the BLOB Object */
Bufferedoutputstream out = new bufferedoutputstream (BLOB. getbinaryoutputstream ());
Bufferedinputstream in = new bufferedinputstream (New fileinputstream (infile ));
Int C;
While (C = in. Read ())! =-1 ){
Out. Write (C );
}
In. Close ();
Out. Close ();
}
/* Formally submit */
Conn. Commit ();
} Catch (exception ex ){
/* Error rollback */
Conn. rollback ();
Throw ex;
}
/* Restore the original submission status */
Conn. setautocommit (defaultcommit );
}
4. Reading blob objects
Public static void blobread (string OUTFILE) throws exception
{
/* Set not to submit automatically */
Boolean defacomcommit = conn. getautocommit ();
Conn. setautocommit (false );
Try {
/* Query blob objects */
Resultset rs = stmt.exe cutequery ("select blobcol from test_blob where id = '000000 '");
While (Rs. Next ()){
/* Retrieve this BLOB Object */
Oracle. SQL. Blob blob = (Oracle. SQL. Blob) Rs. getblob ("blobcol ");
/* Output in binary format */
Bufferedoutputstream out = new bufferedoutputstream (New fileoutputstream (OUTFILE ));
Bufferedinputstream in = new bufferedinputstream (BLOB. getbinarystream ());
Int C;
While (C = in. Read ())! =-1 ){
Out. Write (C );
}
In. Close ();
Out. Close ();
}
/* Formally submit */
Conn. Commit ();
} Catch (exception ex ){
/* Error rollback */
Conn. rollback ();
Throw ex;
}
/* Restore the original submission status */
Conn. setautocommit (defaultcommit );
}

Observe the program's access to the lob field. We can see that compared with other types of fields, there are several notable differences:

First, automatic submission must be canceled.
Before the access operation starts, you must use setautocommit (false) to cancel Automatic submission. Other types of fields do not have this special requirement. This is because multiple operations are required to access a lob field. Otherwise, Oracle will throw a "read violation order" error.

Second, the insertion method is different.
Lob data cannot be inserted directly like other data types ). An empty lob object must be inserted before insertion. The empty clob type object is empty_clob (), and the empty blob type object is empty_blob (). Then, you can use the SELECT command to query the previously inserted records and lock them. Then, you can change the empty object to the lob object to be inserted.

Third, the modification methods are different.
Update... Set... Command. For a field of the lob type, only select... The for update command is used to query and lock records before modification. There are also two ways to modify: one is to modify (overwrite) the original data and execute select... For update and then change the data. The second is to replace (first clear the original data and then modify it). First, run the update command to set the value of the lob field to an empty lob object, then perform the first method. We recommend that you use the replacement method to achieve the same effect as other field update operations.

Fourth, the lob operation class provided by the database JDBC driver should be used for access.
Oracle. SQL. clob and Oracle. SQL. Blob should be used for Oracle databases. When the lob class provided by the database JDBC driver is not used, the "abstract method call" error is easy to occur when the program is running. This is because the java. SQL. clob and Java. SQL. blob interface, some of which are not actually implemented in the driver provided by the database manufacturer.

Fifth, access methods are similar to file operations.
Applies the inputstream/outputstream class to blob type. This class does not undergo encoding conversion and is accessed in bytes one by one. Oracle. SQL. the Blob class provides the getbinarystream () and getbinaryoutputstream () methods. The first method is used to read the Blob field of Oracle, and the second method is used to write data into the Blob field of oracle.
For the clob type, the reader/writer class is used for encoding conversion. Oracle. SQL. the clob class provides the getcharacterstream () and getcharacteroutputstream () methods. The first method is used to read the clob field of Oracle, and the second method is used to write data into the clob field of oracle.
To greatly improve program execution efficiency, you should use the buffer operation class (with the buffered prefix) for reading and writing blob/clob fields, that is, bufferedinputstream, bufferedoutputstream, bufferedreader, bufferedwriter. All the routines use the buffer operation class.

Summary: Using JDBC to manipulate the lob field of an Oracle database is not difficult to grasp, except to insert, modify, replace, and read data. In practice, pay attention to the points mentioned above. In combination with the reading routine source code, the user will soon understand the use of the lob type field, and will surely understand the beauty of this type field!