Blob

8.1 Blob Overview

A Blob object represents the Java programming language mapping of an SQL BLOB (Binary Large Object). An SQL BLOB is a built-in type that stores a Binary Large Object as a column value in a row of a database table. Methods in the interfaces ResultSet, CallableStatement, and PreparedStatement allow a programmer to access the SQL3 type BLOB in the same way that SQL92 built-in types are accessed. In other words, an application using the JDBC 2.0 API uses methods such as getBlob and setBlob for a BLOB value the same way it uses getInt and setInt for an INTEGER value or getString and setString for a CHAR or VARCHAR value.

In a standard implementation, a JDBC driver implements the Blob interface using the SQL type LOCATOR(BLOB) behind the scenes. A LOCATOR(BLOB) designates an SQL BLOB value residing on a database server, and operations on the locator achieve the same results as operations on the BLOB value itself. This means that a client can operate on a Blob instance without ever having to materialize the BLOB data on the client machine, which can improve performance significantly. Because the driver uses LOCATOR(BLOB) behind the scenes, its use is completely transparent to the programmer using a JDBC driver.

The standard behavior for a Blob instance is to remain valid until the transaction in which it was created is either committed or rolled back.

The Blob interface provides methods for getting the length of an SQL BLOB value, for materializing a BLOB value on the client, and for determining the position of a pattern of bytes within a BLOB value.

8.1.1 Creating a Blob Object

The following code fragment illustrates creating a Blob object, where stmt is a Statement object:

ResultSet rs = stmt.executeQuery("SELECT DATA FROM TABLE1");
rs.first();
Blob data = rs.getBlob("DATA");

The variable blob contains a logical pointer to the BLOB value that is stored in the column DATA in the first row of the result set rs. It does not contain the data in the BLOB value, but as far as JDBC methods are concerned, it is operated on as if it did.

8.1.2 Materializing Blob Data

Programmers can invoke methods in the JDBC API on a Blob object as if they were operating on the SQL BLOB it designates. However, if they want to operate on a Blob object as if it were an object in the Java programming language, they must first materialize it on the client. The Blob interface provides two methods for materializing a Blob object: getBinaryStream, which materializes the BLOB value as an input stream, and getBytes, which materializes all or part of the BLOB value as an array of bytes. The following code fragment materializes all of the data in the BLOB value designated by blob as an input stream:

java.io.InputStream in = blob.getBinaryStream();
byte b;
while ((in.read()) > -1) {
        b = in.read();
        System.out.println(b);
}
// prints out all the bytes in the BLOB value that blob designates

The next code fragment also materializes all of the data in the BLOB value designated by blob, but as an array of bytes instead of as an input stream.

long len = blob.length();
byte [] data = blob.getBytes(1, len);
for (int i = 0; i < len; i++) {
        byte b = data[i];
        System.out.println(b);
}
// prints out all the bytes in the BLOB value that blob designates

The variable data contains a copy of all of the bytes in the BLOB value that blob designates. This is true because the arguments passed to the method getBytes specify the entire BLOB value: the first argument tells it to return bytes starting with the first byte, and the second argument tells it to return the number of bytes in the length of the BLOB value. The following line of code illustrates materializing 1024 bytes starting with the 256th byte:

byte [] data = blob.getBytes(256, 1024);
byte b = data[0];
// data contains bytes 256 through 1280 in the BLOB value that blob 
// designates; b contains the 256th byte

A point to keep in mind is that because of differences in SQL and the Java programming language, the first byte in a BLOB value is at position 1, whereas the first element of an array in the Java programming language is at index 0.

8.1.3 Storing a Blob Object

To store a Blob object in the database, it is passed as a parameter to the PreparedStatement method setBlob. For example, the following code fragment stores the Blob object stats by passing it as the first input parameter to the PreparedStatement object pstmt:

