Method One:
In Oracle, fields for LOB (Large object, large objects) types are now used more and more. Because of this type of field, large capacity (up to 4GB of data), and a table can have more than one of these types of fields, is flexible, applicable to a very large number of data business areas (such as images, files, etc.). Fields of type long, long, and so on, although the storage capacity is not small (up to 2GB), are now rarely used because there can be only one type of field in a table.
LOB types are divided into BLOBs and Clob two: BLOBs, which are binary large objects (Binary Large object), are suitable for storing text stream data (such as programs, images, audio, video, etc.) that are not literal. CLOB, a character-type large object (Character Large object), is associated with a character set that is suitable for storing text-type data (such as historical archives, voluminous writings, etc.).
The following is an example of a program that manipulates Oracle database LOB type fields through JDBC.
Set up the following two test database tables, the Power Designer PD model is as follows:
The Build table SQL statement is:
CREATE TABLE Test_clob (ID number (3), Clobcol CLOB)
CREATE TABLE Test_blob (ID number (3), Blobcol BLOB)
I. Access to CLOB objects
1. Insert a new CLOB object into the database
public static void Clobinsert (String infile) throws Exception
{
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);
try {
Stmt.executeupdate ("INSERT into Test_clob VALUES ('", Empty_clob ()) ");
ResultSet rs = stmt.executequery ("Select Clobcol from Test_clob WHERE id= ' for UPDATE");
while (Rs.next ()) {
Oracle.sql.CLOB CLOB = (Oracle.sql.CLOB) rs.getclob ("Clobcol");
BufferedWriter out = new BufferedWriter (Clob.getcharacteroutputstream ());
BufferedReader in = new BufferedReader (new FileReader (infile));
int C;
while ((C=in.read ())!=-1) {
Out.write (c);
}
In.close ();
Out.close ();
}
Conn.commit ();
catch (Exception ex) {
Conn.rollback ();
Throw ex;
}
Conn.setautocommit (Defaultcommit);
}
2, modify the Clob object (is the original Clob object based on the coverage of the modification)
public static void Clobmodify (String infile) throws Exception
{
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);
try {
ResultSet rs = stmt.executequery ("Select Clobcol from Test_clob WHERE id= ' for UPDATE");
while (Rs.next ()) {
Oracle.sql.CLOB CLOB = (Oracle.sql.CLOB) rs.getclob ("Clobcol");
BufferedWriter out = new BufferedWriter (Clob.getcharacteroutputstream ());
BufferedReader in = new BufferedReader (new FileReader (infile));
int C;
while ((C=in.read ())!=-1) {
Out.write (c);
}
In.close ();
Out.close ();
}
Conn.commit ();
catch (Exception ex) {
Conn.rollback ();
Throw ex;
}
Conn.setautocommit (Defaultcommit);
}
3, replace the Clob object (the original Clob object cleared, replaced by a new Clob object)
public static void Clobreplace (String infile) throws Exception
{
Boolean defaultcommit = Conn.getautocommit ();
Conn.setautocommit (FALSE);
try {
Stmt.executeupdate ("UPDATE test_clob SET clobcol=empty_clob () WHERE id= ' 111");
ResultSet rs = stmt.executequery ("Select Clobcol from Test_clob WHERE id= ' for UPDATE");
while (Rs.next ()) {
Oracle.sql.CLOB CLOB = (Oracle.sql.CLOB) rs.getclob ("Clobcol");
BufferedWriter out = new BufferedWriter (Clob.getcharacteroutputstream ());
BufferedReader in = new BufferedReader (new FileReader (infile));
int C;
while ((C=in.read ())!=-1) {
Out.write (c);
}
In.close ();
Out.close ();
}
Conn.commit ();
catch (Exception ex) {
Conn.rollback ();
Throw ex;
}
Conn.setautocommit (Defaultcommit);
}
Method Two:
In Oracle, the maximum number of bytes supported by VARCHAR2 is 4KB, so for some long string processing, we need to use CLOB type fields, CLOB field maximum support 4GB.
There are several other types:
BLOB: binary, if exe,zip
CLOB: Single-byte code, such as a general text file.
Nlob: Multi-Byte code, such as a file in UTF format.
Here's how to work with the clog field, which is used in the Help document section of our project.
1, first is written
View Plaincopy to Clipboardprint?
/* The Hcontent field in the following table pf_help_content is CLOB type * *
To generate a Help ID from a serializer
Map map = Query.getmap ("Select To_char (seq_hid.nextval) HID from DUAL");
HID = string.valueof (Map.get ("HID"));
Insert a piece of data, note the Clob field, you need to first insert an empty CLOB type Empty_clob (), and then update the CLOB field individually
sql = "Insert into Pf_help_content (hid,hcontent) VALUES (?, Empty_clob ())";
Try
{
Perform an Insert
RTN = Dbutils.executeupdate (Sql,hid);
/* After the insert succeeds, modify the hcontent field content * *
Getting a database connection
Connection conn = Dbutils.getconnection ();
Manually submit
Conn.setautocommit (FALSE);
Defining ResultSet and Clob variables
ResultSet rs = null;
Oracle.sql.CLOB CLOB = null;
Update SQL
String Sqlclob = "Select Hcontent from Pf_help_content Where hid=?" For Update ";
Java.sql.PreparedStatement pstmt = conn.preparestatement (Sqlclob);
HID is VARCHAR2 type, so use setstring
Pstmt.setstring (1,hid);
Execute UPDATE statement
Rs= Pstmt.executequery ();
if (Rs.next ())
{
Get the content of the hcontent just now, which is just added Empty_clob ()
CLOB = (Oracle.sql.CLOB) rs.getclob (1);
}
Need to output with clob.getcharacteroutputstream () flow
Writer write = Clob.getcharacteroutputstream ();
Write specific content, helpform.gethcontent () saved is Help content
Write.write (Helpform.gethcontent ());
Write.flush ();
Write.close ();
Rs.close ();
Submit
Conn.commit ();
Conn.close ();
}
catch (Exception ex)
{
//.........
}
2, modify CLOB field content
View Plaincopy to Clipboardprint?
/* Change is basically consistent with the insert, but also with for update to implement * *
If the content of the field before the modification is longer than the currently modified length, part of the end will still exist
So before you modify the content, you need to empty the pf_help_content content
sql = "Update pf_help_content SET hcontent=empty_clob () Where hid=?";
Try
{
RTN = Dbutils.executeupdate (Sql,hid);
The following actions are the same as when added
Connection conn = Dbutils.getconnection ();
Conn.setautocommit (FALSE);
ResultSet rs = null;
Oracle.sql.CLOB CLOB = null;
String Sqlclob = "Select Hcontent from Pf_help_content Where hid=?" For Update ";
Java.sql.PreparedStatement pstmt = conn.preparestatement (Sqlclob);
Pstmt.setstring (1,hid);
Rs= Pstmt.executequery ();
if (Rs.next ())
{
CLOB = (Oracle.sql.CLOB) rs.getclob (1);
}
Writer write = Clob.getcharacteroutputstream ();
Write.write (Helpform.gethcontent ());
Write.flush ();
Write.close ();
Rs.close ();
Conn.commit ();
Conn.close ();
}
catch (Exception ex)
{
//...
}
3, remove the Clob field text content
View Plaincopy to Clipboardprint?
/* The front part is consistent * * *
Connection conn = Dbutils.getconnection ();
Conn.setautocommit (FALSE);
ResultSet rs = null;
Oracle.sql.CLOB CLOB = null;
String Sqlclob = "Select Hcontent from Pf_help_content Where hid=?";
Java.sql.PreparedStatement pstmt = conn.preparestatement (Sqlclob);
Pstmt.setstring (1,hid);
Rs= Pstmt.executequery ();
if (Rs.next ())
{
Parameter 1 in Rs.getclob (1) refers to the Hcontent field index, where the first field starts at 1 instead of 0.
You can also use the field name to fetch Rs.getclob ("Hcontent")
CLOB = (Oracle.sql.CLOB) rs.getclob (1);
}
if (Clob==null | | clob.length () ==0)
{
Hcontent = "";
}else
{
Take Clob field content as String
Hcontent=clob.getsubstring ((Long) 1, (int) clob.length ());
}
Rs.close ();
Conn.close ();
Request.setattribute ("Hcontent", hcontent);
Reproduced from: http://blog.csdn.net/yoland/article/details/6455982
