Analysis of several scenarios for manipulating Oracle database LOB fields through JDBC

oracle| Data | Several situations of database manipulating Oracle database LOB fields through JDBC Software production Center rain also odd 2003-6-10 15:14:19
In Oracle, fields for LOB (Large object, large objects) types are now used more and more. Because of this type of field, large capacity (up to 4GB of data), and a table can have more than one of these types of fields, is flexible, applicable to a very large number of data business areas (such as images, files, etc.). Fields of type long, long, and so on, although the storage capacity is not small (up to 2GB), are now rarely used because there can be only one type of field in a table.




LOB types are divided into BLOBs and Clob two: BLOBs, which are binary large objects (Binary Large object), are suitable for storing text stream data (such as programs, images, audio, video, etc.) that are not literal. CLOB, a character-type large object (Character Large object), is associated with a character set that is suitable for storing text-type data (such as historical archives, voluminous writings, etc.).



The following is an example of a program that manipulates Oracle database LOB type fields through JDBC.



Set up the following two test database tables, the Power Designer PD model is as follows:








The Build table SQL statement is:

CREATE TABLE Test_clob (ID number (3), Clobcol CLOB)

CREATE TABLE Test_blob (ID number (3), Blobcol BLOB)



I. Access to CLOB objects



1. Insert a new CLOB object into the database



public static void Clobinsert (String infile) throws Exception

