Insert large text by using the Oracle long field __oracle

I need a lot of inserting large text when I write an application, but Oracle's CLOB is cumbersome to do, so I didn't choose to use CLOB, but I used the long type in previous versions of Oracle [but the long type has some limitations, You can have only one long field in a table. At the beginning of the

I inserted the database directly using INSERT INTO table1 values (A,b[long]), but Oracle had some restrictions that one statement could not exceed 4,000 characters and reported ORA-01704 errors.

A workaround for a document looking for Oracle is to insert a string into the database using the Setcharacterstream () method for fields long.

Code:
sql= INSERT into msg_info values (?,?,?,? [ Long field], ' C ', sysdate,sysdate+ "+msgterm+",?) ";
PSTAT1 = conn.preparestatement (sql);
Pstat1.setlong (1, msg_id);
Pstat1.setint (2, msg_gp_id);
Pstat1.setstring (3, Msg_title);
Pstat1.setcharacterstream (4,new StringReader (msg_info.tostring ()), msg_info.length ());
Conn.commit ();
Pstat1.setlong (5, this.upid);

Long type Oracle is not recommended, or using CLOB is the right way, after all, it is now 21st century.
But Clob does use up very troublesome, the landlord's post collection.

****************************************
Using JDBC to access long types of data in Oracle
****************************************

You can save large text using a long type in Oracle, but the operation Long data differs from the general data type, and the following example shows how to save the data to a long field.

The data is saved to a long field, which is tested using a table that has the following creation statement:

CREATE TABLE T_longtest
(
ID INTEGER not NULL,
FILENAME VARCHAR2 (MB),
CONTENT LONG
)
LOGGING
NoCache
Noparallel;

CREATE UNIQUE INDEX pk_t_longtest on T_longtest
(ID)
LOGGING
Noparallel;

ALTER TABLE t_longtest ADD (
CONSTRAINT pk_t_longtest PRIMARY KEY (ID));

Inserting a long type of data

BufferedReader bufreader = new BufferedReader (new FileReader (file));
Integer id = integer.valueof (pubfun1.createmaxno (test_long_id, 1));
PreparedStatement pstmt = con.preparestatement (Insert_long_sql);
Pstmt.setobject (1, id);
Pstmt.setobject (2, fileName);
Pstmt.setcharacterstream (3, Bufreader, (int) length);
int retvalue = Pstmt.executeupdate ();
if (RetValue!= 1) {
Logger.error ("Error on insert value");
}
Bufreader.close ();
Pstmt.close ();

The Insert_long_sql value is:
INSERT into T_longtest (ID, FILENAME, CONTENT) VALUES (?,?,?)
Note You need to use the Setcharacterstream method to set the value of a long field.

Read data of long type

Reading also needs to be read using the stream, and the following code fragment illustrates the method of reading a long type of field.
PreparedStatement pstmt = con.preparestatement (Query_long_col_sql);
Pstmt.setobject (1, id);
ResultSet rs = Pstmt.executequery ();
if (Rs.next ()) {
Reader reader = Rs.getcharacterstream (1);
BufferedReader bufreader = new BufferedReader (reader);
StringBuffer strbuf = new StringBuffer ();
String Line;
while (line = Bufreader.readline ())!= null) {
Strbuf.append (line);
Strbuf.append ("/r/n");
}
Bufreader.close ();
System.out.println ("The content is:" + strbuf.tostring ());
}
The Queyr_long_col_sql value is: SELECT CONTENT from T_longtest WHERE id=?

To update a long type of data

Updating a long type method is the same as inserting code, except that the SQL statement is different. The following code breaks the description of how to update a long type of data. (This example does not use the T_longtest table)
StringReader reader = new StringReader (xmlstring);
Pstmt = Con
. Preparestatement (report_model_content_update_sqlstring);
Pstmt.setcharacterstream (1, Reader, xmlstring.length ());
Pstmt.setint (2, reportmodelid);
if (pstmt.executeupdate () = = 0) {
Logger
. Error ("Error on Update");
}
Reader.close ();
The value of the report_model_content_update_sqlstring is:
UPDATE Report_model SET content=? WHERE report_model_id=?

Summarize:

From the example above, you can see that the operation of a long type of field is primarily through Characterstream, if you are updating the database or inserting data into the database using PreparedStatement's Setcharacterstream, And the length of the parameter and string of the incoming reader type. If you are retrieving a long type of data from a database, use the Getcharacterstream method to get an object of reader type, and then you can get a long type of data from it.