Blob in JDBC

Posted in: BLOB,JDBC
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 aCHAR 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.


Creating a Blob ObjectThe 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 theBLOB value, but as far as JDBC methods are concerned, it is operated on as if it did.

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();

Blob Methods:

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;

Examples:
1. Insert a BLOB data into Database

2. Read BLOBs data from Database