Analysis of reading and writing blob fields in oracle

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 Large Object, is related to Character sets and is suitable for storing text-type data (such as historical archives and big topic 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

Copy codeThe Code is as follows:
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 image */
Oracle. SQL. CLOB clob = (oracle. SQL. CLOB) rs. getClob ("CLOBCOL ");
/* Write data to CLOB objects */
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 image (it is a overwriting modification based on the original CLOB image)

Copy codeThe Code is as follows:
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 ()){
/* Obtain the CLOB image */
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)

Copy codeThe Code is as follows:
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 ()){
/* Obtain the CLOB image */
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

Copy codeThe Code is as follows:
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 CLOB objects */
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 );
}

2. BLOB Object Access

1. Insert a new BLOB Object to the database.

Copy codeThe Code is as follows:
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 the BLOB Object */
Oracle. SQL. BLOB blob = (oracle. SQL. BLOB) rs. getBlob ("BLOBCOL ");
/* Write data to BLOB objects */
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 (the original BLOB Object is overwritten)

Copy codeThe Code is as follows:
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 the BLOB Object */
Oracle. SQL. BLOB blob = (oracle. SQL. BLOB) rs. getBlob ("BLOBCOL ");
/* Write data to BLOB objects */
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 the BLOB Object (clear the original BLOB Object and replace it with a brand new BLOB Object)

Copy codeThe Code is as follows:
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 the BLOB Object */
Oracle. SQL. BLOB blob = (oracle. SQL. BLOB) rs. getBlob ("BLOBCOL ");
/* Write data to BLOB objects */
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

Copy codeThe Code is as follows:
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 the 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.