CODEWITHSUNDEEP
javamysqlsqljdbc
Stephane Grenier
Stephane Grenier

Reputation: 15927

How to convert Java byte[] to MySQL BLOB's in SQL script file created by Java code

I have a Java program that creates an SQL script that will then later be imported as a file into MySQL. The Java program cannot directly access the MySQL database but has to generate an SQL file with all the insert commands to be ingested by MySQL. Without getting into too many details we can ignore any security concerns because the data is used once and then the database deleted.

The Java code does something like this:

String sql = "INSERT INTO myTable (column1, column2) VALUES (1, 'hello world');";

BufferedWriter bwr = new BufferedWriter(new FileWriter(new File("output.sql")));
bwr.write(sql);
// then flushes the stream, etc.

The issue I have is when I need to include a byte[] array as the third column:

The issue I have is that I now need to include a byte[] array as the third column. Therefore I want to do:

byte[] data = getDataFromSomewhere();
// Convert byte[] to String and replace all single quote with an escape character for the import
String dataString = new String(data, StandardCharsets.UTF_8).replaceAll("'", "\\\\'");

String sql = "INSERT INTO myTable (column1, column2, column3) VALUES (1, 'hello world', '" + dataString + "');";

BufferedWriter bwr = new BufferedWriter(new FileWriter(new File("output.sql")));
bwr.write(sql);
// then flushes the stream, etc.

The problem is that on the other computer when I load the file I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''????' at line 1

The core of the code to load the SQL file is simply:

try (Stream<String> stream = Files.lines(Paths.get(IMPORT_SQL_FILE)))
{
    stream.forEach(line ->
    {
        try
        {
            // Yes it could be batched but I omitted it for simplicity
            executeStatement(connection, line);
        } catch (Exception e) {
            e.printStackTrace();
            System.exit(-1);
        }
    });
}

And if I load that the file directly in MySQL I get the following error message:

1 row(s) affected, 1 warning(s): 1300 Invalid utf8 character string: 'F18E91'

Therefore my question is how can I generate an SQL file with binary data from Java?

Upvotes: 1

Views: 472

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

Inline your BLOB data into a hexadecimal literal:

StringBuilder sb = new StringBuilder(a.length * 2);
for(byte b: data) {
    sb.append(String.format("%02x", b));
}
String sql = "INSERT INTO myTable (column1, column2, column3) "
           + "VALUES (1, 'hello world', x'" + sb.toString() + "');";

Upvotes: 1

Related Questions