Get Bolder with LOBs Manipulation in Java/JDBC

The Oracle database supports very large CLOB/BLOB columns/data however, the average Java/JDBC applications manipulates mostly medium size LOBs of tens/hundreds Kbytes in size.

WTF with LOB Programming in Java/JDBC

If you have ever dealt with LOBs in Java/JDBC applications, you know that it is not always straightforward, and sometime difficult to program cleanly. Let's take a closer look at the currently available approaches with Orace JDBC.

3 Ways to Slice and Dice LOBs

As of 10g Release 2, Oracle JDBC furnishes 3 ways of accessing and manipulating LOBs including:Oracle's LOB methods, optimized standard LOB methods, and LONG Streaming. Which one to use and when?In this article, i want to shed some light so as to make the reader more comfortable, bolder, when using CLOBs, BLOBs, and BFILE in Java/JDBC applications.

Oracle LOB Approach
LOB manipulation is done in two steps

Step#1: Manipulate the LOB Locator

Create or Retrieve, Insert, and Update the LOB locator using createTemporary, getEmptyCLOB, getEmptyBLOB methods. setBlob, setBLOB, setClob, setCLOB, getBlob, getBLOB, getClob, updateBlob, updateClob, setBLOB, setBLOBATName, setBFILE, updateBLOB, …, and updateCLOB methods.

ResultSet rs = stmt.executeQuery(“SELECT blobcol from XobTab”;
...
BLOB BlobLoc = ((OracleResultSet)rs).getBLOB(1);

Step#2: Manipulate the LOB Content

Write LOB content using getString, getSubString, and getBytes, setStrings, and putBytes methods.

tempLob.putBytes (1, bytarr);
PreparedStatement pstmt = conn.prepareStatement ( "insert into XobTab values (?)”;
((OraclePreparedStatement)ps).setBLOB (1, tempLob);

Read the LOB contents and Stream the content out, using getBinaryStream, get-String, getCharacterStream, and getAsciiStream.

byte[] bytarr = new byte[20]; // 20 bytes chunk
BLOB LOBLoc = ps.getBLOB (1);
InputStream is = LOBLoc.getBinaryStream();
while ((length = stream.read(bytarr)) != -1)
{
// process the chunk
bytarr = new byte[10];
}
is.close();

In pre-10g releases, JDBC-Thin used the PL/SQL DBMS_LOB package for LOB operations; in 10g, it uses a more nativead faster protocol however, certain operations such as hasPattern and isSubLob are still done via the dbms_lob package.

Standard and Optimized LOB Approach

For LOBs of size less than 2 gigabytes, LOB manipulation has been simplified especially in 10g Release 2) by removing the need to explicitly manage the locator. The standard methods getBytes, getBinaryStream, getString, getCharacterStream, getAsciiStream setBytes, setBinaryStream, setString, setCharacterStream, and setAsciiStream have been extended to take BLOB, CLOB and BFILE columns as parameters. This approach simplifies LOB programming; under the covers, the driver does the right thing (i.e., taking care of the locator, etc).

Example: Strings or byte arrays can be stored/read into/from the database, by simply using setString or setBytes, getString or getBytes.

LONG Streaming Approach

LONG streaming is furnished to help faster retrieval of data from a LOB without all the LOB overhead (i.e., retrieving the lob locators and going through the lob protocol), which can be very costly for small data. All you need to do is define the column as a LONG, then the driver takes care of LOB to LONG conversion and vice versa. This technique is faster than the locator approach (up to an order ofmagnitude) however, it is not as flexible as the traditional or optimized LOB manipulation and has the following restrictions: LONG streaming always starts at the beginning of the LOB (i.e., you cannot position or start anywhere); you may read as much data as you wish but only a single pass is allowed (i.e., you cannot read the stream twice).
Using this approach, CLOBs can be declared as LONGVARCHAR using defineColumnType. and streamed using getAsciiStream by redefining it a as
(OracleStatement)stmt.defineColumnType(,Types.LONGVARCHAR);

Similarly BLOBs columns can be declared as LONGVARBINARY using defineColumnType then streamed using getBinaryStream
(OracleStatement)stmt.defineColumnType(, Types.LONGVARBINARY);

Example:
// Pretend that these columns are of LONG and LONG RAW types
(OracleStatement) stmt.defineColumnType(1,Types.LONGVARBINARY);
(OracleStatement)stmt.defineColumnType(2,Types.LONGVARCHAR);
...
ResultSet rs = stmt.executeQuery(“select LOBCOL from XOBTab”);
// Retrieving a Clob with getString");
ocrs.setCommand ("SELECT lobcol FROM XOBTab");
ocrs.execute ();
while (ocrs.next ())
{
bytarr = stmt.getString (1));
}
ocrs.close ();

// Retrieving a Blob with getBytes");
ocrs.execute ();
while (ocrs.next ())
{
ocrs.getBytes (2).length);
}

Get More

This article, excerpted from my book, summarizes the various approaches to LOB manipulation using Oracle JDBC drivers. You can find more details and code snippets in either the Oracle JDBC 10g R2 doc or in chapter 8 of my book.