In Oracle, the field of LOB (Large Object, large object) is now more and more. Because of this type of field, capacity is large (up to 4GB data), and a table can have multiple fields, which is flexible, suitable for business areas of data volume (such as image, file) Wait). LONG, long RAW, etc. field, although the storage capacity is not small (up to 2GB), but now there is only one such type of field limit, it is rarely used.
The LOB type is divided into two types of BLOB and Clob: BINARY LARGE Object, which is suitable for storage non-text byte stream data (such as programs, images, audio, etc.). CLOB, ie character largon Object, is related to character set, suitable for storage text type data (such as historical files, most works, etc.).
Below the program instance illustrate several cases of manipulating the Oracle database LOB type field through the JDBC.
First establish a database table for the following two tests, the Power Designer PD model is as follows:
Construct the table SQL statement is:
Create Table Test_Clob (ID Number (3), Clobcol Clob)
Create Table Test_blob (ID Number (3), Blobcol Blob)
First, the access of the Clob object
1. Insert a new CLOB object into the database
Public Static Void Clobinsert (String Infile) Throws Exception
{
/ * Setting not automatically submit * /
Boolean defaultCommit = conn.getautocommit ();
Conn.setautocommit (false);
Try {
/ * Insert an empty CLOB object * /
Stmt.executeUpdate ("INSERT INTO TEST_CLOB VALUES ('' '' 111 '' ', EMPTY_CLOB ())") ")
/ * Query this CLOB object and lock * /
ResultSet RS = Stmt.executeQuery ("SELECT Clobcol from test_clob where id = '' '' 111 '' ''" ");
While (rs.next ()) {
/ * Remove this CLOB object * /
Oracle.Sql.Clob Clob = (Oracle.Sql.Clob) Rs.getClob ("Clobcol");
/ * Write data to the CLOB object * /
BufferedWriter out = new bufferedwriter (Clob.getCharacteroutputstream ());
BufferedReader IN = New BufferedReader; InfileReader (Infile);
INT C;
While ((c = in.read ())! = - 1) {
Out.write (c);
}
In.Close ();
Out.close ();
}
/ * Official submission * /
CONN.COMMIT ();
} catch (exception ex) {
/ * Error Back * /
CONN. ROLLBACK ();
Throw ex;}
/ * Restore the original submission status * /
Conn.setautocommit (DefaultCommit);
}
2. Modify the Clob object (which is a modification of the overwriting on the original CLOB object)
Public Static Void Clobmodify (String Infile) Throws Exception
{
/ * Setting not automatically submit * /
Boolean defaultCommit = conn.getautocommit ();
Conn.setautocommit (false);
Try {
/ * Query the Clob object and lock * /
ResultSet RS = Stmt.executeQuery ("SELECT Clobcol from test_clob where id = '' '' 111 '' ''" ");
While (rs.next ()) {
/ * Get this CLOB object * /
Oracle.Sql.Clob Clob = (Oracle.Sql.Clob) Rs.getClob ("Clobcol");
/ * Covered modification * /
BufferedWriter out = new bufferedwriter (Clob.getCharacteroutputstream ());
BufferedReader IN = New BufferedReader; InfileReader (Infile);
INT C;
While ((c = in.read ())! = - 1) {
Out.write (c);
}
In.Close ();
Out.close ();
}
/ * Official submission * /
CONN.COMMIT ();
} catch (exception ex) {
/ * Error Back * /
CONN. ROLLBACK ();
Throw EX;
}
/ * Restore the original submission status * /
Conn.setautocommit (DefaultCommit);
}
3, replace the Clob object (clear the original CLOB object, replace it with a brand new CLOB object)
Public Static Void ClobReplace (String Infile) Throws Exception
{
/ * Setting not automatically submit * /
Boolean defaultCommit = conn.getautocommit ();
Conn.setautocommit (false);
Try {
/ * Clear original CLOB object * /
Stmt.executeUpdate ("Update Test_Clob Set Clobcol = EMPTY_CLOB () where id = '' '' 111 '' ');
/ * Query the Clob object and lock * /
ResultSet RS = Stmt.executeQuery ("SELECT Clobcol from test_clob where id = '' '' 111 '' ''" ");
While (rs.next ()) {
/ * Get this CLOB object * /
Oracle.Sql.Clob Clob = (Oracle.Sql.Clob) Rs.getClob ("Clobcol");
/* update data */
BufferedWriter out = new bufferedwriter (Clob.getCharacteroutputstream ());
BufferedReader in = New FileReader (Infile); INT C;
While ((c = in.read ())! = - 1) {
Out.write (c);
}
In.Close ();
Out.close ();
}
/ * Official submission * /
CONN.COMMIT ();
} catch (exception ex) {
/ * Error Back * /
CONN. ROLLBACK ();
Throw EX;
}
/ * Restore the original submission status * /
Conn.setautocommit (DefaultCommit);
}
4, Clob object read
Public Static Void Clobread (String Outfile) THROWS EXCEPTION
{
/ * Setting not automatically submit * /
Boolean defaultCommit = conn.getautocommit ();
Conn.setautocommit (false);
Try {
/ * Query Clob object * /
ResultSet RS = Stmt.executeQuery ("Select * from test_clob where id = '' '' 111 '' ');
While (rs.next ()) {
/ * Get Clob object * /
Oracle.Sql.Clob Clob = (Oracle.Sql.Clob) Rs.getClob ("Clobcol");
/ * Output in character form * /
BufferedReader in = New BufferedReader (Clob.GetCharacterstream ());
BufferedWriter Out = New BufferedWriter (New FileWriter);
INT C;
While ((c = in.read ())! = - 1) {
Out.write (c);
}
Out.close ();
In.Close ();
}
} catch (exception ex) {
CONN. ROLLBACK ();
Throw EX;
}
/ * Restore the original submission status * /
Conn.setautocommit (DefaultCommit);
}
