Java reads and writes data to the Oracle database CLOB

Write Data

1. Create an empty Empty_clob () object to the Oracle CLOB field.

Insert into Ptr_assetxml (cchr_assetplanid,cchr_assetdata,create_date,submitter,last_modified_by,modified_date, status,short_description,request_id) "+" values (?, Empty_clob (),?,?,?,?,?,?, (select NextID from Arschema where Schemai d=3555));

2. Query this record and lock the record other sessions cannot manipulate this record

Select Cchr_assetdata from Ptr_assetxml where cchr_assetplanid=? For update

3. Get the object of the Clob field and establish the output flow to the object to write the data

Conn.setautocommit (FALSE);    rs = Pre4.executequery ();    Rs.next ();    CLOB = (CLOB) rs.getclob (1);    BW = new BufferedWriter (Clob.getcharacteroutputstream ());    Bw.write (Str.tostring ());    if (BW! = NULL) {bw.close (); } conn.commit ();

Reading data

1. Query out this field and get the object for this CLOB field and establish the input stream

1.  try {   pre1 = daodb.pre ( Constanttool.sqlsearch_3555_blob)    pre1.setstring (1,  "PD20140924154");    Rs = pre1.executequery ();   clob c;   string str;    while  (Rs.next ())  {    c =  (Oracle.sql.CLOB)   Rs.getclob (1);     bf = new bufferedreader (C.getcharacterstream ());     while  ((Str = bf.readline ())  != null)  {      system.out.println (str);    }   }  } catch  (sqlexception e)  {   // TODO Auto-generated catch block    e.printstacktrace ();  } catch  (ioexception e)  {    E.printstacktrace ();   } 

2. Update the data this CLOB field should be updated to null and then establish the connection input data, if the direct update data will be overwritten from the value one by one at the beginning of the Clob field.

Note that the Clob object is Oracle.sql.CLOB;

One way to actually apply a write Clob field value

Public void createxmlfile (Stringbuilder str, string name)  {  file  = new file (constanttool.filepath +  "/"  + name +  "Ws.xml");   logger.info ("Start writing data to Path ..."  + file.getabsolutepath ());  try {   fos = new  FileOutputStream (file);    osw = new outputstreamwriter (fos,  "UTF-8");    osw.write (Str.tostring ());    osw.close ();  } catch  ( Filenotfoundexception e)  {   logger.info (e);    e.printstacktrace ();   } catch  (ioexception e)  {   logger.info (e);    E.printstacktrace ();  }  //  Database----------------------------------------------------------  daodb db = new  daodb ();   connection conn = db.conn ();   preparedstatement pre1;  preparedstatement pre2;  preparedstatement  pre3;  preparedstatement pre4;  preparedstatement pre5;  clob  clob = null;  ResultSet rs = null;  BufferedWriter bw;   int i = 0;  pre1 = db.pre (Constanttool.sqlsearch_3555_update_ key);   pre2 = db.pre (constanttool.sqlinsertinto_3555);   pre3 = db.pre ( constanttool.sqlupdateid_3555);   pre4 = db.pre (Constanttool.sqlselect_3555_blob);   pre5 = db.pre (Constanttool.sqlsearch_3555_update_clob_empty);  // public  static final string sqlinsertinto_3555 =  "insert "   // +   //  " into ptr_assetxml (cchr_assetplanid,cchr_assetdata,create_date,submitter,last_ Modified_by,modified_date,status,short_description,request_id)   "  // +  // " values (?, Empty_clob (),?,?,?, ?,?,?, (select nextid from arschema where schemaid=3555)) ";  string[]  Planstr = name.split ("_");   try {   conn.setautocommit (false);    pre1.setlong (1, new date (). GetTime ()  / 1000);    pre1.setstring ( 2, planstr[0]);    i = pre1.executeupdate ();    conn.commit ();    if  (i == 0)  {    pre3.executeupdate ();     conn.commit ();     pre2.setstring (1, planstr[0]);     Pre2.setlong (2,  (New date () getTime ()  / 1000);     pre2.setstring (3,   "Sync");     pre2.setstring (4,  "Sync");     pre2.setlong (5,  (New date (). GettimE ()  / 1000));     pre2.setint (6, 1);     pre2.setstring (7 ,  "Sync");     pre2.executeupdate ();     conn.commit ();     pre4.setstring (1, planstr[0]);     conn.setautocommit (false);     rs = pre4.executequery ();     rs.next ();     clob  =  (CLOB)  rs.getclob (1);     bw = new bufferedwriter ( Clob.getcharacteroutputstream ());     bw.write (str.tostring ());     if   (Bw != null)  {     bw.close ();    }     conn.commit ();   }   if  (i != 0)  {     pre5.setstring (1, planstr[0]);     pre5.executeupdate ();     conn.commit (); &nbSp;   pre4.setstring (1, planstr[0]);     conn.setautocommit (false);     rs = pre4.executequery ();     rs.next ();     clob =  (CLOB)  rs.getclob (1);    bw = new  BufferedWriter (Clob.getcharacteroutputstream ());     bw.write (Str.toString ());     if  (Bw != null)  {     bw.close ();     }    conn.commit ();    }      if (pre1!= NULL) {    pre1.close ();    }   if (Pre2!=null) {     pre1.close ();    }   if (Pre3!=null) {     Pre1.close ();    }   if (pre4!=null) {    pre1.close ();    }   if (pre5!=null) {    pre1.close ();    }   db.closeconn ();  }  catch  (sqlexception e)  {   e.printstacktrace ();  } catch  ( ioexception e)  {   e.printstacktrace ();   } }}

The actual application of Read Clob value a method

Public string getplaninfobyplanid (String planid)  {  preparedstatement pre1  = daodb.pre (Constanttool.sqlsearch_3555_blob);  resultset rs;   Bufferedreader bf;  stringbuilder sb = new stringbuilder ();   CLOB  c;  string str;  try {   pre1.setstring (1,&NBSP;PLANID);    rs = pre1.executequery ();   while  (Rs.next ())  {     c =  (CLOB)  rs.getclob (1);    bf = new  BufferedReader (C.getcharacterstream ());    while  ((Str = bf.readline ())  != null)  {     sb.append (str);    }    }  } catch  (sqlexception e)  {   logger.info (e);    e.printstacktrace ();   } catch  (ioexception e)  {   logger.info (e);    E.printstacktrace ();   }  return sb.tostring (). ReplaceAll ("<",  "\\$\\$"). ReplaceAll (">",  "# #");  }

Java reads and writes data to the Oracle database CLOB