Oracle8i JDBC Developer's Guide and Reference Release 2 (8.1.6) A81354-01 |
|
This section describes how to read and write data to and from binary large objects (BLOBs) and character large objects (CLOBs) in an Oracle database, using LOB locators.
For general information about Oracle8i LOBs and how to use them, see the Oracle8i Application Developer's Guide--Large Objects (LOBs).
Standard as well as Oracle-specific getter and setter methods are available for retrieving or passing LOB locators from or to the database.
Given a standard JDBC result set (java.sql.ResultSet
) or callable statement (java.sql.CallableStatement
) that includes BLOB or CLOB locators, you can access the locators by using standard getter methods, as follows. All the standard and Oracle-specific getter methods discussed here take either an int
column index or a String
column name as input.
getBlob()
and getClob()
methods, which return java.sql.Blob
and Clob
objects, respectively.
getObject()
method, which returns java.lang.Object
, and cast the output as desired.
If you retrieve or cast the result set or callable statement to an OracleResultSet
or OracleCallableStatement
object, then you can use Oracle extensions as follows:
getBLOB()
and getCLOB()
, which return oracle.sql.BLOB
and CLOB
objects, respectively.
getOracleObject()
method, which returns an oracle.sql.Datum
object, and cast the output appropriately.
getBlob()
and getClob()
, which return oracle.jdbc2.Blob
and Clob
objects, respectively. (These Blob
and Clob
interfaces mimic the standard interfaces available in JDK 1.2.x.)
If using
Note:
getObject()
or getOracleObject()
, then remember to cast the output, as necessary. For more information, see "Casting Your get Method Return Values".
Assume the database has a table called lob_table
with a column for a BLOB locator, blob_col
, and a column for a CLOB locator, clob_col
. This example assumes that you have already created the Statement
object, stmt
.
First, select the LOB locators into a standard result set, then get the LOB data into appropriate Java classes:
// 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)rs.getObject(2); (...process...) }
The output is cast to java.sql.Blob
and Clob
. As an alternative, you can cast the output to oracle.sql.BLOB
and CLOB
to take advantage of extended functionality offered by the oracle.sql.*
classes. For example, you can rewrite the above code to get the LOB locators as:
// Get LOB locators into Java wrapper classes. oracle.sql.BLOB blob = (BLOB)rs.getObject(1); oracle.sql.CLOB clob = (CLOB)rs.getObject(2); (...process...)
The callable statement methods for retrieving LOBs are identical to the result set methods.
For example, if you have an OracleCallableStatement
ocs
that calls a function func
that has a CLOB output parameter, then set up the callable statement as in the following example.
This example registers OracleTypes.CLOB
as the typecode of the output parameter.
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call func()}"); ocs.registerOutParameter(1, OracleTypes.CLOB); ocs.execute(); oracle.sql.CLOB clob = ocs.getCLOB(1);
Given a standard JDBC prepared statement (java.sql.PreparedStatement
) or callable statement (java.sql.CallableStatement
), you can use standard setter methods to pass LOB locators, as follows. All the standard and Oracle-specific setter methods discussed here take an int
parameter index and the LOB locator as input.
setBlob()
and setClob()
methods, which take java.sql.Blob
and Clob
locators as input.
setObject()
method, which simply specifies a java.lang.Object
input.
Given an Oracle-specific OraclePreparedStatement
or OracleCallableStatement
, then you can use Oracle extensions as follows:
setBLOB()
and setCLOB()
, which take oracle.sql.BLOB
and CLOB
locators as input, respectively.
setOracleObject()
method, which simply specifies an oracle.sql.Datum
input.
setBlob()
and setClob()
, which take oracle.jdbc2.Blob
and Clob
locators as input, respectively. (These Blob
and Clob
interfaces mimic the standard interfaces available in JDK 1.2.x.)
If you have an OraclePreparedStatement
object ops
and a BLOB named my_blob
, then write the BLOB to the database as follows:
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement ("INSERT INTO blob_table VALUES(?)"); ops.setBLOB(1, my_blob); ops.execute();
If you have an OracleCallableStatement
object ocs
and a CLOB named my_clob
, then input the CLOB to the stored procedure proc
as follows:
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{call proc(?))}"); ocs.setClob(1, my_clob); ocs.execute();
Once you have a LOB locator, you can use JDBC methods to read and write the LOB data. LOB data is materialized as a Java array or stream. However, unlike most Java streams, a locator representing the LOB data is stored in the table. Thus, you can access the LOB data at any time during the life of the connection.
To read and write the LOB data, use the methods in the oracle.sql.BLOB
or oracle.sql.CLOB
class, as appropriate. These classes provide functionality such as reading from the LOB into an input stream, writing from an output stream into a LOB, determining the length of a LOB, and closing a LOB.
Notes:
|
To read and write LOB data, you can use these methods:
getBinaryStream()
method of an oracle.sql.BLOB
object to retrieve the entire BLOB
as an input stream. This returns a java.io.InputStream
object.
As with any InputStream
object, use one of the overloaded read()
methods to read the LOB data, and use the close()
method when you finish.
getBinaryOutputStream()
method of an oracle.sql.BLOB
object to retrieve the BLOB as an output stream. This returns a java.io.OutputStream
object to be written back to the BLOB.
As with any OutputStream
object, use one of the overloaded write()
methods to update the LOB data, and use the close()
method when you finish.
getAsciiStream()
or getCharacterStream()
method of an oracle.sql.CLOB
object to retrieve the entire CLOB as an input stream. The getAsciiStream()
method returns an ASCII input stream in a java.io.InputStream
object. The getCharacterStream()
method returns a Unicode
input stream in a java.io.Reader
object.
As with any InputStream
or Reader
object, use one of the overloaded read()
methods to read the LOB data, and use the close()
method when you finish.
You can also use the getSubString()
method of oracle.sql.CLOB
object to retrieve a subset of the CLOB as a character string of type java.lang.String
.
getAsciiOutputStream()
or getCharacterOutputStream()
method of an oracle.sql.CLOB
object to retrieve the CLOB as an output stream to be written back to the CLOB. The getAsciiOutputStream()
method returns an ASCII output stream in a java.io.OutputStream
object. The getCharacterOutputStream()
method returns a Unicode
output stream in a java.io.Writer
object.
As with any OutputStream
or Writer
object, use one of the overloaded write()
methods to update the LOB data, and use the flush()
and close()
methods when you finish.
Use the getBinaryStream()
method of the oracle.sql.BLOB
class to read BLOB data. The getBinaryStream()
method reads the BLOB data into a binary stream.
The following example uses the getBinaryStream()
method to read BLOB data into a byte stream and then reads the byte stream into a byte array (returning the number of bytes read, as well).
// Read BLOB data from BLOB locator. InputStream byte_stream = my_blob.getBinaryStream(); byte [] byte_array = new byte [10]; int bytes_read = byte_stream.read(byte_array); ...
The following example uses the getCharacterStream()
method to read CLOB
data into a Unicode
character stream. It then reads the character stream into a character array (returning the number of characters read, as well).
// 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); ...
The next example uses the getAsciiStream()
method of the oracle.sql.CLOB
class to read CLOB data into an ASCII character stream. It then reads the ASCII stream into a byte array (returning the number of bytes read, as well).
// Read CLOB data from CLOB locator into Input ASCII character stream Inputstream asciiChar_stream = my_clob.getAsciiStream(); byte[] asciiChar_array = new byte[10]; int asciiChar_read = asciiChar_stream.read(asciiChar_array,0,10);
Use the getBinaryOutputStream()
method of an oracle.sql.BLOB
object to write BLOB data.
The following example reads a vector of data into a byte array, then uses the getBinaryOutputStream()
method to write an array of character data to a BLOB.
java.io.OutputStream outstream; // read data into a byte array 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); ...
Use the getCharacterOutputStream()
method or the getAsciiOutputStream()
method to write data to a CLOB. The getCharacterOutputStream()
method returns a Unicode
output stream; the getAsciiOutputStream()
method returns an ASCII output stream.
The following example reads a vector of data into a character array, then uses the getCharacterOutputStream()
method to write the array of character data to a CLOB. The getCharacterOutputStream()
method returns a java.io.Writer
instance in an oracle.sql.CLOB
object, not a java.sql.Clob
object.
java.io.Writer 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(); ...
The next example reads a vector of data into a byte array, then uses the getAsciiOutputStream()
method to write the array of ASCII data to a CLOB. Because getAsciiOutputStream()
returns an ASCII output stream, you must cast the output to a oracle.sql.CLOB
datatype.
java.io.OutputStream out; // read data into a byte array 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();
Create and populate a BLOB
or CLOB
column in a table by using SQL statements.
Create a BLOB
or CLOB
column in a table with the SQL CREATE TABLE
statement, then populate the LOB. This includes creating the LOB entry in the table, obtaining the LOB locator, creating a file handler for the data (if you are reading the data from a file), and then copying the data into the LOB.
To create a BLOB
or CLOB
column in a new table, execute the SQL CREATE TABLE
statement. The following example code creates a BLOB
column in a new table. This example assumes that you have already created your Connection
object conn
and Statement
object stmt
:
String cmd = "CREATE TABLE my_blob_table (x varchar2 (30), c blob)"; stmt.execute (cmd);
In this example, the VARCHAR2
column designates a row number, such as 1 or 2, and the BLOB
column stores the locator of the BLOB data.
This example demonstrates how to populate a BLOB
or CLOB
column by reading data from a stream. These steps assume that you have already created your Connection
object conn
and Statement
object stmt
. The table my_blob_table
is the table that was created in the previous section.
The following example writes the GIF
file john.gif
to a BLOB.
empty_blob
syntax to create the BLOB locator.
stmt.execute ("INSERT INTO my_blob_table VALUES ('row1', empty_blob())");
BLOB blob; cmd = "SELECT * FROM my_blob_table WHERE X='row1'"; ResultSet rest = stmt.executeQuery(cmd); BLOB blob = ((OracleResultSet)rset).getBLOB(2);
john.gif
file, then print the length of the file. This value will be used later to ensure that the entire file is read into the BLOB. Next, create a FileInputStream
object to read the contents of the GIF
file, and an OutputStream
object to retrieve the BLOB as a stream.
File binaryFile = new File("john.gif"); System.out.println("john.gif length = " + binaryFile.length()); FileInputStream instream = new FileInputStream(binaryFile); OutputStream outstream = blob.getBinaryOutputStream();
getBufferSize()
to retrieve the ideal buffer size (according to calculations by the JDBC driver) to use in writing to the BLOB, then create the buffer
byte array.
int size = blob.getBufferSize(); byte[] buffer = new byte[size]; int length = -1;
read()
method to read the GIF
file to the byte array buffer
, then use the write()
method to write it to the BLOB. When you finish, close the input and output streams.
while ((length = instream.read(buffer)) != -1) outstream.write(buffer, 0, length); instream.close(); outstream.close();
Once your data is in the BLOB or CLOB, you can manipulate the data. This is described in the next section, "Accessing and Manipulating BLOB and CLOB Data".
Once you have your BLOB or CLOB locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you first must select their locators from a result set or from a callable statement. "Getting and Passing BLOB and CLOB Locators" describes these techniques in detail.
After you select the locators, you can retrieve the BLOB or CLOB data. You will usually want to cast the result set to the OracleResultSet
datatype so that you can retrieve the data in oracle.sql.*
format. After retrieving the BLOB or CLOB data, you can manipulate it however you want.
This example is a continuation of the example in the previous section. It uses the SQL SELECT
statement to select the BLOB locator from the table my_blob_table
into a result set. The result of the data manipulation is to print the length of the BLOB in bytes.
// 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 // then print the blob from the array byte bytes[] = blob.getBytes(1, length); printBytes(bytes, length);
In addition to what has already been discussed in this chapter, the oracle.sql.BLOB
and CLOB
classes have a number of methods for further functionality.
The oracle.sql.BLOB
class includes the following methods:
getBinaryOutputStream()
: Returns a java.io.OutputStream
to write data to the BLOB as a stream.
getBinaryStream()
: Returns the BLOB data for this Blob
instance as a stream of bytes.
getBufferSize()
: Returns the ideal buffer size, according to calculations by the JDBC driver, to use in reading and writing BLOB data. This value is a multiple of the chunk size (see getChunkSize()
below) and is close to 32K.
getBytes()
: Reads from the BLOB data, starting at a specified point, into a supplied buffer.
getChunkSize()
: Returns the Oracle chunking size, which can be specified by the database administrator when the LOB column is first created. This value, in Oracle blocks, determines the size of the chunks of data read or written by the LOB data layer in accessing or modifying the BLOB value. Part of each chunk stores system-related information, and the rest stores LOB data. Performance is enhanced if read and write requests use some multiple of the chunk size.
length()
: Returns the length of the BLOB in bytes.
position()
: Determines the byte position in the BLOB where a given pattern begins.
putBytes()
: Writes BLOB data, starting at a specified point, from a supplied buffer.
The oracle.sql.CLOB
class includes the following methods:
getAsciiOutputStream()
: Returns a java.io.OutputStream
to write data to the CLOB as a stream.
getAsciiStream()
: Returns the CLOB value designated by the Clob
object as a stream of ASCII bytes.
getBufferSize()
: Returns the ideal buffer size, according to calculations by the JDBC driver, to use in reading and writing CLOB data. This value is a multiple of the chunk size (see getChunkSize()
below) and is close to 32K.
getCharacterOutputStream()
: Returns a java.io.Writer
to write data to the CLOB as a stream.
getCharacterStream()
: Returns the CLOB data as a stream of Unicode
characters.
getChars()
: Retrieves characters from a specified point in the CLOB data into a character array.
getChunkSize()
: Returns the Oracle chunking size, which can be specified by the database administrator when the LOB column is first created. This value, in Oracle blocks, determines the size of the chunks of data read or written by the LOB data layer in accessing or modifying the CLOB value. Part of each chunk stores system-related information and the rest stores LOB data. Performance is enhanced if you make read and write requests using some multiple of the chunk size.
getSubString()
: Retrieves a substring from a specified point in the CLOB data.
length()
: Returns the length of the CLOB in characters.
position()
: Determines the character position in the CLOB at which a given substring begins.
putChars()
: Writes characters from a character array to a specified point in the CLOB data.
putString()
: Writes a string to a specified point in the CLOB data.