This section describes how to copy BLOB values to new rows with getBlob() and setBlob() methods.
The previous test shows that we can not update a Blob object returned from the getBlob() method.
But we can use it without any changes with the PreparedStatement.setBlob() method to copy it to a new row.
Here is a test program:
/**
* OracleBlobSetBlobRevised.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.io.*;
import java.sql.*;
public class OracleBlobSetBlobRevised {
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();
// Deleting records for re-testing
Statement sta = con.createStatement();
sta.executeUpdate(
"DELETE FROM Image WHERE Subject LIKE 'Copy of %'");
// Creating a PreparedStatement for inserting new records
PreparedStatement ps = con.prepareStatement(
"INSERT INTO Image (Subject, Body, ID) VALUES (?,?,?)");
// Looping though the first 3 records
ResultSet res = sta.executeQuery(
"SELECT * FROM Image ORDER BY ID");
int i = 0;
while (res.next() && i<3) {
i++;
System.out.println("Copying record ID: "+res.getInt("ID"));
String subject = res.getString("Subject");
Blob body = res.getBlob("Body");
// Oracle does not allow to modify the Blob object
// byte[] chuck = {(byte)0x00, (byte)0x00, (byte)0x00,
// (byte)0x00, (byte)0x00, (byte)0x00};
// body.setBytes(1,chuck);
// Inserting a new record with setBlob()
ps.setString(1, "Copy of "+subject);
ps.setBlob(2,body);
ps.setInt(3,res.getInt("ID")+100);
ps.executeUpdate();
}
ps.close();
res.close();
// Checking the new records
res = sta.executeQuery(
"SELECT * FROM Image WHERE Subject LIKE 'Copy of %'");
while (res.next()) {
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) {
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 program worked correctly:
C:\>java -cp .;\local\lib\ojdbc14.jar OracleBlobSetBlobRevised
Copying record ID: 1
Copying record ID: 2
Copying record ID: 3
Record ID: 101
Subject = Copy of Test on INSERT statement
Body in HEX = C9CBBBCCCEB9C8CABCCCCEB9C9CBBB...
Record ID: 102
Subject = Copy of Test of the setBytes() method
Body in HEX = C9CBBBCCCEB9C8CABCCCCEB9C9CBBB...
Record ID: 103
Subject = Copy of Test of setBinaryStream() methods
Body in HEX = CAFEBABE000320BAA03D04A704BA0204A804CA0204D8...
