This section describes how to retrieve BLOB values with the ResultSet.getBlob() method.
If you like to work with java.sql.Blob objects, you can retrieve BLOB values with the getBlob() method on ResultSet objects.
The Blob object offers some interesting methods:
length() - Returns the number of bytes in the Blob object. The return value has a type of "long".
You may need to convert it to "int" to be used in other motheds.
getBytes(long pos, int length) - Returns a substring of characters from the Blob object with a specified starting position and length.
Note the start position is "long" value, but the length is "int" value.
getBinaryStream() - Returns a InputStream object from the Blob object so that you can read the content as a stream.
free() - Releases the resources that the Blob object holds. This was added in JDBC 4.0 (Java 1.6).
Here is my test program on getBlob() method:
/**
* SqlServerBlobGetBlob.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.io.*;
import java.sql.*;
public class SqlServerBlobGetBlob {
public static void main(String [] args) {
Connection con = null;
try {
com.microsoft.sqlserver.jdbc.SQLServerDataSource ds
= new com.microsoft.sqlserver.jdbc.SQLServerDataSource();
ds.setServerName("localhost");
ds.setPortNumber(1269);
ds.setDatabaseName("AdventureWorksLT");
ds.setUser("Herong");
ds.setPassword("TopSecret");
con = ds.getConnection();
// Retrieving BLOB value with getBlob()
Statement sta = con.createStatement();
ResultSet res = sta.executeQuery("SELECT * FROM Image");
int i = 0;
while (res.next() && i<3) {
i++;
System.out.println("Record ID: "+res.getInt("ID"));
System.out.println(" Subject = "+res.getString("Subject"));
Blob bodyOut = res.getBlob("Body");
int length = (int) bodyOut.length();
System.out.println(" Body Size = "+length);
byte[] body = bodyOut.getBytes(1, length);
String bodyHex = bytesToHex(body, 32);
System.out.println(" Body in HEX = "+bodyHex+"...");
// bodyOut.free(); // new in JDBC 4.0
}
res.close();
sta.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
e.printStackTrace();
}
}
public static String bytesToHex(byte[] bytes, int max) {
StringBuffer buffer = new StringBuffer();
for (int i=0; i<bytes.length && i<max; i++) {
buffer.append(Integer.toHexString(bytes[i] & 0xFF));
}
return buffer.toString().toUpperCase();
}
}
The output confirms that the getBlob() method and Blob objects are not hard to use:
C:\>java -cp .;\local\lib\sqljdbc.jar SqlServerBlobGetBlob
Record ID: 1
Subject = Test on INSERT statement
Body Size = 15
Body in HEX = C9CBBBCCCEB9C8CABCCCCEB9C9CBBB...
Record ID: 2
Subject = Test of the setBytes() method
Body Size = 15
Body in HEX = C9CBBBCCCEB9C8CABCCCCEB9C9CBBB...
Record ID: 4
Subject = Test of setBinaryStream() methods
Body Size = 2663
Body in HEX = CAFEBABE000320B5A03B0487049A02048804AA0204BA...