Blob stats = rs.getBlob("STATS");
PreparedStatement pstmt = con.prepareStatement(
        "UPDATE SIGHTINGS SET MEAS = ? WHERE AREA = 'NE'");
pstmt.setBlob(1, stats);
pstmt.executeUpdate();

The BLOB value designated by stats is now stored in the table SIGHTINGS in column MEAS in the row where column AREA contains NE.

8.2 Blob Interface Definition

package java.sql;
public interface Blob {
        long length() throws SQLException;
        InputStream getBinaryStream() throws SQLException;
        byte[] getBytes(long pos, int length) throws SQLException;
        long position(byte [] pattern, long start) throws SQLException;
        long position(Blob pattern, long start) throws SQLException;
}

8.3 Blob Methods

InputStream getBinaryStream() throws SQLException

Materializes the BLOB value designated by this Blob object as a stream of uninterpreted bytes.
RETURNS:

an InputStream object with the data of the BLOB value designated by this Blob object

EXAMPLE:
InputStream in = blob.getBinaryStream();
// in has the data in the BLOB value that blob designates

getBytes

byte[] getBytes(long pos, int length) throws SQLException

Materializes part or all of the BLOB value that this Blob object designates as an array of bytes. The byte array contains up to length consecutive bytes starting at position pos.
PARAMETERS:

pos the ordinal position in the BLOB value
of the first byte to be extracted; the
first byte is at position 1

length the number of consecutive bytes to be copied

RETURNS:

a byte array with up to length consecutive bytes from the BLOB value pointed to by this Blob object, starting with the byte at position pos

EXAMPLE:
byte [] part = blob.getBytes(5, 100);
// part contains the fifth through 104th bytes, inclusive, as an
// array of bytes

length

long length() throws SQLException

Returns the number of bytes in the BLOB value designated by this Blob object.
RETURNS:

the length of the BLOB value designated by this Blob object, in bytes

EXAMPLE:
Blob blob = rs.getBlob(2);
long len = blob.length(); 
// len contains the number of bytes in the BLOB value designated by
// blob (the BLOB value in the second column of the current row of the
// ResultSet object rs)

position

long position(byte [] pattern, long start) throws SQLException

Determines the position at which the byte array pattern begins within the BLOB value that this Blob object represents. The search for pattern begins at position start.
PARAMETERS:

pattern the byte array for which to search

start the position in the BLOB value at which to begin searching; the first byte is at position 1

RETURNS:

the position in the BLOB value at which the byte array pattern begins, which will be start or larger if the search, starting at position start, is successful; -1 otherwise

EXAMPLE:
byte [] part = blob.getBytes(5, 100);
long beginning = blob.position(part, 1024);
// if part is contained in the BLOB value that blob designates, from
// position 1024 on, beginning will contain the position at which
// part begins

position

long position(Blob pattern, long start) throws SQLException

Determines the byte position in the BLOB value designated by this Blob object at which pattern begins. The search begins at position start.
PARAMETERS:

pattern the Blob object designating the BLOB value for which to search

start the position in the BLOB value at which to begin searching; the first byte is at position 1

RETURNS:

the position at which the Blob object pattern begins, which will be start or larger if the search, starting at position start, is successful; -1 otherwise

EXAMPLE:
Blob blob2 = rs.getBlob(4);
long beginning = blob1.position(blob2, 512);
// if the BLOB value designated by blob2 is contained in the BLOB
// value designated by blob1, starting at position 512 or later, 
// beginning will contain the position at which the BLOB value 
// designated by blob2 begins

Java Technology

pos		the ordinal position in the `BLOB` value of the first `byte` to be extracted; the first `byte` is at position `1`
length		the number of consecutive bytes to be copied

pattern		the `byte` array for which to search
start		the position in the `BLOB` value at which to begin searching; the first `byte` is at position `1`

pattern		the `Blob` object designating the `BLOB` value for which to search
start		the position in the `BLOB` value at which to begin searching; the first `byte` is at position `1`