Retrieving BLOB Values with getBlob() Method

JDBC for SQL Server - Herong's Tutorial Examples

∟SQL Server BLOB (Binary Large Object) - BLOB

∟Retrieving BLOB Values with getBlob() Method

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 methods.
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) HerongYang.com. 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(60782);
      ds.setInstanceName("SQLEXPRESS");
      ds.setDatabaseName("AdventureWorks2019");
      ds.setUser("Herong");
      ds.setPassword("T0pSecret");
      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) {
      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:

herong> java -cp .;mssql-jdbc-9.4.1.jre16.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...