Java reads and writes data to the Oracle database CLOB

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.