This section describes how to insert BLOB values with the PreparedStatement.setBlob() method.
If you want to insert a BLOB column with a character string that comes from a java.sql.Blob object,
you can directly set the value with PreparedStatement.setBlob() method.
To test this, I wrote the following program to copy some records with BLOB values as new records back into the same table.
During the copy process, the BLOB values are also modified with some Blob object methods - The first 6 bytes are replaced with 0 values.
/**
* MySqlBlobSetBlob.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.io.*;
import java.sql.*;
public class MySqlBlobSetBlob {
public static void main(String [] args) {
Connection con = null;
try {
com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds
= new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
ds.setServerName("localhost");
ds.setPortNumber(3306);
ds.setDatabaseName("HerongDB");
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) 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");
// Modifying 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.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 performed exactly as I expected:
C:\>java -cp .;\local\lib\mysql-connector-java-5.0.7-bin.jar
MySqlBlobSetBlob
Copying record ID: 4
Copying record ID: 10
Copying record ID: 16
Record ID: 17
Subject = Copy of Test on INSERT statement
Body in HEX = 000000C8CABCCCCEB9C9CBBB...
Record ID: 18
Subject = Copy of Test of the setBytes() method
Body in HEX = 000000C8CABCCCCEB9C9CBBB...
Record ID: 19
Subject = Copy of Test of setBinaryStream() methods
Body in HEX = 0000000320B5A03B0487049A02048804AA0204BA...
