Oracle8i JDBC Developer's Guide and Reference Release 8.1.5 A64685-01 |
|
This section has these subsections:
LOBs can be either internal or external. Internal LOBs, as their name suggests, are stored inside database tablespaces in a way that optimizes space and provides efficient access. The JDBC drivers provide support for two types of internal LOBs: BLOB
s (unstructured binary data) and CLOB
s (single-byte character data). BLOB
and CLOB
data is accessed and referenced by using a locator which is stored in the database table and points to the BLOB
or CLOB
data.
External LOBs (BFILES
) are large binary data objects stored in operating system files outside of database tablespaces. These files use reference semantics. They may also be located on tertiary storage devices such as hard disks, CD-ROMs, PhotoCDs and DVDs. Like BLOB
s and CLOB
s, a BFILE
is accessed and referenced by a locator which is stored in the database table and points to the BFILE
data.
This section describes how you use JDBC and the oracle.sql.*
classes to work with LOBs. To work with LOB data, you must first obtain its locator from the table. Then, you can read data from or write data to the LOB and perform various types of data manipulation. This section also describes how to create and populate a LOB column in a table.
The JDBC drivers support these oracle.sql.*
classes for BLOB
s, CLOB
s, and BFILE
s:
The oracle.sql.BLOB
and CLOB
classes implement the oracle.jdbc2.Blob
and Clob
interfaces, respectively. In contrast, BFILE
s have no oracle.jdbc2
interface.
Instances of these classes contain only the locators for these datatypes, not the data. After accessing the locators, you must perform some additional steps to access the data. These steps are described in "Reading and Writing BLOB and CLOB Data" and "Reading BFILE Data".
Given a standard JDBC result set or callable statement object that includes BLOB
or CLOB
locators, you can access the locators by using the standard ResultSet
.getObject()
method. This method returns an oracle.sql.BLOB
object or oracle.sql.CLOB
object, as applicable (but note that it returns the BLOB
or CLOB
into a variable of type oracle.jdbc2.Blob
or oracle.jdbc2.Clob
).
You can also access the locators by casting your result set to OracleResultSet
or your callable statement to OracleCallableStatement
and using the getOracleObject()
, getBLOB()
, or getCLOB()
method, as appropriate.
In the OracleResultSet
and OracleCallableStatement
classes, getBlob()
returns oracle.jdbc2.Blob
, and getBLOB()
returns oracle.sql.BLOB
. Similarly, getCLOB()
returns oracle.jdbc2.CLOB
and getClob()
returns oracle.sql.Clob
.
Notes:
|
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. oracle.jdbc2.Blob blob = (oracle.jdbc2.Blob)rs.getObject(1); oracle.jdbc2.Clob clob = (oracle.jdbc2.Clob)rs.getObject(2); [...process...] }
The output is cast to oracle.jdbc2.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. In the case of a callable statement, register the output parameter as OracleTypes.BLOB
or OracleTypes.CLOB
.
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 follows:
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call func()}") ocs.registerOutParameter(1, OracleTypes.CLOB); ocs.executeQuery() oracle.sql.CLOB clob = ocs.getCLOB(1);
To pass a LOB locator to a prepared statement or callable statement (to update a LOB locator in the database, for example), you can use the generic setObject()
method, or you can cast the statement to OraclePreparedStatement
or OracleCallableStatement
and use the setOracleObject()
, setBLOB()
, or setCLOB()
method, as appropriate. These methods take the parameter index and a BLOB
object or CLOB
object as input.
If you have an OraclePreparedStatement
ops
where its first parameter is a BLOB
named my_blob
, then input the BLOB
to the prepared statement as follows:
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement("INSERT INTO blob_table VALUES(?)");ops.setBLOB(1, my_blob); ops.execute();
If you have an OracleCallableStatement
ocs
where its first parameter is a CLOB
, then input the CLOB
to the callable statement as follows:
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? := call func()}") ocs.setClob(1, my_clob) ocs.execute();
The SQL SELECT
statement queries for LOB locators. Once you have the locator, you can read and write the LOB data from JDBC. LOB data is materialized as a Java 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:
BLOB
, use the 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.
BLOB
, use the 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.
CLOB
, use the 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
.
CLOB
, use the 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 close()
method 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
object in an oracle.sql.CLOB
, not an oracle.jdbc2.Clob
.
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();
You create and populate a BLOB
or CLOB
column in a table by using SQL statements.
You create a BLOB
or CLOB
column in a table with the SQL CREATE TABLE
statement. Then, you 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 one
or two
, 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
.
BLOB
entry in the table. Use the empty_blob
syntax to create the BLOB
locator.
stmt.execute ("insert into my_blob_table values ('row1', empty_blob()");
BLOB
locator from the table.
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();
getChunkSize()
to determine the ideal chunk size to write to the BLOB
, then create the buffer
byte array.
int chunk = blob.getChunkSize(); byte[] buffer = new byte[chunk]; 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 following 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 BLOB and CLOB Locators" describes these techniques in detail.
After you select the locators, you can get 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 getting 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.getlength(); // 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(0, length); printBytes(bytes, length);
Given a standard JDBC result set or callable statement object that includes BFILE
locators, you can access the locators by using the standard ResultSet.getObject()
method. This method returns an oracle.sql.BFILE
object.
You can also access the locators by casting your result set to OracleResultSet
or your callable statement to OracleCallableStatement
and using the getOracleObject()
or getBFILE()
method.
Notes:
|
Assume that the database has a table called bfile_table
with a single column for the BFILE
locator bfile_col
. This example assumes that you have already created your Statement
object stmt
.
Select the BFILE
locator into a standard result set. If you cast the result set to an OracleResultSet
, you can use getBFILE()
to get the BFILE
data:
// Select the BFILE locator into a result set ResultSet rs = stmt.executeQuery("SELECT bfile_col FROM bfile_table"); while (rs.next()) { oracle.sql.BFILE my_bfile = ((OracleResultSet)rs).getBFILE(1); };
Note that as an alternative, you can use getObject()
to return the BFILE
locator. In this case, since getObject()
returns a java.lang.Object
, cast the results to BFILE
. For example:
oracle.sql.BFILE my_bfile = (BFILE)rs.getObject(1);
Assume you have an OracleCallableStatement
ocs
that calls a function func
that has a BFILE
output parameter. The following code example sets up the callable statement, registers the output parameter as OracleTypes.BFILE
, executes the statement, and retrieves the BFILE
locator:
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call func()}") ocs.registerOutParameter(1, OracleTypes.BFILE); ocs.execute(); oracle.sql.BFILE bfile = ocs.getBFILE(1);
To pass a BFILE
locator to a prepared statement or callable statement (to update a BFILE
locator, for example), you can use the generic setObject()
method or you can cast the statement to OraclePreparedStatement
or OracleCallableStatement
and use the setOracleObject()
or setBFILE()
method. These methods take the parameter index and an oracle.sql.BFILE
object as input.
You want to insert a BFILE
locator into a table. Assume that you have an OraclePreparedStatement
ops
where the first parameter is a string (to designate a row number), its second parameter is a BFILE
, and you have a valid oracle.sql.BFILE
object (bfile
). Input the BFILE
to the prepared statement as follows:
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement("INSERT INTO my_bfile_table VALUES (?,?)");ops.setString(1,"one"); ops.setBFILE(2, bfile); ops.execute();
Passing a BFILE
locator to a callable statement is similar to passing it to a prepared statement. In this case, the BFILE
locator is passed to the myGetFileLength()
procedure, which returns the BFILE
length as a numeric value.
OracleCallableStatement cstmt = (OracleCallableStatement) conn.prepareCall ("begin ? := myGetFileLength (?); end;"); try { cstmt.registerOutParameter (1, Types.NUMERIC); cstmt.setBFILE (2, bfile); cstmt.execute (); return cstmt.getLong (1); } finally { cstmt.close (); } }
To read BFILE
data, you must first get the BFILE
locator. You can get the locator from either a callable statement or a result set. "Getting BFILE Locators" describes this.
Once you obtain the locator, there are a number of methods that you can perform on the BFILE
without opening it. For example, you can use the oracle.sql.BFILE
methods fileExists()
and isFileOpen()
to determine whether the BFILE
exists and if it is open. However, if you want to read and manipulate the data, you must open the BFILE
. BFILE
data is materialized as a Java stream. Operate on BFILE
s from JDBC as follows:
BFILE
, use the getBinaryStream()
method of an oracle.sql.BFILE
object to retrieve the entire file as an input stream. This returns a java.io.InputStream
class.
As with any InputStream
object, use one of the overloaded read()
methods to read the file data and use the close()
method when you finish.
The following example uses the getBinaryStream()
method of an oracle.sql.BFILE
object to read BFILE
data into a byte stream and then read the byte stream into a byte array. The example assumes that the BFILE
has already been opened.
// Read BFILE data from a BFILE locator Inputstream in = bfile.getBinaryStream(); byte[] byte_array = new byte{10}; int byte_read = in.read(byte_array);
You create a BFILE
column in a table with SQL statements and specify the location where the BFILE
resides. The examples below assume that you have already created your Connection
object conn
and Statement
object stmt
.
To work with BFILE
data, create a BFILE
column in a table and specify the location of the BFILE
. To specify the location of the BFILE
, use the SQL CREATE DIRECTORY
...AS
statement to specify an alias for the directory where the BFILE
resides. Then execute the statement. In this example, the directory alias is test_dir
and the location where the BFILE
resides is /home/work
.
String cmd; cmd = "CREATE DIRECTORY test_dir AS '/home/work'"; stmt.execute (cmd);
Use the SQL CREATE
TABLE
statement to create a table containing a BFILE
column, then execute the statement. In this example, the name of the table is my_bfile_table
.
// Create a table containing a BFILE field cmd = "CREATE TABLE my_bfile_table (x varchar2 (30), b bfile)"; stmt.execute (cmd);
In this example, the VARCHAR2
column designates a row number and the bfile
column stores the locator of the BFILE
data.
Use the SQL INSERT INTO
...VALUES
statement to populate the VARCHAR2
and bfile
fields, then execute the statement. The bfile
column is populated with the locator to the BFILE
data. To populate the BFILE
column, use the bfilename
keyword to specify the directory alias and the name of the BFILE
file.
cmd ="INSERT INTO my_bfile_table VALUES ('one', bfilename(test_dir, 'file1.data'))"; stmt.execute (cmd); cmd ="INSERT INTO my_bfile_table VALUES ('two', bfilename(test_dir, 'jdbcTest.data'))"; stmt.execute (cmd);
In this example, the name of the directory alias is test_dir
. The locator of the BFILE
file1.data
is loaded into the bfile
column on row one
, and the locator of the BFILE
jdbcTest.data
is loaded into the bfile
column on row two
.
As an alternative, you might want to create the row for the row number and BFILE
locator now, but wait until later to insert the locator. In this case, insert the row number into the table, and null
as a place holder for the BFILE
locator.
cmd ="INSERT INTO my_bfile_table VALUES ('three', null)"; stmt.execute(cmd);
Here, three
is inserted into the row number column and null
is inserted as the place holder. Later in your program, insert the BFILE
locator into the table by using a prepared statement.
First get a valid BFILE
locator into the bfile
object:
rs = stmt.executeQuery("SELECT b FROM my_bfile_table WHERE x='two'"); rs.next() oracle.sql.BFILE bfile = ((OracleResultSet)rs).getBFILE(2);
Then, create your prepared statement. Note that because this example uses the setBFILE()
method to identify the BFILE
, the prepared statement must be cast to an OraclePreparedStatement
:
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement(INSERT ? INTO my_bfile_table) WHERE (x = 'three'); ops.setBFILE(2, bfile); ops.execute();
Now row two
and row three
contain the same BFILE
.
Once you have the BFILE
locators available in a table, you can access and manipulate the BFILE
data. The next section, "Accessing and Manipulating BFILE Data", describes this.
Once you have the BFILE
locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you must first select its locator from a result set or a callable statement.
The following example gets the locator of the BFILE
from row two
of a table into a result set. The result set is cast to an OracleResultSet
so that oracle.sql.*
methods can be used on it. Several of the methods applied to the BFILE
, such as getDirAlias()
and getName()
, do not require you to open the BFILE
. Methods that manipulate the BFILE
data, such as reading, getting the length, and displaying, do require you to open the BFILE
.
When you finish manipulating the BFILE
data, you must close the BFILE
. For a complete BFILE
example, see "BFILE Sample".
// select the bfile locator cmd = "SELECT * FROM my_bfile_table WHERE x = 'two'"; rset = stmt.executeQuery (cmd); if (rset.next ()) { BFILE bfile = ((OracleResultSet)rset).getBFILE (2); // for these methods, you do not have to open the bfile println("getDirAlias() = " + bfile.getDirAlias()); println("getName() = " + bfile.getName()); println("fileExists() = " + bfile.fileExists()); println("isFileOpen() = " + bfile.isFileOpen()); // now open the bfile to get the data bfile.openFile(); // get the BFILE data as a binary stream InputStream in = bfile.getBinaryStream(); int length ; // read the bfile data in 6-byte chunks byte[] buf = new byte[6]; while ((length = in.read(buf)) != -1) { // append and display the bfile data in 6-byte chunks StringBuffer sb = new StringBuffer(length); for (int i=0; i<length; i++) sb.append( (char)buf[i] ); println(sb.toString()); } // we are done working with the input stream. Close it. in.close(); // we are done working with the BFILE. Close it. bfile.closeFile();