{

/* Set does not automatically submit * *

Boolean defaultcommit = Conn.getautocommit ();

Conn.setautocommit (FALSE);



try {

/* Insert an empty CLOB object * *

Stmt.executeupdate ("INSERT into Test_clob VALUES ('", Empty_clob ()) ");

* * Query this CLOB object and lock/*

ResultSet rs = stmt.executequery ("Select Clobcol from Test_clob WHERE id= ' for UPDATE");

while (Rs.next ()) {

/* Remove this 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 submitted * * *

Conn.commit ();

catch (Exception ex) {

/* ERROR Roll back/*

Conn.rollback ();

Throw ex;

}



/* Restore original Submission Status * * *

Conn.setautocommit (Defaultcommit);

}



2, modify the Clob object (is the original Clob object based on the coverage of the modification)



public static void Clobmodify (String infile) throws Exception

{

/* Set does not automatically submit * *

Boolean defaultcommit = Conn.getautocommit ();

Conn.setautocommit (FALSE);



try {

* * Query Clob object and lock/*

ResultSet rs = stmt.executequery ("Select Clobcol from Test_clob WHERE id= ' for UPDATE");

while (Rs.next ()) {

/* Get this CLOB object * *

Oracle.sql.CLOB CLOB = (Oracle.sql.CLOB) rs.getclob ("Clobcol");

/* Make Overlay 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 submitted * * *

Conn.commit ();

catch (Exception ex) {

/* ERROR Roll back/*

Conn.rollback ();

Throw ex;

}



/* Restore original Submission Status * * *

Conn.setautocommit (Defaultcommit);

}



3, replace the Clob object (the original Clob object cleared, replaced by a new Clob object)



public static void Clobreplace (String infile) throws Exception

{

/* Set does not automatically submit * *

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= ' 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 submitted * * *

Conn.commit ();

catch (Exception ex) {

/* ERROR Roll back/*

Conn.rollback ();

Throw ex;

}



/* Restore original Submission Status * * *

Conn.setautocommit (Defaultcommit);

}



4, Clob object reading



public static void Clobread (String outfile) throws Exception

{

/* Set does not automatically submit * *

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 original Submission Status * * *

Conn.setautocommit (Defaultcommit);

}



Second, the access of BLOB objects



1. Insert a new BLOB object into the database



public static void Blobinsert (String infile) throws Exception

{

/* Set does not automatically submit * *

Boolean defaultcommit = Conn.getautocommit ();

Conn.setautocommit (FALSE);



try {

/* Insert an 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= ' for UPDATE");

while (Rs.next ()) {

/* Remove this BLOB object/*

Oracle.sql.BLOB BLOB = (Oracle.sql.BLOB) rs.getblob ("Blobcol");

/* Write data to a 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 submitted * * *

Conn.commit ();

catch (Exception ex) {

/* ERROR Roll back/*

Conn.rollback ();

Throw ex;

}



/* Restore original Submission Status * * *

Conn.setautocommit (Defaultcommit);

}



2, modify the Blob object (is the original Blob object based on the overwrite modification)



public static void Blobmodify (String infile) throws Exception

{

/* Set does not automatically submit * *

Boolean defaultcommit = Conn.getautocommit ();

Conn.setautocommit (FALSE);



try {

* * Query Blob object and lock/*

ResultSet rs = stmt.executequery ("Select Blobcol from Test_blob WHERE id= ' for UPDATE");

while (Rs.next ()) {

/* Remove this BLOB object/*

Oracle.sql.BLOB BLOB = (Oracle.sql.BLOB) rs.getblob ("Blobcol");

/* Write data to a 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 submitted * * *

Conn.commit ();

catch (Exception ex) {

/* ERROR Roll back/*

Conn.rollback ();

Throw ex;

}



/* Restore original Submission Status * * *

Conn.setautocommit (Defaultcommit);

}



3. Replace the Blob object (erase the original BLOB object and replace it with a new BLOB object)



public static void Blobreplace (String infile) throws Exception

{

/* Set does not automatically submit * *

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= ' for UPDATE");

while (Rs.next ()) {

/* Remove this BLOB object/*

Oracle.sql.BLOB BLOB = (Oracle.sql.BLOB) rs.getblob ("Blobcol");

/* Write data to a 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 submitted * * *

Conn.commit ();

catch (Exception ex) {

/* ERROR Roll back/*

Conn.rollback ();

Throw ex;

}



/* Restore original Submission Status * * *

Conn.setautocommit (Defaultcommit);

}



4. Blob Object Reading



public static void Blobread (String outfile) throws Exception

{

/* Set does not automatically submit * *

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 ()) {

/* Remove this BLOB object/*

Oracle.sql.BLOB BLOB = (Oracle.sql.BLOB) rs.getblob ("Blobcol");

/* 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 ();

}

* * Formally submitted * * *

Conn.commit ();

catch (Exception ex) {

/* ERROR Roll back/*

Conn.rollback ();

Throw ex;

}



/* Restore original Submission Status * * *

Conn.setautocommit (Defaultcommit);

}



Looking at the access of the above program to LOB type fields, we can see that there are a few notable differences compared to other types of fields:



The first is that automatic submission must be canceled.



You must cancel Autocommit with Setautocommit (false) before the access operation begins. Other type fields do not have this special requirement. This is because accessing LOB type fields usually takes many actions to complete. If not, Oracle throws a "read violation order" error.



The other is the way of inserting.



LOB data cannot be inserted directly (insert) Like other types of data. You must insert an empty LOB object before inserting it, the empty object of type CLOB is Empty_clob (), and the empty object of the BLOB type is Empty_blob (). The Select command then queries the previously inserted record and locks it, and then modifies the empty object to the LOB object that you want to insert.



Third, different ways to modify.



When other types of fields are modified, use the update ... SET ... command. The LOB Type field, however, can be used only with select ... The FOR UPDATE command queries the record and locks it before it can be modified. And the modification also has two kinds of changes: first, the original data based on the modification (that is, overlay modification), the implementation of SELECT ... For update after the data, the second is to replace (first the original data clear, and then modify), first execute the update command to set the value of the LOB field to empty LOB objects, and then proceed to the first method of modification. It is recommended that you use a replacement method to achieve the same effect as after other field update operations.



Four is the LOB operation class provided by the database JDBC driver should be used when accessing.



For Oracle databases, you should use Oracle.sql.CLOB and Oracle.sql.BLOB. When you do not use LOB classes provided by the database JDBC driver, the program runs with an "abstract method call" error. This is because JDBC defines the Java.sql.Clob and Java.sql.Blob interfaces, some of which are not actually implemented in the driver provided by the database manufacturer.



The access means is similar to the file operation.



For a BLOB type, apply the Inputstream/outputstream class, which does not encode conversions, byte-per-access. The Oracle.sql.BLOB class provides the Getbinarystream () and Getbinaryoutputstream () two methods, which are used to read the Oracle BLOB field. The latter method is used to write data to an Oracle BLOB field.



For the CLOB type, the Reader/writer class is applied, and this class is encoded for conversion. The Oracle.sql.CLOB class provides the Getcharacterstream () and Getcharacteroutputstream () two methods that are used to read the CLOB fields of Oracle. The latter method is used to write data to the Oracle CLOB field.



It should be explained that in order to greatly improve the efficiency of program execution, read and write operations on the Blob/clob field should use the Buffer action class (with buffered prefix), namely: Bufferedinputstream,bufferedoutputstream, Bufferedreader,bufferedwriter. The buffer action class is used in all routines.





Summary: Manipulating the LOB fields of an Oracle database via JDBC is not difficult to grasp, but to insert, modify, replace, and read four of ways. In the actual operation to pay attention to the above points, combined with the reading example routines program, users will soon understand the use of LOB type fields, will also be aware of the beauty of this type of field! source File Download >>
(Web page edit: Programming Prodigal Son)