CODEWITHSUNDEEP
javamysqlsqlblobmysql-workbench
DeanMWake
DeanMWake

Reputation: 923

Inserting/Updating a blob into MySQL from Java

After a day of googling I have finally decided its time to ask the all powerful SO community. I am trying to insert or update an image into a MySQL database. The query and code I am using is as follows:

    FileInputStream inputStream = new FileInputStream(file);

    String[] str_array = file.getName().split("-");
    String stringb = str_array[1];
    String stringc = str_array[2];
    String fingerName = stringc.substring(0, 2);
    //gets file name and splits it accordingly

    String id = getID.id(stringb); //does a sql lookup to get the previously inserted id according to the stringb(users unique id number)

    String INSERT_PIC = "INSERT INTO database.user_picture(id_ref, picture_num, user_image) values('" + id + "', ?, ?) ON DUPLICATE KEY UPDATE user_image = ?;";

    //creates the sql statement that inserts or updates according to the primary keys id_ref and picture_num

    ps = (PreparedStatement) connection.prepareStatement(INSERT_PIC);

    ps.setString(1, fingerName);
    ps.setBinaryStream(2, (InputStream) inputStream, file.length());
    ps.setBinaryStream(3, (InputStream) inputStream, file.length());

    //creates the prepared statement and inserts the 3 parameters

    ps.executeUpdate();
    connection.commit();
    //executes the query on the connected database

I was quite sure this would work. When tested it will insert the image into the database correctly. When updating all of the fields are updated correctly except for the blob/image field which is instead changed to NULL.

I'm not sure why this is happening or of any other way to get this to work, Any suggestions would be appreciated...

Upvotes: 0

Views: 7552

Answers (2)

Hendrik
Hendrik

Reputation: 5310

You are providing the same input stream for both parameter 2 and 3. When the statement is executed, first the parameter 2 stream is read until the end. When JDBC driver then attempts to read the stream for parameter 3, it is already at its end, because it is the same stream instance.

Try to provide two input streams:

FileInputStream inputStream1 = new FileInputStream(file);
FileInputStream inputStream2 = new FileInputStream(file);
[...]
ps.setBinaryStream(2, (InputStream) inputStream1, file.length());
ps.setBinaryStream(3, (InputStream) inputStream2, file.length());

Also, don't forget to close the streams in some finally block.

Good luck.

Upvotes: 1

user3012345
user3012345

Reputation: 25

My guess is the first setBinaryStream() consumes the stream. So the second call to setBinaryStream() simply reads "null".

Upvotes: 1

Related Questions