How to deal with clob fields in oracle databases

When creating a knowledge base, it is obviously not enough to store articles with common VARCHAR2. There are only 4000 bytes in the partition, and there are not many words in it,
The CLOB data type can store up to 8 GB of data. However, this field has many special features. record it.
Insert:
It cannot be written directly in SQL. The SQL script has a limit on the number of characters, and the article contains many special characters, such as line breaks, quotation marks,
And so on. A common practice on the Internet is to insert an empty CLOB field and use the empty_clob () method to create an empty field, for example: Copy codeThe Code is as follows: insert into T_TOPIC (TOPIC_ID, TOPIC_CONTENT) VALUES ('20170101', empty_clob ());

Then use the query statement SELECT TOPIC_CONTENT FROM T_TOPIC WHERE TOPIC_ID = '2013' for update,
To construct an updated STATEMENT. After obtaining the ResultSet, update the CLOB field.Copy codeThe Code is as follows: ResultSet rs = p0000.exe cuteQuery ();
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 should be placed in the transaction, that is, setAutoCommit (false) should be set after the Connection is obtained );
Update:
SEELCT is also used for the update... For update Method
You also need to set the transaction

Read:Copy codeThe Code is 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 ("failed to read the article content.", e );
}

Query particularity:
Data Tables with CLOB fields cannot be filtered using the DISTINCT keyword in SQL statements, even if the keywords do not need to be before the CLOB field name,
In fact, the DISTINCT keyword is valid for all fields in SQL. The CLOB field cannot be matched LIKE. Therefore,
Repeated operations cannot be performed.
Two solutions:
1. After calling a method in SQL to convert it to the VARCHAR2 field, then DISTINCT, the limitations of this method are obvious.
2. Change the SQL script writing method. First, find the set without the CLOB field, and then use the EXISTS keyword or IN keyword IN the outer layer for filtering.Copy codeThe Code is 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.exe cuteQuery (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;
}