Analysis of LOB field manipulation in Oracle Database through JDBC

In Oracle, fields of the LOB (LargeObject) type are more and more 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 ). LONG, LONGRAW, AND OTHER TYPES

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 ). For LONG and LONG RAW

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 OracleLOB field 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 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 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 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 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 );
}