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") ")")
/ * Query this CLOB object and lock * /
ResultSet RS = Stmt.executeQuery ("SELECT Clobcol from test_clob where id = '111' for update");
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' for update");
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' for update");
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 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);
}
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);
}
Second, the access of the blob object
1. Insert a new BLOB object to the database
Public Static Void Blobinsert (String Infile) Throws Exception
{
/ * Setting not automatically submit * /
Boolean defaultCommit = conn.getautocommit ();
Conn.setautocommit (false);
Try {
/ * Insert an empty BLOB object * /
Stmt.executeUpdate ("INSERT INTO TEST_BLOB VALUES ('222', EMPTY_BLOB ()") ")
/ * Query this BLOB object and lock * /
ResultSet RS = Stmt.executeQuery ("Select Blobcol from Test_blob where id = '222' for update");
While (rs.next ()) {
/ * Remove this BLOB object * /
Oracle.Sql.blob Blob = (Oracle.Sql.blob) rs.getblob ("blobcol");
/ * Write data to the BLOB object * /
BufferedoutputStream out = new bufferedoutputstream (blob.getbinaryoutputstream ());
BufferedInputStream in = New BufferedInputStream (New FileInputStream (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 BLOB object (which is a modification of the coverage on the basis of the original BLOB object)
Public Static Void Blobmodify (String Infile) Throws Exception
{
/ * Setting not automatically submit * /
Boolean defaultCommit = conn.getautocommit ();
Conn.setautocommit (false);
Try {
/ * Query your blob object and lock * /
ResultSet RS = Stmt.executeQuery ("Select Blobcol from Test_blob where id = '222' for update");
While (rs.next ()) {
/ * Remove this BLOB object * /
Oracle.Sql.blob Blob = (Oracle.Sql.blob) rs.getblob ("blobcol");
/ * Write data to the BLOB object * /
BufferedoutputStream out = new bufferedoutputstream (blob.getbinaryoutputstream ());
BufferedInputStream in = New BufferedInputStream (New FileInputStream (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 BLOB object (clear the original BLOB object, change to a brand new BLOB object)
Public Static Void BlobReplace (String Infile) Throws Exception
{
/ * Setting not automatically submit * /
Boolean defaultCommit = conn.getautocommit ();
Conn.setautocommit (false);
Try {
/ * Clear the original BLOB object * /
Stmt.executeUpdate ("Update Test_blob Set Blobcol = EMPTY_BLOB () Where ID = '222');
/ * Query this BLOB object and lock * /
ResultSet RS = Stmt.executeQuery ("Select Blobcol from Test_blob where id = '222' for update");
While (rs.next ()) {
/ * Remove this BLOB object * /
Oracle.Sql.blob Blob = (Oracle.Sql.blob) rs.getblob ("blobcol");
/ * Write data to the BLOB object * /
BufferedoutputStream out = new bufferedoutputStream (blob.getbinaryoutputstream ()); bufferedinputstream in = New buffutStream (new fileInputstream (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, blob object read
Public Static Void Blobread (String Outfile) Throws Exception
{
/ * Setting not automatically submit * /
Boolean defaultCommit = conn.getautocommit ();
Conn.setautocommit (false);
Try {
/ * Query blob object * /
ResultSet RS = Stmt.executeQuery ("Select Blobcol from Test_blob where id = '222');
While (rs.next ()) {
/ * Remove this BLOB object * /
Oracle.Sql.blob Blob = (Oracle.Sql.blob) rs.getblob ("blobcol");
/ * Output in binary form * /
BufferedoutputStream out = new bufferedoutputstream (new fileoutputstream (outfile);
BufferedInputStream in = new bufferedinputstream (blob.getbinaryStream ());
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);
}
Observe the above-mentioned procedures to access the LOB type field, we can see that there are several significant differences from the other types of fields:
First, it must be canceled automatically.
Before the start of the access operation, you must cancel the automatic submission with SetAutocommit (FALSE). Other types of fields have no special requirements. This is because when accessing the LOB type field, you can usually perform multiple operations. In this case, Oracle will throw a "read violation order" error.
Second, the insert is different.
LOB data cannot be directly inserted as other types of data (INSERT). An empty LOB object must be inserted before insertion, and the CLOB type empty object is EMPTY_CLOB (), the blob type empty object is EMPTY_BLOB (). Then the select command query gets the previously inserted record and locks, followed by modifying the empty object to the LOB object to be inserted.
Third, the modification is different.
Other types of fields are modified, use the Update ... set ... command. The Lob type field can only be used to query and lock it with the select ... for update command, and then you can modify it. And there are two modifications: First, the modification (ie, overwriting modification) on the basis of the original data, perform the select ... for update and then change the data; the other is the replacement (first data clear, then modify), first execute The UPDATE command sets the value of the LOB field to an empty LOB object and then performs the first change. Replacement methods are recommended to achieve the same effect as follows with other fields. Fourth, the LOB operation class provided by the Database JDBC driver is used when accessing.
For Oracle Database, Oracle.Sql.Clob and Oracle.Sql.blob should be used. When not using the LOB class provided by the Database JDBC driver, the program is easy to appear "Abstract Method Call" error, because JDBC defined by Java.sql.clob and java.sql.blob interface, some of them Not truly implemented in the drivers provided by the database manufacturers.
The fifth is that the access method is similar to the file operation.
For the BLOB type, apply the InputStream / OutputStream class, which is not encoded and converted one byte by one byte. Oracle.sql.blob class provides both getBinaryStream () and getBinaryOutputStream () two methods, the former method is used to read the Oracle's BLOB field, and the latter method is used to write data to the Oracle's BLOB field.
For Clob type, apply the Reader / Writer class, this class performs encoding conversion. The Oracle.SQL.Clob class provides both a getcharacterStream () and GetCharacterOutputStream (), the former method is used to read the Oracle's Clob field, and the latter method is used to write data to the CLOB field of Oracle.
It should be noted that in order to significantly improve program execution efficiency, read and write to the blob / clob field should use buffer operation classes (with buffered prefix), ie: bufferedInputstream, BufferedReader, BufferedWriter. The buffer operation class is all used in the routine.
Summary: Manipulate the LOB field of the Oracle database through the JDBC, not more inserted, modified, replaced, and read four ways, and it is not difficult to master. Pay attention to the few points mentioned above, combine reading routine source procedures, users will soon understand the use of LOB type fields, will also understand this type of field! Download Download >>
.
I am getting it,
JDK1.4
Can only be used in the future
Rs.getClob
"
Content
"
);
in
1.4
I can use it before
(Oracle.Sql.Clob) rs.getObject (
"
Content
"
)
