How to handle CLOB field methods in Oracle database _oracle

In the establishment of the knowledge base, the use of ordinary VARCHAR2 to store the article is obviously not enough, only 4000 of the byte, can not put many words,
and CLOB data types, you can store up to 8G of data. But this field has a lot of specificity to deal with, record.
Insert:
Writing directly in the SQL is not possible, one SQL script has a character limit, and the article content contains a number of special characters, such as wrapping, quotes,
Things like that, very troublesome. The popular practice on the Internet is to insert an empty CLOB field first and use the Empty_clob () method to create an empty field, such as:

Copy Code code as follows:

INSERT into T_topic (topic_id,topic_content) VALUES (' 0000001 ', Empty_clob ());

Then use the Select Topic_content from T_topic WHERE topic_id= ' 0000001 ' for update's query statement,
To construct an updated statement that updates the Clob field after the resultset is fetched.

Copy Code code as follows:

ResultSet rs = Pstm.executequery ();
if (Rs.next ()) {
Oracle.sql.CLOB lob = (CLOB) rs.getclob (1);
try {
Writer OS = Lob.getcharacteroutputstream ();
Os.write (Dr.getfield ("Fld_content"). asstring ());
Os.flush ();
catch (IOException e) {
E.printstacktrace ();
}
}

This insert and update operation is to be placed in a transaction, that is, to set Setautocommit (false) after getting to connection;
Update:
Update is also the use of SEELCT ... For Update method
You also set up transactions

Read:

Copy Code code as follows:

CLOB CLOB = (CLOB) rs.getclob ("Fld_content");
Reader reader = Clob.getcharacterstream ();
StringBuffer sb=new StringBuffer ();
char[] cb = new char[1024];
try {
for (int len = Reader.read (CB); len>0;len= Reader.read (CB)) {
Sb.append (Cb,0,len);
}
catch (IOException e) {
throw new SQLException ("read article content failed.", e);
}

The particularity of the query:
A datasheet with a CLOB field that cannot be filtered using the DISTINCT keyword in an SQL statement, even if the keyword does not need to be in front of the Clob field name.
Actually the DISTINCT keyword is valid for all fields in SQL. And the Clob field is not able to match like similar, so
Unable to repeat the operation.
Two kinds of solutions:
1, in the SQL to call the method into the VARCHAR2 field, and then distinct, the limitations of this approach is obvious.
2, change the writing method of SQL script, first detect the collection without CLOB field, and then in the outer layer with EXISTS keyword or in keyword filter.

Copy Code code as follows:

String sqlsel2 = "Select Jsonbody from Db_ps_listcatalog where" +
"ListType = ' sh11 ' for Update";
String col= "Jsonbody";

public boolean Updateclob (String sql,string col,string buf) {
Boolean flag=false;
Statement Stem=null;
Connection Conn=null;
ResultSet Rs=null;
Writer WR = null;
try{
Conn= dp.getconnection ();
Conn.setautocommit (FALSE);
Stem=conn.createstatement ();
rs = stem.executequery (SQL);
if (Rs.next ()) {
CLOB CLOB = (CLOB) rs.getclob (COL);
Java.lang.reflect.Method Methodtoinvoke = Clob.getclass (). GetMethod (
"Getcharacteroutputstream", (class[]) null);
WR = (Writer) Methodtoinvoke.invoke (CLOB, (object[)) null);
BufferedWriter bw = new BufferedWriter (WR);
Bw.write (BUF);
Bw.flush ();
Bw.close ();
Conn.commit ();
Conn.close ();
}
Flag=true;
catch (Exception ex) {
try {
Conn.rollback ();
catch (SQLException e) {
E.printstacktrace ();
}
}
return flag;
}