Analysis of Reading and Writing blob Fields in oracle

LOB is divided into two types: BLOB and CLOB: BLOB is binary large object (Binary Large Object), which is suitable for storing non-text byte stream data (such as program, image, audio and video, etc.). CLOB, that is, large object of character type (Character Large Object), is related to character set and suitable for storing text-type data (such as historical archives, large works, etc.).
The following program examples illustrate several cases of manipulating LOB type fields in Oracle database through JDBC.

First, establish the following two database tables for testing. The Power Designer PD model is as follows:

The SQL statement for building the table 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 into the database

public static void clobInsert(String infile) throws Exception
{
/*  Setting No Automatic Submission  */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/*  Insert 1 Empty CLOB Object  */
stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES ('111', EMPTY_CLOB())");
/*  Query this CLOB Object and lock  */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/*  Take out this CLOB Object  */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/*  Toward CLOB Write data to the image  */
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();
}
/*  Formal submission  */
conn.commit();
} catch (Exception ex) {
/*  Error rollback  */
conn.rollback();
throw ex;
}
/*  Restore the original commit status  */
conn.setAutoCommit(defaultCommit);
}

2. Modify the CLOB object (it is an overlay modification based on the original CLOB object)

public static void clobModify(String infile) throws Exception
{
/*  Setting No Automatic Submission  */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/*  Query CLOB Object and lock  */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/*  Gets this CLOB Object  */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/*  Make overlay modifications  */
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();
}
/*  Formal submission  */
conn.commit();
} catch (Exception ex) {
/*  Error rollback  */
conn.rollback();
throw ex;
}
/*  Restore the original commit status  */
conn.setAutoCommit(defaultCommit);
} 

3. Replace the CLOB object (erase the original CLOB object and replace it with a brand-new CLOB object)

public static void clobReplace(String infile) throws Exception
{
/*  Setting No Automatic Submission  */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/*  Empty the original CLOB Object  */
stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID='111'");
/*  Query CLOB Object and lock  */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/*  Gets 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();
}
/*  Formal submission  */
conn.commit();
} catch (Exception ex) {
/*  Error rollback  */
conn.rollback();
throw ex;
}
/*  Restore the original commit status  */
conn.setAutoCommit(defaultCommit);
}

4. CLOB object reading

public static void clobRead(String outfile) throws Exception
{
/*  Setting No Automatic Submission  */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/*  Query CLOB Object  */
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='111'");
while (rs.next()) {
/*  Get 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 commit status  */
conn.setAutoCommit(defaultCommit);
}

2. Access to BLOB objects

1. Insert a new BLOB object into the database

public static void blobInsert(String infile) throws Exception
{
/*  Setting No Automatic Submission  */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/*  Insert 1 Empty BLOB Object  */
stmt.executeUpdate("INSERT INTO TEST_BLOB VALUES ('222', EMPTY_BLOB())");
/*  Query this BLOB Object and lock  */
ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE");
while (rs.next()) {
/*  Take out this BLOB Object  */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");
/*  Toward BLOB Write data to the image  */
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();
}
/*  Formal submission  */
conn.commit();
} catch (Exception ex) {
/*  Error rollback  */
conn.rollback();
throw ex;
}
/*  Restore the original commit status  */
conn.setAutoCommit(defaultCommit);
}

2. Modify the BLOB object (it is an overlay modification based on the original BLOB object)

public static void blobModify(String infile) throws Exception
{
/*  Setting No Automatic Submission  */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/*  Query BLOB Object and lock  */
ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE");
while (rs.next()) {
/*  Take out this BLOB Object  */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");
/*  Toward BLOB Write data to the image  */
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();
}
/*  Formal submission  */
conn.commit();
} catch (Exception ex) {
/*  Error rollback  */
conn.rollback();
throw ex;
}
/*  Restore the original commit status  */
conn.setAutoCommit(defaultCommit);
}

3. Replace the BLOB object (clear the original BLOB object and replace it with a brand-new BLOB object)

public static void blobReplace(String infile) throws Exception
{
/*  Setting No Automatic Submission  */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/*  Empty the original BLOB Object  */
stmt.executeUpdate("UPDATE TEST_BLOB SET BLOBCOL=EMPTY_BLOB() WHERE ID='222'");
/*  Query this BLOB Object and lock  */
ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE");
while (rs.next()) {
/*  Take out this BLOB Object  */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");
/*  Toward BLOB Write data to the image  */
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();
}
/*  Formal submission  */
conn.commit();
} catch (Exception ex) {
/*  Error rollback  */
conn.rollback();
throw ex;
}
/*  Restore the original commit status  */
conn.setAutoCommit(defaultCommit);
}

4. BLOB Object Reading

public static void blobRead(String outfile) throws Exception
{
/*  Setting No Automatic Submission  */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/*  Query BLOB Object  */
ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222'");
while (rs.next()) {
/*  Take out this BLOB Object  */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");
/*  With 2 Output in binary form  */
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();
}
/*  Formal submission  */
conn.commit();
} catch (Exception ex) {
/*  Error rollback  */
conn.rollback();
throw ex;
}
/*  Restore the original commit status  */
conn.setAutoCommit(defaultCommit);
}

Looking at the access to LOB type fields by the above program, we can see that compared with other types of fields, there are the following significant differences:

1 is that automatic submission must be cancelled.