ORACLE Operations CLOB Fields

method One:

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

{

Boolean defaultcommit = Conn.getautocommit ();

Conn.setautocommit (FALSE);

try {

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

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

while (Rs.next ()) {

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

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

}

Conn.commit ();

catch (Exception ex) {

Conn.rollback ();

Throw ex;

}

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

{

Boolean defaultcommit = Conn.getautocommit ();

Conn.setautocommit (FALSE);

try {

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

while (Rs.next ()) {

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

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

}

Conn.commit ();

catch (Exception ex) {

Conn.rollback ();

Throw ex;

}

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

{

Boolean defaultcommit = Conn.getautocommit ();

Conn.setautocommit (FALSE);

try {

Stmt.executeupdate ("UPDATE test_clob SET clobcol=empty_clob () WHERE id= ' 111");

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

while (Rs.next ()) {

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

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

}

Conn.commit ();

catch (Exception ex) {

Conn.rollback ();

Throw ex;

}

Conn.setautocommit (Defaultcommit);

}

Method Two:

In Oracle, the maximum number of bytes supported by VARCHAR2 is 4KB, so for some long string processing, we need to use CLOB type fields, CLOB field maximum support 4GB.
There are several other types:
BLOB: binary, if exe,zip
CLOB: Single-byte code, such as a general text file.
Nlob: Multi-Byte code, such as a file in UTF format.
Here's how to work with the clog field, which is used in the Help document section of our project.
1, first is written
View plain Copy to clipboard print? /*  CLOB type  */  //  the Hcontent field in the following table pf_help_content the Help id    Map is generated by the serializer  map = query.getmap ("Select to_char (seq_hid.nextval)  HID FROM DUAL ");     hid = string.valueof (Map.get ("hid"));   //Insert a piece of data, note clob field, You need to first insert an empty CLOB type  empty_clob (), and then update the CLOB field separately     sql =  "insert into pf_ Help_content (hid,hcontent)  VALUES  (?, Empty_clob ())    ";    try   {                    // Execute Insert          rtn = dbutils.executeupdate (sql,hid);              /*  After successful insert, modify hcontent field contents  */         //Get database connection         &NBsp;                          connection conn = dbutils.getconnection ();          //Manual Submission           Conn.setautocommit (false);         //definition resultset  and  Clob  Variables          ResultSet rs = null;          oracle.sql.CLOB clob = null;          //update sql         String sqlclob =  "select  hcontent from pf_help_content where hid=? for update  ";         java.sql.preparedstatement pstmt = conn.preparestatement (Sqlclob) ;      &Nbsp;  //hid is VARCHAR2 type, so use setstring         pstmt.setstring (1,hid);         //Execute UPDATE statement           rs= pstmt.executequery ();         if (Rs.next ())           {            // Get the content of the hcontent just now, which is just added Empty_clob ()             clob  =  (Oracle.sql.CLOB) Rs.getclob (1);         }          //need Clob.getcharacteroutputstream () flow mode output           writer write = clob.getcharacteroutputstream ();          //Write specific content, helpform.gethcontent ()   saved is Help content           write.write (HeLpform.gethcontent ());         write.flush ();          write.close ();         rs.close ();          //submission          conn.commit ();          conn.close ();   }    catch (Exception  ex)     {        //.........   }   
2, modify CLOB field content
View plain Copy to clipboard print? /*  modifications are basically consistent with inserts, and are also implemented with for update  */  //If the field content before the modification is longer than the current modified length, part of the end will still exist     /So before you modify the content, you need to empty the pf_help_content content     sql =  " update pf_help_content  set hcontent=empty_clob ()  Where HID=?  ";    try   {            rtn = dbutils.executeupdate (Sql,hid);      //The following actions are the same as when they were added                                       connection conn = dbutils.getconnection ();      Conn.setautocommit (false);     ResultSet rs = null;      oracle.sql.clob clob = null;     string sqlclob =  "select hcontent from pf_help_content where hid=? for update " ;     java.sql.preparedstatement pstmt = conn.preparestatement (SQLCLOB);      pstmt.setstring (1,hid);     rs= pstmt.executequery ();      if (Rs.next ())      {        clob =   (Oracle.sql.CLOB) Rs.getclob (1);     }     writer write =  clob.getcharacteroutputstream ();     write.write (Helpform.gethcontent ());      write.flush ();     write.close ();     rs.close ();      conn.commit ();     conn.close ();                                     }    catch (Exception ex)      {      //...   }  

3, remove the Clob field text content
View plain Copy to clipboard print? /*  the front part is consistent  */   connection conn = dbutils.getconnection ();    Conn.setautocommit (false);    resultset rs = null;    oracle.sql.clob clob = null;    string sqlclob =  "Select  hcontent from pf_help_content where hid=?  ";    Java.sql.preparedstatement pstmt = conn.preparestatement (Sqlclob);    Pstmt.setstring (1,hid);    rs= pstmt.executequery ();    if (Rs.next ())   In    {        //rs.getclob (1), parameter 1 refers to the Hcontent field index, with the first field starting at 1 instead of 0.         //can also use the field name to fetch Rs.getclob ("Hcontent")          clob =  (Oracle.sql.CLOB) Rs.getclob (1);   }    if (clob==null  | |  clob.lEngth () ==0)     {        hcontent =  "";    }else   {        //clob field content is string          hcontent=clob.getsubstring ((Long) 1, (int) clob.length ());   }    Rs.close ();    conn.close ();    request.setattribute ("HCONTENT", HCONTENT);