Sender: DMoracle (DMoracle), News District: Java
Title: Access the LOB type of Oracle through JDBC (original)
Sending station: Wuhan Baiyun Huanghe Station (July 25, 2003 09:48:16 Friday), station letters
Lobs ("Large Objects") is managed by optimizing space and providing efficient access. Oracle JDBC supports two LOBS: BLOBS (unable structured binary data) and Clobs (character data). Blob and Clob data are accessed through a locker. This locator is actually a pointer. It stores in the table of the database, pointing to the table
BLOB and CLOB data.
In order to operate the LOBS type column, the positioning of this type must be obtained, and the operation is performed by the locator. Oracle.sql.blob and oracle.sql.clob have implemented java.sql.blob and java.sql.cloB interfaces, respectively. In Java applications, you cannot directly generate a LOB object. Only by retrieving data in the database or using CreateTemporary () and EMPTY_L
OB () to construct the LOB object.
1. Operate the LOB locator
The Lob type locator can be obtained or set with the extended method provided by JDBC standard or Oracle. This shows that Oracle's JDBC is operable to operate multimedia data between the Oracle database.
(1) Retrieve the LOB locator:
In a result set or a callablestatement object containing a LOB type, a locator pointing to the LOB data can be obtained by standard (1.2) getblob () or getClob (). You can also use (1.1) getObject () to get it.
If you use the result set or callablestatement to an OracleResultset or OracleCallableStatement object, you can use Oracle's extension method (1.2) getBlob (), getClob () or (1.1) getoracleObject () to get.
example:
(i) Locating the locale from the result set
// Select Lob Locator Into Standard Result Set.
ResultSet RS =
Stmt.executeQuery ("SELECT BLOB_COL, Clob_col from Lob_TABLE");
While (rs.next ())
{
// Get Lob Locators INTO JAVA Wrapper Classes.
Java.sql.blob blob = (java.sql.blob) Rs.GetObject (1);
Java.sql.clob clob = (java.sql.clob) gs.getObject (2);
(... Process ...)
}
You can also shape the results into an oracle.sql.lob type:
// Get Lob Locators INTO JAVA Wrapper Classes.
Oracle.sql.blob blob = (blob) rgetObject (1);
Oracle.Sql.Clob Clob = (Clob) Rs.GetObject (2);
(... Process ...)
(ii) Get the locator of the LOB object from the results of CallableStatement, and its operation method is basically the same as the result set:
OracleCallaBleStatement OCS =
(OracleCalLABLESTATEMENT) Conn.prepareCall ("{? = Call func ()});
Ocs.registeroutparameter (1, ORACLETYPES.CLOB);
Ocs.execute ();
Oracle.sql.clob Clob = OCS.GETCLOB (1); (2) Setting the LOB positioning
In a preparedstatement or callablestatement object containing the LOB type, standard methods can be used: (1.2) setblob (), setClob () or (1.1) setObject () to set the value of the corresponding column or parameter. You can also use (1.2) setblob (), setc to OraclePreParedStatement or OracleCallastatement
LOB () or (1.1) setOracleObject () is operated.
example:
(i) Set the value of the parameter for the preparedStatement:
OraclePreparedStatement OPS = (OraclePreparedStatement) Conn.preparestatement
("INSERT INTO BLOB_TABLE VALUES (?)");
Ops.setblob (1, MY_BLOB);
Ops.execute ();
(ii) Set the value of the parameters for the Callablestatement:
OracleCallaBleStatement OCS =
(OracleCallastatement) Conn.PrepareCall ("{Call Proc (?))}")
Ocs.setClob (1, MY_CLOB);
Ocs.execute ();
2. Operate the LOB type data
Once the locator of the LOB object is obtained, the JDBC method can be utilized to access the data of the LOB object. LOB data can be configured to create a Java array or data stream. However, this data stream is different from other Java data streams because the locating regimen represents LOB data is stored in the table, and we can access LOB data at any time during the data connection.
Oracle.sql.blob and oracle.sql.clob provide methods for accessing LOB data. With these methods, the client can read the data into the data stream, or from the data stream to the LOB, you can also query the length of the LOB data, and you can also turn off the LOB object.
When writing data in the Lob type, you must use the write lock, which can be implemented by select for update and set to non-automatic submission mode.
Operation LOB data, you can use the following methods:
(a) Read BLOB Data: Use Oracle.Sql.blob's getBinaryStream () method to return a java.io.inputStream object, using its read method to read BLOB data. Finally, the close () method of InputStream is used to close.
(b) Write BLOB data: Use Oracle.Sql.blob's getBinaryoutputStream () method to return a java.io.outputstream object, using its WRITE method to write data in BLOB. Finally, the Close () method of OutputStream is closed.
(c) Read Clob Data: Use Oracle.Sql.Clob's getasciistream () method or getCharacterStream () method. The former returns an INPUTSTREAM of an ASCII code, and the latter returns an unicode code Reader. You can also return a String object with getSubstring ().
(d) Write CLOB data: Use Oracle.Sql.Clob's GetASCIIOutputStream () method or getCharacterOutputStream () method. The former returns an OutputStream of an ASCII code, and the latter returns a UNICODE Writer. After using the Write method writes into the data, you want to turn the flush () and a close () method to close. note:
(i) When writing data in the LOB, it is written directly to the database. All of this is transparent to the client, no need to make an UPDATE operation. However, after writing, you need to submit a transaction to actually save to the database.
(ii) When operating data, the JDBC automatically implements the conversion between the character set.
(Iii) JDBC 2.0 specification states that, PreparedStatement of the setBinaryStream () and the setObject () method can be used to initially BLOB columns input data; the PreparedStatement of setAsciiStream (), setUnicodeStream (), setCharacterStream () and the setObject () can be used to and from a CLOB column input data. These methods bypass LOB positioning
Direct access directly to the LOB data itself. In the implementation of the Oracle JDBC driver, this feature is only supported in one configuration of 8.1.6 and later, or uses a higher version of JDBC OCI driver.
example:
(1)
// Read Blob Data from Blom Blob Locator.
InputStream Byte_Stream = my_blob.getbinaryStream ();
Byte [] byte_Array = new byte [10];
INT bytes_read = byte_stream.read (byte_Array);
(2)
// Read Clob Data from Clob Locator Into Reader Char Stream.
Reader char_stream = my_clob.getcharacterstream ();
Char [] char_array = new char [10];
INT Chars_read = char_stream.read (char_array, 0, 10);
(3)
InputStream Asciichar_Stream = my_clob.getasciistream ();
Byte [] asCIICHAR_ARRAY = New byte [10];
INT asciichar_read = asCIICHAR_STREAM.READ (ASCIICHAR_ARRAY, 0, 10);
(4)
Java.io.outputstream outstream;
// r r t b b a
Byte [] DATA = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
// write the array of binary data to a blob
Outstream = ((blob) my_blob) .GetbinaryoutputStream ();
OutStream.write (DATA);
(5)
Java.io.writer;
// Read Data Into a Character Array
Char [] DATA = {'0', '1', '2', '3', '4', '5', '6', '7', '8', '9'}; // Write The Array of Character Data To a Clob
Writer = ((clob) my_clob) .GetCharacterOutputStream ();
Writer.write (DATA);
Writer.flush ();
Writer.close ();
(6)
Java.io.outputstream out;
// r r t b b a
Byte [] DATA = {'0', '1', '2', '3', '4', '5', '6', '7', '8', '9'};
// Write the array of ascii data to a clob
OUT = ((Clob) Clob) .getasciiOutputStream ();
Out.write (DATA);
Out.flush ();
Out.close ();
3. Operate a loop process:
(1) Newly built a table containing the LOB column:
String cmd = "CREATE TABLE MY_BLOB_TABLE (x varchar2 (30), c blob);"
Stmt.executeUpdate (cmd);
(2) Generate a blob entity, which is to generate a locator:
Stmt.executeUpdate ("INSERT INTO My_BLOB_TABLE VALUES ('ROW1', EMPTY_BLOB ());")
(3) Get blob locator:
BLOB BLOB;
CMD = "SELECT * from my_blob_table where x = 'row1'"
ResultSet Rset = Stmt.executeQuery (CMD);
RSET.NEXT ();
BLOB BLOB = ((ORACleResultset) Rset) .getblob (2);
(4) Specify the data to be stored in the database:
File binaryfile = new file ("john.gif");
System.out.println ("john.gif length =" binaryfile.length ());
FileInputStream Instream = New FileInputStream (binaryfile);
OutputStream outstream = blob.getbinaryoutputstream ();
(5) Get the ideal buffer:
INT size = blob.getBuffersize ();
Byte [] buffer = new byte [size];
INT length = -1;
(6) Read the data into the buffer and write to the database:
While (length = instream.read (buffer)! = -1)
Outstream.write (buffer, 0, length);
Instream.close ();
Outstream.close ();
(7) After the data is written to the database, you can read: // Select the blob - what we are really doing here
// is getting the blob locator Into a result set
BLOB BLOB;
CMD = "SELECT * from my_blob_table";
ResultSet Rset = Stmt.executeQuery (CMD);
// Get the blob data - Cast to OracleResult Set To
// Retrieve The Data in Oracle.sql Format
String Index = ((ORACleResultset) Rset .getstring (1);
BLOB = (ORACleResultset) Rset) .getblob (2);
// Get the length of the blob
INT length = blob.length ();
// Print the length of the blob
System.out.println ("BLOB Length" Length;
// read the blob Into a byte array
// insteit the blob from the array
Byte Bytes [] = blob.getbytes (1, Length);
PrintBytes (Bytes, Length);
It can be seen that LOB must be initialized before the operation is performed, that is, to assign a locator. Use EMPTY_LOB () to specify a specific tag to LOB. This time you can't read the content because it doesn't have a positioner, just an empty pointer. If you read, you will throw an exception.
4. Open / close LOB
Users don't have to turn on, turn off the LOBS operation. If the user considers the performance of the performance, it may also be able to do so. If the user does not perform LOB operations between open and closing the operation, then each change of the LOB will implicit open, turn off the LOBS, tact the trigger related to the LOB. The content of LOB will be reflected in time.
If the user performs a LOB operation between the open and off operation, the LOB changes until it is closed, and the trigger associated with it will be triggered.
The user can call the open () or open (int mode) method to open the LOBS. All read and write operations do not touch the trigger associated with it prior to shutting down. You can use iSopen () to determine if a LOB has been turned off. The Mode parameter can only be one of Mode_Readonly and Mode_ReadWrite. These two values are defined in Oracle.Sql.blob and Clob. Literally
It can be seen that the limitations in each mode can be seen. If you write in Mode_Readonly, you will throw SQL anomalies.
Before conducting transaction commit, you must turn off the LOBS. Otherwise, it will be wrong: the system is turned off, and the transaction is successfully submitted. But all triggers related to it will not be executed, which affects the integrity of the data, and even cause the system to crash.
5. Discussion on some problems
(1) Oracle8i is more important than LOB with Oracle7 than LONG, RAW.
(a) LOB can store 4G size data, which is 2 times that of LONG;
(b) Single table can have multiple LOB type columns, but only one long column;
(c) LOB uses a random read, and the last is read in order; and the LOB is transmitted in the form of a block.
(2) LOB can only store Locator, but it can also store data less than 4K; if more than 4K, the database automatically removes it from the table, puts another segment, even other database space. The original value is replaced with LOCATOR. (3) When copying the LOB column (INSERT, UPDATE operation), replication is not just Locator, but also the data of the LOB.
(4) If the character set used by the user is growing, the database uses the Unicode character set to unify storage.
(5) There is a Locator in the table regardless of the part of the database in the database. Locator can be seen as a pointer to the true location of the LOB data. There is a unique LOCATOR for the LOB column of a row.
(6) Generally, the LOB is initialized to EMPTY, or it can be initialized with less than 4K data.
(7) Can you store data less than 4K in a LOB quotation, you can specify by command Disable Storage.
6. Summary
Oracle's long, RAW These two types of TEXT, Image of DM3 correspond to the way they are stored, and the access methods are similar.
Oracle's Clob and Blob have their own unique, and the two types are recommended from the literature to Oracle comparison.
-
※ Source: · Wuhan Baiyun Huanghe Station bbs.Whnet.edu.cn · [From: 202.114.1.108]
[
Classification discussion area] [
Previous] [
This discussion area] [
Next] [
Read the same topic]
