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.
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.
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
.
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
.
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; }
InputStream getBinaryStream() throws SQLException
Materializes theBLOB
value designated by thisBlob
object as a stream of uninterpreted bytes.RETURNS:
an
InputStream
object with the data of theBLOB
value designated by thisBlob
objectInputStream in = blob.getBinaryStream(); // in has the data in the BLOB value that blob designates
byte[] getBytes(long pos,
int length) throws SQLException
Materializes part or all of theBLOB
value that thisBlob
object designates as an array of bytes. The byte array contains up tolength
consecutive bytes starting at positionpos
.PARAMETERS:
pos the ordinal position in the BLOB
value
of the firstbyte
to be extracted; the
firstbyte
is at position1
length the number of consecutive bytes to be copied RETURNS:
a
byte
array with up tolength
consecutive bytes from theBLOB
value pointed to by thisBlob
object, starting with thebyte
at positionpos
byte [] part = blob.getBytes(5, 100); // part contains the fifth through 104th bytes, inclusive, as an // array of bytes
long length() throws SQLException
Returns the number of bytes in theBLOB
value designated by thisBlob
object.the length of the
BLOB
value designated by thisBlob
object, in bytesBlob 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)
long position(byte [] pattern,
long start) throws SQLException
Determines the position at which thebyte
arraypattern
begins within theBLOB
value that thisBlob
object represents. The search forpattern
begins at positionstart
.PARAMETERS:
pattern the byte
array for which to searchstart the position in the BLOB
value at which to begin searching; the firstbyte
is at position1
the position in the
BLOB
value at which thebyte
arraypattern
begins, which will bestart
or larger if the search, starting at positionstart
, is successful;-1
otherwisebyte [] 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
long position(Blob pattern,
long start) throws SQLException
Determines thebyte
position in theBLOB
value designated by thisBlob
object at whichpattern
begins. The search begins at positionstart
.
pattern the Blob
object designating theBLOB
value for which to searchstart the position in the BLOB
value at which to begin searching; the firstbyte
is at position1
the position at which the
Blob
objectpattern
begins, which will bestart
or larger if the search, starting at positionstart
, is successful;-1
otherwiseBlob 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