JDBC for Oracle - Herong's Tutorial Examples - v3.13, by Herong Yang
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...
Table of Contents
JDBC (Java Database Connectivity) Introduction
Oracle Express Edition Installation on Windows
Oracle - Reference Implementation of JdbcRowSet
Oracle - JBDC CallableStatement
Oracle CLOB (Character Large Object) - TEXT
►Oracle BLOB (Binary Large Object) - BLOB
Overview of BLOB (Binary Large Object)
Create Tables with CLOB Columns
Inserting BLOB Values with SQL INSERT Statements
Inserting BLOB Values with setBytes() Method
Inserting BLOB Values with setBinaryStream() Method
Closing InputStream Too Early on setBinaryStream()
►Retrieving BLOB Values with getBytes() Method
Retrieving BLOB Values with getBinaryStream() Method
Retrieving BLOB Values with getBlob() Method
Inserting BLOB Values with setBlob() Method
Copying BLOB Values to New Rows