Inserting BLOB Values with SQL INSERT Statements

JDBC Tutorials - Herong's Tutorial Examples

∟MySQL BLOB (Binary Large Object) - BLOB

∟Inserting BLOB Values with SQL INSERT Statements

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) HerongYang.com. 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.cj.jdbc.MysqlDataSource ds
        = new com.mysql.cj.jdbc.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:

herong> java -cp .:mysql-connector-java-8.0.19.jar MySqlBlobInsert.java

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.