Large ObjectsBLOBs and CLOBs

Large Objects BLOBs and CLOBs

The SQL types we've used so far in this and previous chapters NUMBER, VARCHAR2, and DATE correspond more or less to basic Java types, such as int, long double, Date, and String. To allow maximum flexibility, SQL also supports untyped objects, which unlike the other SQL types are virtually unlimited (up to 4 gigabytes) in size. These are called large objects (LOBs) and come in two varieties, character large objects (CLOBs) and binary large objects (BLOBs). The latter are especially well named because they are, in effect, just blobs to the database undifferentiated stuff with no meaning, as far as the database is concerned.

One way to think of the difference between BLOBs and CLOBs is by analogy to the difference between text files and binary files in some operating systems. Text files, like CLOBs, are associated with a character set and are plain text, without formatting information; typical examples are HTML, XML, and plain text documents. Binary files, like BLOBs, can contain anything at all; typical examples are: executable files, image files, sound files, and documents produced by word processing programs.

The comparison between LOBs and operating system files is not entirely spurious. LOBs are most commonly used to store and retrieve operating system files in a database. They are not limited to this, of course; data generated in memory (such as byte arrays or strings, for example) could also be stored in a LOB. Also, serialized Java objects could be stored in a BLOB which presents interesting possibilities for roll-your-own object persistence.

An important thing to note is that the LOB data (except for small objects) is not saved in the database table itself. Instead, a locator is stored in the table. This locator introduces a layer of indirection that can make LOBs somewhat complicated to work with.

Reading and Writing BLOBs and CLOBs

There are two ways to write LOB data to the database. The first, and most straightforward, is to use the PreparedStatement's setBinaryStream() or the setCharacterStream() methods to insert the data. This works only with the OCI JDBC driver in Oracle versions 8.1.6 and greater. The other way is to obtain a binary or character stream from the LOB locator. The following examples use BLOBs, but they are identical to using CLOBs.

Writing a BLOB Using the PreparedStatement setBinaryStream() Method

Assuming that we are using the OCI driver and have a valid Connection object, conn, we can insert an MP3 file as a BLOB in the database like this:

 // write blob using PreparedStatement setBinaryStream PreparedStatement ps = conn.prepareStatement(         "INSERT INTO MP3S (ID, FILENAME, MP3) " +         "VALUES(?, ? , ?)" ); ps.setLong(1, 199); ps.setString(2, "Elevation.mp3"); File file = new File ("C:\\MyTunes\\Television\\Elevation.mp3"); FileInputStream fileStream = new FileInputStream(file); ps.setBinaryStream(3, fileStream, (int) file.length()); ps.executeUpdate(); ps.close(); rs.close(); fileStream.close(); 

The setBinaryStream() method conveniently takes an input stream and its length as its parameters and takes care of the details of reading the file for us.

Writing a BLOB Using a Locator

If we are using the thin driver, the PreparedStatement class does not support the method setBinaryStream() above. Instead, we need to obtain the output stream for the BLOB from the BLOB locator. This means that we must create an empty BLOB first before we can write data to it. We cannot create a BLOB in Java; the database must create the BLOB for us. To create a BLOB, we insert a row using the function EMPTY_BLOB() to provide the BLOB value in the insert value list (in addition to any other non-BLOB information).

 // write blob using locator //  - step 1: insert with empty BLOB PreparedStatement ps = conn.prepareStatement(         "INSERT INTO MP3S (ID, FILENAME, MP3) " +         "VALUES(?, ? , EMPTY_BLOB())" ); ps.setLong(1, 199); File file = new File("C:\\MyTunes\\Television\\Elevation.mp3"); ps.setString(2, "Elevation.mp3"); ps.executeUpdate(); ps.close(); 

Next, we query the table to obtain the locator created by EMPTY_ BLOB(), use the locator to obtain the binary stream, and write the data to the stream. These steps must all be part of a single transaction. To do this, we must make sure auto-commit is false and lock the row by using the SQL command SELECT…FOR UPDATE for the query. This is the code required to obtain the binary stream:

 // Get BLOB locator conn.setAutoCommit(false); ps = conn.prepareStatement("SELECT MP3 FROM MP3S "                            + "WHERE ID = ? FOR UPDATE"); ps.setLong(1, 199); rs = ps.executeQuery(); BLOB blob = null; if(rs.next()) {    blob = ((OracleResultSet)rs).getBLOB(1); } // Standard JDBC Blob method not supported by Oracle API: // OutputStream outStream = blob.setBinaryStream(); // This is Oracle's version: OutputStream outStream = blob.getBinaryOutputStream(0); 

Note that there are a few Oracle-specific features here. First, we need to use Oracle's BLOB, rather than the JDBC standard Blob. ResultSet is an interface defined by JDBC, but the actual implementation is an OracleResultSet, so we can cast rs to OracleResultSet to call the Oracle-specific getBLOB() method (rather than the JDBC standard getBlob() method), which returns a BLOB instead of a Blob. Finally, we call the BLOB's getBinaryOutputStream() method to obtain the stream.

Now we are finally prepared to write to the stream. In the previous example, the PreparedStatement's setBinaryStream() method took care of reading from one stream and writing to the other for us. Here, we must do this job ourselves, and to do this, we need a temporary buffer. This is the code for opening the file stream, reading from it, and writing to the output stream, a buffer's worth of data at a time:

 // use buffer to write from file to db int size = blob.getBufferSize(); byte [] buffer = new byte[size]; int length = -1; while ((length = inStream.read(buffer)) != -1) {   outStream.write(buffer, 0, length); } inStream.close(); outStream.close(); conn.commit(); ps.close(); rs.close(); 

This, using the BLOB locator, is obviously a great deal more trouble than writing the BLOB directly as a binary stream, but if you write a general-purpose BLOB class, you'll have to do it only once.

Reading BLOBs and CLOBs

As was the case with writing BLOBs and CLOBs, there are two ways to read them. The most straightforward way is to use the PreparedStatement's getBinaryStream() or getCharacterStream() classes. The more complicated way is to use the getCLOB() or getBLOB() methods to obtain the locator and use that to obtain an input stream. Fortunately, support for the first method is consistent in the JDBC drivers and that is the method used in the following example. As in the previous section, BLOBs are used in the examples but the use of CLOBs is identical:

 // Read BLOB into file String sql = "SELECT FILENAME, MP3 FROM MP3S " +              "WHERE ID = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setLong(1, id); ResultSet rs = ps.executeQuery(); if(rs.next()) {     filename = "C:\\TEMP\\" + rs.getString(1);     File file = new File(filename);     FileOutputStream outStream = new FileOutputStream (file);     InputStream inStream = rs.getBinaryStream(2);     // set up buffer for transfer     byte [] buffer = new byte[BUFFERSIZE]; //BUFFERSIZE==2048     int length = -1;     // Read and write, buffer by buffer     while((length = inStream.read(buffer)) != -1)     {        outStream.write(buffer, 0, length);     }     inStream.close();     outStream.close(); } rs.close(); ps.close(); 

Notice that we need to set up a buffer to transfer data from the input stream to the output stream. Had we used the BLOB locator to obtain the input stream (as in the previous example), we could have called the BLOB's getBufferSize() method to obtain the optimal size for the buffer. Here, we're using an arbitrarily sized 2K buffer this could probably be adjusted for better performance. On my system, getBufferSize() returns 32288.