Section 8.7. BLOBs and CLOBs

8.7. BLOBs and CLOBs

As users began to increase the volume of data stored in databases, vendors introduced support for large objects (LOBs). The two varieties of LOBs, binary large objects (BLOBs) and character large objects (CLOBs), store large amounts of binary or character data, respectively. The SQL99 specification formally defined these data types.

Support for LOB types across databases varies. Some don't support them at all, and most have unique type names (BINARY, LONG RAW, and so forth). JDBC 1.0 makes programs retrieve BLOB and CLOB data using the getBinaryStream( ) or getAsciiStream( ) methods. (A third method, getUnicodeStream( ), has been deprecated in favor of the getCharacterStream( ) method, which returns a Reader.)

As of JDBC 2.0, the ResultSet interface includes getBlob( ) and getClob( ) methods, which return Blob and Clob objects, respectively. The Blob and Clob objects themselves allow access to their data via streams (the getBinaryStream( ) method of Blob and the getCharacterStream( ) method of Clob) or via direct-read methods (the getBytes( ) method of Blob and the getSubString( ) method of Clob).

To retrieve the data from a CLOB, simply retrieve the Clob object and call the getCharacterStream( ) method:

 String s; Clob clob = blobResultSet.getBlob("CLOBFIELD"); BufferedReader clobData = new BufferedReader(clob.getCharacterStream(  )); while((s = clobData.readLine(  )) != null)   System.out.println(s);

In addition, you can set Blob and Clob objects when you are working with a PreparedStatement, using the setBlob( ) and setClob( ) methods. Note that the life span of a Blob or Clob object is limited to the transaction that created it, so if you've retrieved one from a ResultSet, you need to keep that ResultSet open while you're working with it.

JDBC 3.0 extended the java.sql.Blob interface to include a setBytes( ) method to alter the BLOB's content, and the java.sql.Clob class includes a setString( ) method. When using these methods, it's up to the driver whether to update a local copy of the LOB or to directly update the copy in the database. The locatorsUpdateCopy( ) method of DatabaseMetaData will tell you which approach is used by your driver. If the method returns true, you'll need to issue a separate update statement to commit the changes to the LOB back to the database. To insert data in a new BLOB or CLOB field, first create a row with an empty LOB, select the row, retrieve the LOB, edit its contents, and write it back if necessary.

Modified Blob and Clob objects can be passed to the setBlob( ) and setClob( ) methods of PreparedStatement and to the new updateBlob( ) and updateClob( ) methods of ResultSet (for updateable result sets).

JDBC driver support for BLOB and CLOB types varies wildly. Some vendors don't support any LOB functionality at all, and others support only JDBC 2.0-level (read-only) LOBs or provide their own extensions for manipulating them. Check your driver documentation for more details.