This section describes how to insert BLOB values as normal strings using INSERT statements.
The simplest way to insert a binary string into a BLOB column is to use a SQL INSERT statement and include the binary string
a SQL binary literal in the statement as shown in this sample program. Note that SQL binary literal format is x'<hex_numbers>'.
/**
* MySqlBlobInsert.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
import java.nio.charset.*;
public class MySqlBlobInsert {
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 the record for re-testing
String subject = "Test on INSERT statement";
Statement sta = con.createStatement();
sta.executeUpdate("DELETE FROM Image WHERE Subject = '"
+subject+"'");
// Inserting CLOB value with a regular insert statement
sta = con.createStatement();
int count = sta.executeUpdate(
"INSERT INTO Image"
+" (Subject, Body)"
+" VALUES ('"+subject+"'"
+", x'C9CBBBCCCEB9C8CABCCCCEB9C9CBBB')");
// Retrieving BLOB value with getBytes()
ResultSet res = sta.executeQuery(
"SELECT * FROM Image WHERE Subject = '"+subject+"'");
res.next();
System.out.println("The inserted record: ");
System.out.println(" Subject = "+res.getString("Subject"));
System.out.println(" Body = "
+new String(res.getBytes("Body")));
res.close();
sta.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
}
}
}
Compilation and execution of this program is below.
The output confirms that the character string value was correctly inserted
into the BLOB column:
C:\>javac -cp .;\local\lib\mysql-connector-java-5.0.7-bin.jar
MySqlBlobInsert.java
C:\>java -cp .;\local\lib\mysql-connector-java-5.0.7-bin.jar
MySqlBlobInsert
The inserted record:
Subject = Test on INSERT statement
Body = ╔╦╗╠╬╣╚╩╝╠╬╣╔╦╗
Using SQL binary literals to insert BLOB values into database is simple. But it requires you
to convert your binary data into the SQL binary literal format: x'<hex_numbers>', which could
be a problem if you have a very long binary data to enter.
Using PreparedStatement with setXXX() method is a much better choice.
