Retrieving BLOB Values with getBytes() Method

JDBC for Oracle - Herong's Tutorial Examples

∟Oracle BLOB (Binary Large Object) - BLOB

∟Retrieving BLOB Values with getBytes() Method

This section describes how to retrieve BLOB values with the ResultSet.getBytes() method.

The simplest way to retrieve the character string value from a BLOB column is to use the getBytes() method on the ResultSet object. Here is short example program:

/* OracleBlobGetBytes.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.io.*;
import java.sql.*;
public class OracleBlobGetBytes {
  public static void main(String [] args) {
    Connection con = null;
    try {
      oracle.jdbc.pool.OracleDataSource ds
        = new oracle.jdbc.pool.OracleDataSource();
      ds.setDriverType("thin");
      ds.setServerName("localhost");
      ds.setPortNumber(1521);
      ds.setDatabaseName("XE");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      con = ds.getConnection();

// Retrieving BLOB value with getBytes()
      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"));
        byte[] body = res.getBytes("Body");
        String bodyHex = bytesToHex(body, 32);
        System.out.println("   Body in HEX = "+bodyHex+"...");
      }
      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();
  }
}

bytesToHex() method is used to convert a byte array to a Hex string. The output of the program confirms that CLOB values can be retrieved with getBytes() method on the ResultSet object:

herong> java -cp .;ojdbc11.jar OracleBlobGetBytes

Record ID: 2
   Subject = Test of the setBytes() method
   Body in HEX = C9CBBBCCCEB9C8CABCCCCEB9C9CBBB...
Record ID: 3
   Subject = Test of setBinaryStream() methods
   Body in HEX = CAFEBABE000340ABA0370447045A020448046A020478...
Record ID: 4
   Subject = Error test of setBinaryStream() method
   Body in HEX = CAFEBABE000340B2A03A0477048A020478049A0204A8...